Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Passing args in a class

This is a discussion on Passing args in a class within the Excel Questions forums, part of the Question Forums category; This is in Module1: Code: Option Explicit Sub WithoutClass() Dim SheetArray() As Variant SheetArray = Array(Sheet1, Sheet2) Dim SheetArrayElement As ...

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Posts
    368

    Default Passing args in a class

    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 by tiredofit; Apr 19th, 2017 at 11:52 AM.

  2. #2
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,174

    Default Re: Passing args in a class

    What distinction? I don't really understand what you are asking

  3. #3
    GTO
    GTO is offline
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,031

    Default Re: Passing args in a class

    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:

    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...

  4. #4
    GTO
    GTO is offline
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    6,031

    Default Re: Passing args in a class

    PS. I forgot to mention, type in, "ByRef argument type mismatch" in Help for another example.

  5. #5
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,471

    Default Re: Passing args in a class

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

  6. #6
    Board Regular
    Join Date
    Apr 2013
    Posts
    368

    Default Re: Passing args in a class

    Quote Originally Posted by Kyle123 View Post
    What distinction? I don't really understand what you are asking
    I meant byref / byval.

  7. #7
    Board Regular
    Join Date
    Apr 2013
    Posts
    368

    Default Re: Passing args in a class

    Quote Originally Posted by RoryA View Post
    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?

  8. #8
    Board Regular
    Join Date
    Apr 2013
    Posts
    368

    Default Re: Passing args in a class

    Quote Originally Posted by RoryA View Post
    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

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    29,471

    Default Re: Passing args in a class

    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.

  10. #10
    Board Regular
    Join Date
    Apr 2013
    Posts
    368

    Default Re: Passing args in a class

    So instead of ByRef, he should have omitted it altogether or better still, write ByVal.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com