Variable in class interface

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,926
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This is in a standard module:

Code:
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:

Code:
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:

Code:
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:

Code:
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:

Code:
    Variable required -can't assign to this operation

and points to this line:

Code:
    For Each ClsInterface_RngElement In ClsInterface_Rng.Cells

in particular, highlighting this word:

Code:
    ClsInterface_RngElement

If I changed it to:

Code:
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

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

works.

But even if I DO change it to:

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

it also works.

Can someone please explain what is wrong?

Thanks
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
Rich (BB code):
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.
 
Upvote 0
Solution
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.
Rich (BB code):
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)!

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

Do you agree?
 
Upvote 0
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)!
Code:
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.
 
Upvote 0
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:

Code:
For Counter = 1 To ClsInterface_Rng.Count

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

        Next Counter
 
Upvote 0
If I changed it to:

Code:
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.
 
Upvote 0
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:

Code:
For Each ClsInterface_RngElement In ClsInterface_Rng.Cells

because as @Jaafar Tribak pointed out:

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

so I might as well do away with RngElement.
 
Upvote 0
I know you can't. My question was why would you want to? It makes no sense to me.
 
Upvote 0
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).
 
Upvote 0
I mean why would you want to use either of those for a For Each loop rather than just a local Range variable?
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top