ByVal or ByRef - As Variant


Integers

When you declare a variable with an Integer data type it can be passed in using either ByVal or ByRef.
When you declare a variable with a Variant data it can be passed in using either ByVal or ByRef.



Arrays

There is a difference between declaring a variable with a Variant data type as opposed to an Array data type.
When you declare a variable with an Array data type it can only be passed in using ByRef.
When you declare a variable with a Variant data type it can be passed in using either ByVal or ByRef.

Sub MySubroutine1() 
   Dim var3 As Variant
   var3 = Array(1, 2, 3, 4)
    
   MyFunc_1a (var3)
   Debug.Print var3(2) '3

   MyFunc_1b (var3)
   Debug.Print var3(2) '3
End Sub

Public Function MyFunc_1a(ByVal var4 As Variant)
   var4(2) = 4
End Function
Public Function MyFunc_1b(ByRef var4 As Variant)
   var4(2) = 8
End Function

Collections


Classes

When you declare a variable with a Class data type it can be passed in using either ByVal or ByRef.
When you declare a variable with a Variant data type it can only be passed in using either ByVal.

'myClass module
Private mstrPropertyName As String

Property Get Property_Name() As String
    Property_Name = mstrPropertyName
End Property

Property Let Property_Name(rData As String)
    mstrPropertyName = rData
End Property

When you declare a variable with a Variant data type it can only be passed in using ByVal.

Sub MySubroutine3() 
   Dim var5 As Variant
   Set var5 = New myClass
   var5.Property_Name = "before"
   Debug.Print var5.Property_Name

   MyFunc_3a (var5)
   Debug.Print var5.Property_Name
End Sub

'must be ByVal
Public Function MyFunc_3a(ByVal var6 As Variant)
   var6.Property_Name = "after"
End Function

User Defined Types



© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited TopPrevNext