Arrays and VarPtrArray

Question

Can anyone explain why the code below produces the results it does? Copy the code to a new module and run Test. The address of the array structure AA isn't what I would have thought it would be.

Private Declare Function VarPtrArray Lib "msvbvm60.dll" Alias "VarPtr" _

        (Var() As Any) As Long

Public First As Long

Public AA(0 To 99) As Long

Sub Test()

    Dim L As Long

    Dim K As Long

    Debug.Print "L", VarPtr(L)

    Debug.Print "K", VarPtr(K)

    Debug.Print "AA", VarPtrArray(AA)

    Debug.Print "First", VarPtr(First)

    Debug.Print "AA(0)", VarPtr(AA(0))

End Sub

The results in the Immediate window are as follows (your numbers will be different but will be relatively the same):

L              2225040

K              2225036

AA             2225032

First          145709728

AA(0)          146149664

Clearly, variables L and K are allocated locally in Test, as one would expect. Similarly, First and AA(0) are allocated globally, also as one would expect. But why is the address of AA (the SAFEARRAY structure) local to Test when it is declared globally? Shouldn't the address of AA be in the same range as First and AA(0) ? Neither VarPtr nor VarPtrArray are officially supported by MS, so there isn't much documentation to go on.

I'm using Excel 2013 32-Bit on a Windows 8 Pro 64-bit system. Thanks.

Cordially,

Chip Pearson

MS MVP 1998 - 2013 Excel

******@cpearson.com

www.cpearson.com

Answer 1

Hi Chip,

If you omit the dimension in the declaration the array address changes. Try this version:

Option Explicit

Private Declare Function VarPtrArray Lib "msvbvm60.dll" Alias "VarPtr" _

         (Var() As Any) As Long

 Public First As Long

 Public AA() As Long

 Sub Test()

     Dim L As Long

     Dim K As Long

     ReDim AA(0 To 99)

     Debug.Print "L", VarPtr(L)

     Debug.Print "K", VarPtr(K)

     Debug.Print "AA", VarPtrArray(AA())

     Debug.Print "First", VarPtr(First)

     Debug.Print "AA(0)", VarPtr(AA(0))

 End Sub

Answer 2

Looking at this through C/C++ goggles, the code line,

Debug.Print "AA", VarPtrArray(AA)

... would equate closer to,

Debug.Print "*AA(0)", VarPtr(VarPtr(AA(0)))

... than,

Debug.Print "AA(0)", VarPtr(AA(0))

In fact, I believe the pointer to a pointer is what the local Test sub is returning. A local memory address pointer to a global memory address pointer if you will. I used to use double pointers when coding for RAS and other network structures and I freely admit to being rusty in the concept.

Answer 3

Errata:  Sorry for the misdirection in my previous, now-deleted reponse.

Chip wrote:

Private Declare Function VarPtrArray Lib "msvbvm60.dll" Alias "VarPtr" _

        (Var() As Any) As Long

Public First As Long

Public AA(0 To 99) As Long

Sub Test()

    Dim L As Long

    Dim K As Long

    Debug.Print "L", VarPtr(L)

    Debug.Print "K", VarPtr(K)

    Debug.Print "AA", VarPtrArray(AA)

    Debug.Print "First", VarPtr(First)

    Debug.Print "AA(0)", VarPtr(AA(0))

End Sub

 

The results in the Immediate window are as follows (your numbers will be different but will be relatively the same):

L              2225040

K              2225036

AA             2225032

First          145709728

AA(0)          146149664

 

Clearly, variables L and K are allocated locally in Test, as one would expect. Similarly, First and AA(0) are allocated globally, also as one would expect. But why is the address of AA (the SAFEARRAY structure) local to Test when it is declared globally? Shouldn't the address of AA be in the same range as First and AA(0) ?

My guess:  AA is the address of a local pointer to the global array starting with AA(0).