How To Fill a 32-bit VBA Array of UDType via a Visual C++ DLL

• Resizing the array.
• UDTs that contain variable length strings, Objects, or Variants.
• Multi-dimension arrays.

Back to the top

Issues

1. When passing an array of UDTs with string elements, the strings are not converted from UNICODE to ANSI as when passing a variable of UDT.
2. Elements of a UDT in Visual Basic use a 4-byte alignment. The default Visual C++ alignment is on 8-byte boundaries, so you have to explicitly set a smaller alignment.
3. In Visual Basic, UNICODE strings are implemented as an array of bytes; in Visual C++, they are implemented as an array of unsigned short int. This means that Visual Basic aligns UNICODE characters on 1-byte boundaries and Visual C++ aligns them on 2-byte boundaries. Because of this, your Visual C++ code has to specify 1-byte alignment as opposed to 4-byte alignment if you have any fixed-length strings in your UDT. With the 1-byte alignment, you will have to add filler fields in your Visual C++ struct to fix the alignment of other types on a 4-byte boundary.
4. Visual C++ mangles function names. You will need to build a .DEF file to make the names readable by Visual Basic.

Type         Alignment   Size

Byte          1 byte     1 byte
String * n    1 byte     2 bytes per UNICODE character
Integer       2 bytes    2 bytes
Boolean       2 bytes    2 bytes
String        4 bytes    4 byte pointer - UNICODE data not in structure
Long          4 bytes    4 bytes
Single        4 bytes    4 bytes
Double        4 bytes    8 bytes
Currency      4 bytes    8 bytes
Date          4 bytes    8 bytes
Variant       4 bytes    16 bytes - may point to data not in structure
Object        4 bytes    4 byte pointer - object not in structure
				

   unsigned short cDims;   // Count of dimensions
   unsigned short fFeatures;  // Flags
   unsigned long cbElements;  // Size of an element of the array
   unsigned long cLocks;   // Lock count
   void HUGEP* pvData;     // Pointer to the data
   SAFEARRAYBOUND rgsabound[n];  // One bound for each dimension
				

   unsigned long cElements;   // Number of elements in this dimension
   long lLbound;     // Lower bound
				

Back to the top

Step-by-Step Example

1. Create a new Project Workspace of Dynamic-Link Library type and save as TestType.
2. Create a Text file and save as TestType.DEF. Add the file to the project:

   LIBRARY   TestType
   
      EXPORTS
        FillUDTSafeArray
        FillUDTVariable
   					

3. Create a Text file and save as TestType.CPP. Add the file to the project:

         #include <windows.h>
         #include <ole2.h>
   
         #pragma pack(1)
   
         typedef struct
         {
         short f1;
         short filler_1;
         long f2;
         BYTE f3;
         WCHAR f4;
         BYTE filler_2;
         float f5;
         } MyStruct;
   
         void _stdcall FillUDTVariable(MyStruct * ms)
         {
         ms->f1 = 2001;
         ms->f2 = 20012001;
         ms->f3 = 255;
         ms->f4 = L'A';
         ms->f5 = 200.1f;
         }
   
         void _stdcall FillUDTSafeArray(LPSAFEARRAY FAR * ppsa)
         {
         MyStruct * pdata;
         unsigned int i;
         pdata = (MyStruct*)((*ppsa)->pvData);
         for (i=0;i<((*ppsa)->rgsabound->cElements);i++,pdata++)
            FillUDTVariable(pdata);
         }
   					

4. Build the DLL.
5. In your VBA application, add a Module with the following code: (You will need to explicitly specify the path of the DLL or move the DLL to the system directory, application directory, or elsewhere on the path.)

      Option Explicit
   
         Type My_VarUDT
           F1 As Integer
           F2 As Long
           F3 As Byte
           F4(0 To 1) As Byte  ' to avoid UNICODE/ANSI conversion
           F5 As Single
         End Type
   
         Type My_ArrayUDT
           F1 As Integer
           F2 As Long
           F3 As Byte
           F4 As String * 1
           F5 As Single
         End Type
   
         Declare Sub FillUDTVariable Lib "TestType.DLL" (A As My_VarUDT)
         Declare Sub FillUDTSafeArray Lib "TestType.DLL" (A() As My_ArrayUDT)
   
         Sub Test()
         Dim A As Long, B As My_VarUDT, C As String, D(3) As My_ArrayUDT
           Debug.Print "---Variable of My_VarUDT-------"
           FillUDTVariable B
           With B
             C = .F4
             Debug.Print .F1, .F2, .F3, C; "("; .F4(0); .F4(1); ")", .F5
           End With
           Debug.Print "---Safe array of My_ArrayUDT-------"
           FillUDTSafeArray D()
           For A = 0 To 3
             With D(A)
               Debug.Print .F1, .F2, .F3, .F4; "("; AscB(MidB(.F4, 1, 1));
               Debug.Print AscB(MidB(.F4, 2, 1)); ")", .F5
             End With
           Next A
         End Sub
   					

6. Save your VBA project and test the code by typing Test in the Immediate Window. The output should appear as follows:

      ---Variable of My_VarUDT-------
       2001          20012001      255          A( 65  0 )     200.1
      ---Safe array of My_ArrayUDT-------
       2001          20012001      255          A( 65  0 )     200.1
       2001          20012001      255          A( 65  0 )     200.1
       2001          20012001      255          A( 65  0 )     200.1
       2001          20012001      255          A( 65  0 )     200.1
   					

   NOTE: The My_VarUDT example is included to illustrate avoiding the normal UNICODE/ANSI conversion that happens when calling a DLL and passing a single variable. This conversion does not happen when passing the entire array, so can use STRING * 1 in the My_ArrayUDT as opposed to passing a byte array.

Back to the top