I have noticed an oddity in VBA when using ParamArray
and passing array elements through it. In some cases it is not the value of the array element that arrives in a function, but the var pointer. (Excel 2016, 32-bit).
After some plodding I found that the definition of the function as a variant array - in combination with a parameter list combined with a ParamArray
- seems to be where the unexpected behaviour originates, but I can't see any feasible reasons why this would be so.
The behaviour reverts to normal when:
1) the variable r is removed in the function declarations
2) b
is declared with Dim b()
3) the function returns a Variant
, rather than a Variant()
I appreciate that it's a rather esoteric question, and it appears to be controllable for in various ways, but is there an explanation out there that elucidates this behaviour?
Sub Variantarraybug() Dim b: b = [{1, 2, 3}] Debug.Print farray1(2, b(1))(0) Debug.Print Application.WorksheetFunction.Sum(farray1(2, b(1))) Debug.Print Join(farray1(2, b(1)), " ") Debug.Print farray2(2, b(1))(0) Debug.Print Application.WorksheetFunction.Sum(farray2(2, b(1))) Debug.Print Join(farray2(2, b(1)), " ") Debug.Print VarPtr(b(1)), VarPtr(b(2)) End Sub Function farray1(r, ParamArray plop()) As Variant farray1 = Array(plop(0), 3) End Function Function farray2(r, ParamArray plop()) As Variant() farray2 = Array(plop(0), 5) End Function
Result in the debug window:
1 4 1 3 1 6 358808368 5 358808368 358808384
Note 1: My understanding is that the VarPtr
function returns the memory location of the start address of the memory required by that variable. Here it is used only to demonstrate that the unexpected number (358808368) that was seen by the farray2
function, is in fact the address of that element.
Note 2: This happens independent of how you generate the array (e.g. b=array(1,2,3)
, b=[1,2,3]
, etc.) and how b
is declared (b
, b(1 to 3)
, etc.). However, if you declare b
with Dim b()
, the unexpected behaviour disappears. (You can't print VarPtr(b)
in that case, as VarPtr
cannot accept array variables.)
In case of an array (variable), while passed as a function argument, it decays to the pointer to the first element of the array. The pointer is then passed-by-value, as usual.
When you pass an array to a function as an argument, the address of the first element of the array is copied to a local variable pertaining to the function. That is basically the difference between passing a regular variable and passing an array to a function.
An array is a sequence of values; the values in the array are called elements. You can make an array of int s, double s, String s, or any other type, but all the values in an array must have the same type.
I think that this bug appear due to the array "declaration". In this point, I think that Microsoft states that one clearly:
Arrays
You can declare a variable to hold an array, which can hold multiple values. To specify that a variable holds an array, follow its variablename immediately with parentheses.
On here
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With