VBA二进制序列化反序列化：动态创建填充未知维度数组

Great question—dynamic multi-dimensional arrays in VBA can feel like a black box when you don't know the exact dimensions at compile time, especially when building deserialization logic. Let's break down how to replicate that static array behavior dynamically:

Core Approach: Using SafeArray APIs #

VBA arrays under the hood are COM SafeArray structures, which gives us direct control to create arrays of any dimension and bounds via Windows API calls. This is the most reliable method for your deserialization use case, as it matches the exact behavior of statically declared arrays (including custom lower bounds).

First, declare the necessary APIs and types (32/64-bit compatible):

#If VBA7 Then
    Private Declare PtrSafe Sub SafeArrayPutElement Lib "oleaut32.dll" (ByVal psa As LongPtr, ByVal rgIndices As LongPtr, ByVal pvData As LongPtr)
    Private Declare PtrSafe Function SafeArrayCreate Lib "oleaut32.dll" (ByVal vt As Integer, ByVal cDims As Integer, ByVal rgsabound As LongPtr) As LongPtr
    Private Declare PtrSafe Sub SafeArrayDestroy Lib "oleaut32.dll" (ByVal psa As LongPtr)
    Private Declare PtrSafe Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As LongPtr)
#Else
    Private Declare Sub SafeArrayPutElement Lib "oleaut32.dll" (ByVal psa As Long, ByVal rgIndices As Long, ByVal pvData As Long)
    Private Declare Function SafeArrayCreate Lib "oleaut32.dll" (ByVal vt As Integer, ByVal cDims As Integer, ByVal rgsabound As Long) As Long
    Private Declare Sub SafeArrayDestroy Lib "oleaut32.dll" (ByVal psa As Long)
    Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (Destination As Any, Source As Any, ByVal Length As Long)
#End If

Private Type SAFEARRAYBOUND
    cElements As Long ' Number of elements in the dimension
    lLbound As Long   ' Lower bound of the dimension
End Type

Step 1: Create the Dynamic Multi-Dimensional Array #

This function builds an array with your specified dimensions and bounds:

Function CreateDynamicMultiDArray(dimensionCount As Integer, bounds() As SAFEARRAYBOUND) As Variant
    Dim saPointer As LongPtr
    Dim resultArray As Variant
    
    ' Create the underlying SafeArray structure
    saPointer = SafeArrayCreate(vbInteger, dimensionCount, VarPtr(bounds(0)))
    
    If saPointer = 0 Then
        Err.Raise vbObjectError + 1001, , "Failed to create dynamic array: SafeArray initialization failed"
    End If
    
    ' Attach the SafeArray to a Variant (so VBA can interact with it)
    CopyMemory resultArray, saPointer, LenB(saPointer)
    
    Set CreateDynamicMultiDArray = resultArray
End Function

Step 2: Populate Elements Dynamically #

Since we don't know the dimension count at compile time, we use the API to set elements by their index array:

Sub SetMultiDArrayElement(targetArray As Variant, elementIndices() As Long, value As Integer)
    Dim saPointer As LongPtr
    Dim indicesPointer As LongPtr
    
    ' Extract the SafeArray pointer from the Variant
    CopyMemory saPointer, targetArray, LenB(saPointer)
    
    If saPointer = 0 Then
        Err.Raise vbObjectError + 1002, , "Invalid array: Target is not a valid SafeArray"
    End If
    
    ' Get a pointer to our indices array
    indicesPointer = VarPtr(elementIndices(0))
    
    ' Write the value to the specified array position
    SafeArrayPutElement saPointer, indicesPointer, VarPtr(value)
End Sub

Step 3: Test the Implementation #

Here's how to replicate your sample 4-dimensional array and set the value dynamically:

Sub TestDeserializationScenario()
    Dim dimensionBounds(0 To 3) As SAFEARRAYBOUND
    Dim dynamicArray As Variant
    Dim targetIndices(0 To 3) As Long
    Dim targetValue As Integer
    
    ' Define each dimension's bounds (matches your static declaration: 0-3, 0-4, 0-2, 0-2)
    dimensionBounds(0).lLbound = 0: dimensionBounds(0).cElements = 4
    dimensionBounds(1).lLbound = 0: dimensionBounds(1).cElements = 5
    dimensionBounds(2).lLbound = 0: dimensionBounds(2).cElements = 3
    dimensionBounds(3).lLbound = 0: dimensionBounds(3).cElements = 3
    
    ' Create the 4D array
    Set dynamicArray = CreateDynamicMultiDArray(4, dimensionBounds)
    
    ' Define the position to set (3, 1, 1, 0)
    targetIndices(0) = 3: targetIndices(1) = 1: targetIndices(2) = 1: targetIndices(3) = 0
    targetValue = 12345
    
    ' Set the value
    SetMultiDArrayElement dynamicArray, targetIndices, targetValue
    
    ' Verify the result (should print 12345)
    Debug.Print dynamicArray(3, 1, 1, 0)
    
    ' Clean up critical: Avoid memory leaks by destroying the SafeArray
    Dim saPointer As LongPtr
    CopyMemory saPointer, dynamicArray, LenB(saPointer)
    SafeArrayDestroy saPointer
    CopyMemory dynamicArray, 0&, LenB(saPointer)
End Sub

Pure VBA Alternative (Limitations Apply) #

If you want to avoid API calls, you can build arrays incrementally with ReDim Preserve, but this only works for arrays with a lower bound of 0 (unless you use Option Base, which is not recommended for portability). It also only lets you modify the last dimension during each ReDim:

Function CreateMultiDArrayPureVBA(dimensionSizes() As Long) As Variant
    Dim tempArray As Variant
    Dim i As Integer
    
    ' Start with a 1D array
    ReDim tempArray(0 To dimensionSizes(0) - 1)
    
    ' Add each subsequent dimension
    For i = 1 To UBound(dimensionSizes)
        ReDim Preserve tempArray(0 To dimensionSizes(0) - 1, 0 To dimensionSizes(i) - 1)
    Next i
    
    CreateMultiDArrayPureVBA = tempArray
End Function

This is simpler but lacks support for custom lower bounds and is less flexible for complex deserialization workflows.

Key Notes for Deserialization #

• Store Metadata: When serializing, make sure to save the dimension count, each dimension's lower/upper bounds, and the element type (we used vbInteger here, but you can adjust for other types).
• Memory Management: Always clean up SafeArrays with SafeArrayDestroy to avoid memory leaks, especially if you're processing multiple arrays.
• Type Safety: Adjust the vt parameter in SafeArrayCreate to match your data type (e.g., vbLong, vbString).

内容的提问来源于stack exchange，提问作者Bogey