VBA – How to refer a function ByVal, when the parameter is explicitly ByRef
Ok, you have read the title correctly – there is a way to refer a parameter ByValue, even though the parameter contains explicitly the word ByRef in it.
Let’s start with the functions. Imagine a function, getting an array as parameter and returning array. The only action the function does is to increment the first element of the array with 100. Something like this:
Public Function increment1(ByVal testValue As Variant) As Variant
testValue(0) = testValue(0) + 100
increment1 = testValue
End Function
Public Function increment2(ByRef testValue As Variant) As Variant
testValue(0) = testValue(0) + 100
increment2 = testValue
End Function
You see the ByVal and the ByRef and most probably you are already smiling, because you know what is the difference between these two functions – the one, which is referencing ByVal would not change the parameter in the calling array, if the function is not assigned to a specific value. And you are partially correct. In a scenario like this:
Public Sub TestMe()
Dim var1, var2
Dim var3, var4
Dim var5, var6
var1 = Array(1, 1)
var2 = Array(2, 1)
var3 = Array(3, 1)
var4 = Array(4, 1)
var5 = Array(5, 1)
var6 = Array(6, 1)
increment1 (var1)
increment2 (var2)
increment1 var3
increment2 var4
var5 = increment1(var5)
var6 = increment2(var6)
' Debug.Print var1(0)
' Debug.Print var2(0)
Debug.Print var3(0)
Debug.Print var4(0)
' Debug.Print var5(0)
' Debug.Print var6(0)
End Sub
You would see 3 and 104 printed on the immediate window. 3 is because it is ByVal and 104 is because it is ByRef. I suppose that you feel happy and content! 🙂
However, the reason why I am writing this article is that, under some circumstances, the VBE has its own ideas of ByVal and ByRef . Which are most probably a bit strange at the beginning. E.g., if you pass an argument to a function in parenthesis like this:
increment1 (var1)
increment2 (var2)
the VBEditor would simply take the argument ByVal and would not care about the ByVal and the ByRef of the parameters of the function. Now take a deep breath, uncomment the code from the example above and run it. You will get 1 and 2.
At the end, the last example with var5 and var6 is a bit trivial, because their value is assigned after the incrementation, thus it is not something unexpected.
The whole code:
Option Explicit
Public Sub TestMe()
Dim var1, var2
Dim var3, var4
Dim var5, var6
var1 = Array(1, 1)
var2 = Array(2, 1)
var3 = Array(3, 1)
var4 = Array(4, 1)
var5 = Array(5, 1)
var6 = Array(6, 1)
increment1 (var1)
increment2 (var2)
increment1 var3
increment2 var4
var5 = increment1(var5)
var6 = increment2(var6)
Debug.Print var1(0)
Debug.Print var2(0)
Debug.Print var3(0)
Debug.Print var4(0)
Debug.Print var5(0)
Debug.Print var6(0)
End Sub
Public Function increment1(ByVal testValue As Variant) As Variant
testValue(0) = testValue(0) + 100
increment1 = testValue
End Function
Public Function increment2(ByRef testValue As Variant) As Variant
testValue(0) = testValue(0) + 100
increment2 = testValue
End Function
Cheers!