42

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.)

Mesut Akcan
  • 899
  • 7
  • 19
vbAdder
  • 659
  • 5
  • 12
  • 1
    Not a real explaination, but x = farray2(2, b(1)) Debug.Print Join(x, " ") wil give the value in the string and not the pointer. It looks like the Join function acts different on a Variant() with a ParamArray (different evaluation steps...??) – EvR Sep 05 '18 at 08:31
  • ah good point, the join function tries to convert the result to a string, sum to a number, somehow the string conversion uses the pointer not the value, where sum can still access the value. maybe worksheet functions and vba functions treat variants subtle different? – vbAdder Sep 05 '18 at 15:31
  • 5
    You're not alone! See this: https://stackoverflow.com/questions/3375562/vba-what-is-causing-this-string-argument-passed-to-paramarray-to-get-changed-to – Blackhawk Sep 05 '18 at 17:42
  • 3
    I suggest two possible courses of action if you want to dig deeper: 1. you can play around with the underlying data structures VARIANT and SAFEARRAY using the types in my answer [here](https://stackoverflow.com/questions/24613101/vba-check-if-array-is-one-dimensional/24613807#24613807) 2. you can decompile the compiled VBA code that is actually run to see if the individual pcode instructions reveal the compiler bug, using something like [this](https://github.com/bontchev/pcodedmp) – Blackhawk Sep 05 '18 at 17:53
  • 3
    Thanks Blackhawk, the accepted answer to question 3375562 is that its a bug. The previous question indeed is similar, but different in its implementation. The common thing is that a first element in the function parameters (r in farray above) is essential in modifying the paramarray behaviour, and both questions have noticed that this behaviour is erratic, and depends on what ought to be unconnected (e.g sub in stead of function, different declartion of b etc) – vbAdder Sep 06 '18 at 20:41
  • 4
    I would argue that it *is* a duplicate of https://stackoverflow.com/q/3375562/11683. – GSerg Sep 07 '18 at 18:08
  • @gene It's perfectly fine syntax. Furthermore, I merely edited the question to add code blocks. You are off base, and dragging the wrong people into your off-base take. – JNevill Sep 13 '18 at 18:09
  • @JNevill Thank you for a quick reaction to my comment. Still, I'd like to know what this "perfectly fine syntax" defines. It would be very nice of you to provide an equivalent common (inelegant?) equivalent to the definition given in the post. Could you, please? –  Sep 13 '18 at 18:15
  • `Dim b As Variant: b=Array(1,2,3)` Difference though that the syntax in the question generates a 1-based array. – JNevill Sep 13 '18 at 18:25
  • @vbAdder Running you code **exactly** I get this: 426960808 // 426960811 // 426960808 3 // 426960808 // 426960813 // 426960808 5 // 426960808 426960824 –  Sep 13 '18 at 19:32
  • @vbAdder Changing the declaration and the assignments to `Dim b(): ReDim b(0 To 2): b(0) = 1: b(1) = 2: b(2) = 3` results in this: 2 // 5 // 2 3 // 2 // 7 // 2 5 –  Sep 13 '18 at 19:44
  • 1
    Hi Gene, thanks for your interest. (a) you seem to get more pointerlike outputs than I do with the code "as-is" (b) you confirm that as per point (2) in the original post delaring b as an array with `dim b()`, or `b(0 to 2)` as you do the issue disappears. (did you omit the output from the last line in the code, the two varpointers?). Are you using the same version of excel? – vbAdder Sep 15 '18 at 22:25
  • 1
    It's actually more interesting than you think: If you put your code into an *Module*, you get results in the form the question demonstrates. However, if you put the code into (and run it from) a *Worksheet* instead, then you get the pointer for **all 7** output lines, not just the last 2. – Chronocidal Oct 23 '19 at 13:03
  • I have similar results for Excel 16 – RBarryYoung Nov 22 '21 at 15:46

2 Answers2

1

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

David García Bodego
  • 1,058
  • 3
  • 13
  • 21
  • I think you've missed the point: when using a ParamArray, sometimes the pointer which is passed is interpreted as a 'val' instead of as a 'var'. – david Oct 22 '20 at 03:45
1

I think, it is a carelessness in "C". You get such an error with

#include <stdio.h>

int main(int argc, char **argv)
{
   int *X;
   int i;
   
   i = 7;
   X = &i;
   
   printf("%d\n", X);
   printf("%d\n", *X);
   
   return(0);
}

Elaboration:

"C" is a kind of abstract assembler. You have to understand the concept of accessing memory through addresses (or pointers). With

int i;

you declare a variable, that holds an integer (the number of bits of this integer depends on the compiler). With

i = 7;

you define the value of this variable in memory (here 7). With

int *X;

you declare a variable, which holds a value of a pointer (the number of bits of a pointer depends on the compiler; often the number of bits of an integer and a pointer are equal) to a location, which contains an integer. With

X = &i;

you define the value of the pointer X as the address of the variable i. With

*X

you have access to the (integer) value in memory of the location, where X points to. With

printf("%d\n", X);

you print the value of the pointer X (interpreted as integer). With

printf("%d\n", *X);

you print the (integer) value of the memory, where X points to.

I believe, someone forgot a "*".

Christian Reinbothe
  • 62
  • 7