Passing parameters in a class

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,257
Is it better to use properties to pass parameters in a class, (as in Method 1 below) or not?


Method 1:

Sheet1:

Rich (BB code):
Private Sub UpdateTable()
    
    Dim MyUpdateTable As ClsUpdateTable
    
    Set MyUpdateTable = New ClsUpdateTable
    
    Set MyUpdateTable.Period = Me.Range("Now")
    
    Call MyUpdateTable.UpdateTable
    
End Sub


ClsUpdateTable

Rich (BB code):
Option Explicit

    Private pPeriod As Range
    
Public Property Get Period() As Range
    
    Set Period = pPeriod
    
End Property

Public Property Set Period(ByVal P As Range)
    
    Set pPeriod = P
    
End Property

Sub UpdateTables()

    Dim MyArray() As Variant
            
    MyArray = Array("1", "2", "3")
    
    Dim MyArrayElement As Variant
    
    For Each MyArrayElement In MyArray()
    
        Range(Period.Address).Value = MyArrayElement
                    
    Next MyArrayElement         

End Sub


Method 2:

Sheet1:

Rich (BB code):
Private Sub UpdateTable()
    
    Dim MyUpdateTable As ClsUpdateTable
    
    Set MyUpdateTable = New ClsUpdateTable
    
    Call MyUpdateTable.UpdateTable(Period:=Me.Range("Now"))
    
End Sub


ClsUpdateTable

Rich (BB code):
Option Explicit

Sub UpdateTables(ByRef Period As Range)

    Dim MyArray() As Variant
            
    MyArray = Array("1", "2", "3")
    
    Dim MyArrayElement As Variant
    
    For Each MyArrayElement In MyArray()
    
        Range(Period.Address).Value = MyArrayElement
                    
    Next MyArrayElement         

End Sub

Thanks
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,509
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Define "better". :)
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,939
Office Version
  1. 2016
Platform
  1. Windows
I am not sure if there is much difference but I personally prefer to pass all the parameters in a single Method or Property like in Method 2 specially when the number of parameters is relatively small or/and when there are optional arguments ... It involves less code and more readable.
 

tiredofit

Well-known Member
Joined
Apr 11, 2013
Messages
1,257
Define "better".

I can't!

I am not sure if there is much difference but I personally prefer to pass all the parameters in a single Method or Property like in Method 2 specially when the number of parameters is relatively small or/and when there are optional arguments ... It involves less code and more readable.

When I was creating this, it occurred to me that IF I used method 1, there's never a need to write code such as (in a class):

Rich (BB code):
Sub UpdateTables(ByRef Period As Range)
Rich (BB code):

as in Method 2, ie to have to take in arguments because that's the purpose of using properties Get, Let and Set.
 
Last edited:

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708

ADVERTISEMENT

VBA lacks constructors which make it neater, but I do it by state/scope. If the property alters the state or is used outside the scope of a function/sub then I use a property, otherwise it's an argument.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,509
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Unless you use those variables for anything else in the class, I don't really see any benefit of making them separate properties, unless perhaps you need to perform some validation on them.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,651
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top