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:

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:

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:

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:

Available also in GitHub.

Cheers!

About

VBA Developer

Tagged with: , , ,