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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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:
|
1 2 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
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!