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:

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Greetings,

From Help: An argument passed ByRef (by reference), the default, must have the precise data type expected in the procedure.

In this case, I believe you cannot pass ByRef, the Variant sub-typed to a worksheet.

If the elements of the array are explicitly worksheets, then you could pass by reference:

Rich (BB code):
Sub WithoutClass()
Dim SheetArray() As Worksheet
Dim lngSheetArrayElement As Long
  
  ReDim SheetArray(0 To 1)
  
  For lngSheetArrayElement = LBound(SheetArray) To UBound(SheetArray)
    Set SheetArray(lngSheetArrayElement) = Array(Sheet1, Sheet2)(lngSheetArrayElement)
  Next
  
  For lngSheetArrayElement = LBound(SheetArray) To UBound(SheetArray)
    Call Second(SheetArray(lngSheetArrayElement))
  Next
  
End Sub
  
Sub Second(wks As Worksheet)
  
  Select Case wks.Name
  Case Sheet1.CodeName
    MsgBox "Hi!"
  Case Sheet2.CodeName
    MsgBox "Hello!"
  End Select
  
End Sub

...NOT that you really want to be passing objects around by reference...
 
Upvote 0
PS. I forgot to mention, type in, "ByRef argument type mismatch" in Help for another example.
 
Upvote 0
But note that Property procedures in a class always pass ByVal regardless of what you specify.
 
Upvote 0
But note that Property procedures in a class always pass ByVal regardless of what you specify.

So you shouldn't use the property method if you want the called procedure to change values?
 
Upvote 0
But note that Property procedures in a class always pass ByVal regardless of what you specify.


So would you say this is wrong

Code:
Option Explicit

Public Enum anlCellType
    anlCellTypeEmpty
    anlCellTypeLabel
    anlCellTypeConstant
    anlCellTypeFormula
End Enum

Private muCellType As anlCellType
Private mrngCell As Excel.Range

Property Set Cell(ByRef rngCell As Excel.Range)
    Set mrngCell = rngCell
End Property

Property Get Cell() As Excel.Range
    Set Cell = mrngCell
End Property

Property Get CellType() As anlCellType
    CellType = muCellType
End Property

Property Get DescriptiveCellType() As String
    Select Case muCellType
        Case anlCellTypeEmpty
            DescriptiveCellType = "Empty"
        Case anlCellTypeFormula
            DescriptiveCellType = "Formula"
        Case anlCellTypeConstant
            DescriptiveCellType = "Constant"
        Case anlCellTypeLabel
            DescriptiveCellType = "Label"
    End Select
End Property

Public Sub Analyze()
    If IsEmpty(mrngCell) Then
        muCellType = anlCellTypeEmpty
    ElseIf mrngCell.HasFormula Then
        muCellType = anlCellTypeFormula
    ElseIf IsNumeric(mrngCell.Formula) Then
        muCellType = anlCellTypeConstant
    Else
        muCellType = anlCellTypeLabel
    End If
End Sub

taken from

Professional Excel Development: Using Class Modules to Create Objects | Creating Objects | InformIT

because properties CANNOT be ByRef?

Code:
Property Set Cell(ByRef rngCell As Excel.Range)
    Set mrngCell = rngCell
End Property
 
Upvote 0
Well, it's wrong in as much as the object is passed ByVal but it makes no difference there anyway, so it's a somewhat moot point.
 
Upvote 0
So instead of ByRef, he should have omitted it altogether or better still, write ByVal.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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