Passing args in a class

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,825
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
This is in Module1:

Code:
Option Explicit

Sub WithoutClass()

    Dim SheetArray() As Variant
    
    SheetArray = Array(Sheet1, Sheet2)
    
    Dim SheetArrayElement As Variant
    
    For Each SheetArrayElement In SheetArray
    
        Call Second(SheetArrayElement)
        
    Next SheetArrayElement

End Sub

Sub Second(ByVal wks As Worksheet)

    Select Case wks.Name
    
        Case Sheet1.CodeName
        
            MsgBox "Hi!"
    
        Case Sheet2.CodeName
        
            MsgBox "Hello!"
    
    End Select

End Sub

I understand why the keyword ByVal is required in Sub Second because SheetArrayElement is declared as a Variant, yet the argument wks in Sub Second expects a worksheet.

However, why is it when I try the same using a class, I don't have to make this distinction?

This is in Module2:

Code:
Sub WithClass()

    Dim SheetArray() As Variant
    
    SheetArray = Array(Sheet1, Sheet2)
    
    Dim SheetsArrayElement As Variant

    Dim MyState As ClsState

    For Each SheetsArrayElement In SheetArray
                    
        Set MyState = New ClsState
        
        Set MyState.wks = SheetsArrayElement
        
        Call MyState.State
        
        Set MyState = Nothing
        
    Next SheetsArrayElement
            
End Sub

This is in ClsSate:

Code:
Option Explicit

    Private pwks As Worksheet

Public Property Get wks() As Worksheet

    Set wks = pwks
    
End Property

Public Property Set wks(ByVal ws As Worksheet)
    
    Set pwks = ws
    
End Property

Sub State()

    Select Case wks.CodeName
    
        Case Sheet1.CodeName
        
            MsgBox "Hi!"
    
        Case Sheet2.CodeName
        
            MsgBox "Hello!"
    
    End Select
    
End Sub

Is it because Sub State is not passed any arg?
 
Last edited:
Omitting it would be the same as adding ByRef. ByVal would have been clearer.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
@tiredofit:

Sorry I seemed to misunderstand your question.

@Rory:

But note that Property procedures in a class always pass ByVal regardless of what you specify.

Probably logging out shortly (near coma-tired and looking forward to the joy of replacing a P/S gear box...) and not back until Sunday night, but wanted to ask before it falls out of my head...

It makes sense to me that Property Set/Let would be a one-way (in only) deal, as we're trying to save it for later use. I am not understanding the difference if the property is in a class or standard module however?

I only tested this...

Standard Module (Module1)
Rich (BB code):
Option ExplicitSub example()
Dim lCnt As Long
Dim cls1 As Class1
  Set cls1 = New Class1
  
  lCnt = 3
  
  cls1.Count = lCnt
  
  MsgBox "Class property returns " & cls1.Count & vbLf & "lCnt (passed byRef) returns " & lCnt
  
  Module2.MyProperty = lCnt
  
  MsgBox "Std Mod Property returns " & Module2.MyProperty & vbLf & "lCnt (passed byVal) returns " & lCnt
  
  Module2.MyOtherProperty = lCnt
  
  MsgBox "Std Mod Property returns " & Module2.MyOtherProperty & vbLf & "lCnt (passed byRef) STILL returns " & lCnt
  
End Sub

Class Module (Class1)
Rich (BB code):
Option Explicit
Private lCount As Long
Public Property Get Count() As Long
  Count = lCount
End Property
Public Property Let Count(ByRef lNewValue As Long)
  lNewValue = lNewValue + 1
  lCount = lNewValue
End Property

Standard Module w/Properties (Module2)
Rich (BB code):
Option Explicit
Private lMyProperty As Long
Private lOtherProperty As Long
Public Property Get MyProperty() As Long
  MyProperty = lMyProperty
End Property
Public Property Let MyProperty(ByVal lNewValue As Long)
  lNewValue = lNewValue + 1
  lMyProperty = lNewValue
End Property
Public Property Get MyOtherProperty() As Long
  MyOtherProperty = lOtherProperty
End Property
Public Property Let MyOtherProperty(ByRef lSomeInt As Long)
  lSomeInt = lSomeInt + 1
  lOtherProperty = lSomeInt
End Property

...what am I missing?

Mark
 
Upvote 0
Properties behave the same in a module as a class module, or is that your point?
 
Last edited:
Upvote 0
Hi Kyle,

Yes or specifically, if there is any difference in how the argument is passed to a property Let/Set in a Standard vs Class module.

Mark
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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