Variable in class interface

[tiredofit]

This is in a standard module:

Option Explicit
    
Public Sub Test()
    
    Dim MyClsInterface As ClsInterface
    
    Select Case Sheet1.Cells(2, 1).Value
    
        Case vbNullString
            
            Set MyClsInterface = ClassFactory(Val:="Empty")
            
        Case Else
    
            Set MyClsInterface = ClassFactory(Val:="Not empty")
            
    End Select
    
    Dim Rng As Range
    
    Dim Rngelement As Range
    
    Dim ws As Worksheet
    
    For Each ws In ThisWorkbook.Worksheets
    
        With ws

            Set Rng = .Range(.Cells(2, 6), .Cells(10, 6))
        
        End With
        
        Set MyClsInterface.Rng = Rng
        Set MyClsInterface.Rngelement = Rngelement
        
        Call MyClsInterface.SomeMethod
          
    Next ws
    
    Set ws = Nothing
     
    Set Rng = Nothing
    
    Set Rngelement = Nothing
    
End Sub
    
Private Function ClassFactory(ByRef Val As String) As ClsInterface

    Select Case Val
    
        Case "Empty"
            
            Set ClassFactory = New ClsEmpty
            
        Case "Not empty"
        
            Set ClassFactory = New ClsNotEmpty
            
    End Select

End Function

This is ClsInterface:

Option Explicit
    
    Private pRng As Range
    Private pRngElement As Range

Public Property Get Rng() As Range

    Set Rng = pRng
    
End Property

Public Property Set Rng(ByVal R As Range)

    Set pRng = R
    
End Property

Public Property Get Rngelement() As Range
    
    Set Rngelement = pRngElement
    
End Property

Public Property Set Rngelement(ByVal RElement As Range)

    Set pRngElement = RElement
    
End Property

Public Sub SomeMethod()

End Sub

This is ClsNonEmpty:

Option Explicit

    Implements ClsInterface

    Private pClsInterface_Rng As Range
    Private pClsInterface_RngElement As Range
    
Public Property Get ClsInterface_Rng() As Range

    Set ClsInterface_Rng = pClsInterface_Rng
    
End Property

Public Property Set ClsInterface_Rng(ByVal R As Range)

    Set pClsInterface_Rng = R
    
End Property

Public Property Get ClsInterface_RngElement() As Range
    
    Set ClsInterface_RngElement = pClsInterface_RngElement
    
End Property

Public Property Set ClsInterface_RngElement(ByVal RElement As Range)

    Set pClsInterface_RngElement = RElement
    
End Property

Private Sub ClsInterface_SomeMethod()
    
    Dim Coll As Collection
    Set Coll = New Collection
    
    Coll.Add "apple"
    Coll.Add "orange"
    
    Dim CollElement As Variant
     
    For Each CollElement In Coll
    
        For Each ClsInterface_RngElement In ClsInterface_Rng.Cells
        
            If InStr(1, ClsInterface_RngElement.Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"
                                                                                                        
           
        Next ClsInterface_RngElement
        
    Next CollElement
    
    Set CollElement = Nothing

End Sub

This is ClsEmpty:

Option Explicit

    Implements ClsInterface

    Private pClsInterface_Rng As Range
    Private pClsInterface_RngElement As Range
    
Public Property Get ClsInterface_Rng() As Range
    
End Property

Public Property Set ClsInterface_Rng(ByVal R As Range)

End Property

Public Property Get ClsInterface_RngElement() As Range
    
End Property

Public Property Set ClsInterface_RngElement(ByVal RElement As Range)

End Property

Private Sub ClsInterface_SomeMethod()

End Sub

The above code does not compile. The error message is:

    Variable required -can't assign to this operation

and points to this line:

    For Each ClsInterface_RngElement In ClsInterface_Rng.Cells

in particular, highlighting this word:

    ClsInterface_RngElement

If I changed it to:

For Each pClsInterface_RngElement In ClsInterface_Rng.Cells
        
        If InStr(1, ClsInterface_RngElement.Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"
           
Next pClsInterface_RngElement

it works.

Note I DON'T have to change the ClsInterface_RngElement inside the loop, ie

    If InStr(1, ClsInterface_RngElement.Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"

works.

But even if I DO change it to:

    If InStr(1, pClsInterface_RngElement.Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"

it also works.

Can someone please explain what is wrong?

Thanks

 

[Jaafar Tribak]

For Each expects a variable in its counter not a Property .

ClsInterface_RngElement is a Property so it won't compile, whereas pClsInterface_RngElement is a variable

Also, I would use the variables pClsInterface_RngElement and pClsInterface_Rng throughout to make the code more efficient and quicker as the variables are already set pior to implementing the SomeMethod interface Method.

For Each pClsInterface_RngElement In pClsInterface_Rng
    If InStr(1, pClsInterface_RngElement.Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"
Next pClsInterface_RngElement

If you use the ClsInterface_RngElement and ClsInterface_Rng Properties, the code will need to repeatedly evaluate those properties at each call which is less efficient.

 

[tiredofit]

For Each expects a variable in its counter not a Property .

ClsInterface_RngElement is a Property so it won't compile, whereas pClsInterface_RngElement is a variable

Also, I would use the variables pClsInterface_RngElement and pClsInterface_Rng throughout to make the code more efficient and quicker as the variables are already set pior to implementing the SomeMethod interface Method.

For Each pClsInterface_RngElement In pClsInterface_Rng
    If InStr(1, pClsInterface_RngElement.Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"
Next pClsInterface_RngElement

If you use the ClsInterface_RngElement and ClsInterface_Rng Properties, the code will need to repeatedly evaluate those properties at each call which is less efficient.

Thanks for the explanation.

In general, I prefer to use the properties, ie ClsInterface_RngElement because if the Get / Set Properties have some additonal validation code, it would trigger that off, whereas using just pClsInterface_RngElement won't, according tp the following video (unless I've misunderstood it)!

https://www.youtube.com/watch?v=MjbmsVDnAL0&t=2802s

Do you agree?

 

[Jaafar Tribak]

In general, I prefer to use the properties, ie ClsInterface_RngElement because if the Get / Set Properties have some additonal validation code, it would trigger that off, whereas using just pClsInterface_RngElement won't, according tp the following video (unless I've misunderstood it)!

https://www.youtube.com/watch?v=MjbmsVDnAL0&t=2802s

Do you agree?

I guess if you have additonal validation code in the Property then YES, otherwise it would be a waste of time like in your particular scenario.

 

[tiredofit]

I guess if you have additonal validation code in the Property then YES, otherwise it would be a waste of time like in your particular scenario.

Thanks.

I could also change it from a For Each Loop to a For Next Loop:

For Counter = 1 To ClsInterface_Rng.Count

            If InStr(1, ClsInterface_Rng(Counter).Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"

        Next Counter

 

[RoryA]

If I changed it to:

For Each pClsInterface_RngElement In ClsInterface_Rng.Cells

If InStr(1, ClsInterface_RngElement.Text, CollElement, vbBinaryCompare) <> 0 Then MsgBox "hi"

Next pClsInterface_RngElement

it works.

Why on earth would you want to do that? You have a property to set that range element, then you want some random internal process to use it as a loop variable? Makes no sense at all to me.

 

[tiredofit]

Why on earth would you want to do that? You have a property to set that range element, then you want some random internal process to use it as a loop variable? Makes no sense at all to me.

I can't use this:

For Each ClsInterface_RngElement In ClsInterface_Rng.Cells

because as @Jaafar Tribak pointed out:

For Each expects a variable in its counter not a Property .

so I might as well do away with RngElement.

 

[RoryA]

I know you can't. My question was why would you want to? It makes no sense to me.

 

[tiredofit]

I know you can't. My question was why would you want to? It makes no sense to me.

You're asking why would I want to use ClsInterface_RngElement or why would I want to use a For Next Loop?

I wanted to use ClsInterface_RngElement because a For Each Loop is quicker in a collection. However, I can't use the property ClsInterface_RngElement and instead have to use the variable pClsInterface_RngElement. If I have additional validation code, it would not trigger it off.

So therefore I could use a For Next Loop instead, for consistency (and not have any traces of ClsInterface_RngElement).

 

[RoryA]

I mean why would you want to use either of those for a For Each loop rather than just a local Range variable?