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

 

[RoryA]

That just might be the weirdest thing I've ever seen in VBA. You've seriously declared a variable somewhere and added an argument to two subs just so that you could save typing Dim in each one?

 

[tiredofit]

That just might be the weirdest thing I've ever seen in VBA. You've seriously declared a variable somewhere and added an argument to two subs just so that you could save typing Dim in each one?

Yes, that was the sole purpose.

I'm not saying it's great but I not at your level of expertise, so fail to see why it is frowned upon.

Revisiting the code now, I could've declared RngElement at the module level. Then in the two subs, I wouldn't have had to pass the Arg1 but I would still need to pass it for the class.

So a better approach would be to just declare a local variable in the class for the Loop.

 

[RoryA]

Reusing variables in completely unrelated procedures is generally not regarded as a good idea because it could make debugging much harder - eg your variable has an odd value in one routine because it was assigned it by a completely unrelated routine that is not even part of the current call stack.

I'm also baffled as to why saving one word in a procedure is worth it. I don't see any benefit and I think your code would be extremely confusing to anyone else having to pick it up later.

 

[tiredofit]

Reusing variables in completely unrelated procedures is generally not regarded as a good idea because it could make debugging much harder - eg your variable has an odd value in one routine because it was assigned it by a completely unrelated routine that is not even part of the current call stack.

I'm also baffled as to why saving one word in a procedure is worth it. I don't see any benefit and I think your code would be extremely confusing to anyone else having to pick it up later.

Thanks for the explanation.

"Reusing variables in completely unrelated procedures is generally not regarded as a good idea because it could make debugging much harder
"

You mean Sub SomeMethod1 and Sub SomeMethod2 are unrelated, so don't use the same variable for both, instead declare a local varible in each instead?

I edited my reply before yours, so is the edited version a better approach?

 

[RoryA]

You mean Sub SomeMethod1 and Sub SomeMethod2 are unrelated, so don't use the same variable for both, instead declare a local variable in each instead?

Yes, exactly that.

 

[tiredofit]

Yes, exactly that.

Many thanks for your time on this thread.

I understand.

Ordinarily I don't but I was writing my first class interface (that worked!) and was keen to get things working, so was on autopilot,