Excel中向量叉积的应用：3D数组与ReDim使用问题

Hey there! Let's break down how to solve this problem in VBA. The core issue here is that traditional fixed-dimension arrays can't handle matrices of different sizes—so we need a more flexible approach. Here's how to store your variable-size arrays and retrieve specific groups later:

VBA doesn't support "jagged" 3D arrays directly (where each "slice" has different row/column counts), but we can work around this by using a 1D Variant array where each element holds its own 2D matrix. Alternatively, a VBA Collection works great too for easier appending.

Step 1: Initialize Your Individual Matrices #

First, let's create and populate the sample 3x3, 5x5, etc. arrays you mentioned:

Sub SetupSampleMatrices()
    Dim Arr1(1 To 3, 1 To 3) As Integer
    Dim Arr2(1 To 5, 1 To 5) As Integer
    Dim Arr3(1 To 7, 1 To 7) As Integer
    Dim Arr4(1 To 9, 1 To 9) As Integer
    Dim Arr5(1 To 11, 1 To 11) As Integer
    
    ' Populate sample data (adjust as needed)
    Dim i As Integer, j As Integer
    For i = 1 To 3
        For j = 1 To 3
            Arr1(i, j) = i * j ' Fill with simple multiplication values
        Next j
    Next i
    
    For i = 1 To 5
        For j = 1 To 5
            Arr2(i, j) = i + j ' Fill with addition values
        Next j
    Next i
    
    ' Repeat similar population for Arr3-Arr5 if you need test data
End Sub

Step 2: Build a Master Container for All Matrices #

Option 1: Using a Variant Array #

We'll dynamically resize this array to add each matrix one by one:

Sub BuildMasterVariantArray()
    ' Re-initialize sample arrays (or call SetupSampleMatrices here)
    Dim Arr1(1 To 3, 1 To 3) As Integer, Arr2(1 To 5, 1 To 5) As Integer
    Dim Arr3(1 To 7, 1 To 7) As Integer, Arr4(1 To 9, 1 To 9) As Integer
    Dim Arr5(1 To 11, 1 To 11) As Integer
    
    ' Populate sample data quickly
    Dim i As Integer, j As Integer
    For i = 1 To 3: For j = 1 To 3: Arr1(i, j) = i*j: Next j: Next i
    For i = 1 To 5: For j = 1 To 5: Arr2(i, j) = i+j: Next j: Next i
    For i = 1 To 7: For j = 1 To 7: Arr3(i, j) = i-j: Next j: Next i
    For i = 1 To 9: For j = 1 To 9: Arr4(i, j) = i^2: Next j: Next i
    For i = 1 To 11: For j = 1 To 11: Arr5(i, j) = j^2: Next j: Next i
    
    ' Initialize master variant array
    Dim masterArr As Variant
    ReDim masterArr(1 To 1) ' Start with space for 1 matrix
    
    ' Append each matrix to the master array
    masterArr(1) = Arr1
    ReDim Preserve masterArr(1 To 2): masterArr(2) = Arr2
    ReDim Preserve masterArr(1 To 3): masterArr(3) = Arr3
    ReDim Preserve masterArr(1 To 4): masterArr(4) = Arr4
    ReDim Preserve masterArr(1 To 5): masterArr(5) = Arr5
    
    ' Test reading the 2nd group (Arr2)
    ReadMatrixFromGroup masterArr, 2
End Sub

Option 2: Using a Collection (Easier for Appending) #

Collections let you add items without manual resizing, and you can even reference groups by name:

Sub BuildMatrixCollection()
    ' Initialize sample arrays and populate data (same as above)
    Dim Arr1(1 To 3, 1 To 3) As Integer, Arr2(1 To 5, 1 To 5) As Integer
    Dim Arr3(1 To 7, 1 To 7) As Integer, Arr4(1 To 9, 1 To 9) As Integer
    Dim Arr5(1 To 11, 1 To 11) As Integer
    
    ' Populate data...
    
    Dim matrixCol As New Collection
    ' Add matrices with optional keys for easy reference
    matrixCol.Add Arr1, "3x3_Group"
    matrixCol.Add Arr2, "5x5_Group"
    matrixCol.Add Arr3, "7x7_Group"
    matrixCol.Add Arr4, "9x9_Group"
    matrixCol.Add Arr5, "11x11_Group"
    
    ' Read the 2nd group (by index or key)
    Dim targetMatrix As Variant
    targetMatrix = matrixCol(2) ' Or matrixCol("5x5_Group")
    ' Process the matrix as needed...
End Sub

Step 3: Read Data from a Specific Group #

Create a helper routine to extract and work with any group's matrix:

Sub ReadMatrixFromGroup(masterArr As Variant, groupNum As Integer)
    ' Validate group number
    If groupNum < LBound(masterArr) Or groupNum > UBound(masterArr) Then
        MsgBox "Invalid group number! Please enter a number between " & LBound(masterArr) & " and " & UBound(masterArr)
        Exit Sub
    End If
    
    ' Extract the target matrix
    Dim targetMatrix As Variant
    targetMatrix = masterArr(groupNum)
    
    ' Get the matrix dimensions (works even if arrays aren't 1-based)
    Dim totalRows As Integer, totalCols As Integer
    totalRows = UBound(targetMatrix, 1) - LBound(targetMatrix, 1) + 1
    totalCols = UBound(targetMatrix, 2) - LBound(targetMatrix, 2) + 1
    
    ' Example: Print all values to the Immediate Window (Ctrl+G to view)
    Debug.Print "Group " & groupNum & " (" & totalRows & "x" & totalCols & "):"
    Dim i As Integer, j As Integer
    For i = LBound(targetMatrix, 1) To UBound(targetMatrix, 1)
        Dim rowOutput As String
        rowOutput = ""
        For j = LBound(targetMatrix, 2) To UBound(targetMatrix, 2)
            rowOutput = rowOutput & targetMatrix(i, j) & vbTab
        Next j
        Debug.Print rowOutput
    Next i
End Sub

Key Takeaways #

• Variant Arrays: The only way to store variable-size matrices in a single array structure in VBA—each element acts as a container for its own 2D array.
• ReDim Preserve: When resizing the master array, use this to keep existing data intact (note: it only works on the last dimension of an array, hence why we use a 1D master array).
• Collections: A simpler alternative if you don't need array-specific operations—great for adding/removing items dynamically and referencing groups by name.

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