VBA -在一行中设置多维数组值

Question

对，所以使用Python我将创建一个多维列表并在一行代码中设置值(如下所示)。

aryTitle = [["Desciption", "Value"],["Description2", "Value2"]]
print(aryTitle[0,0] + aryTitle[0,1])

我喜欢在一行中设置值的方式。在VBA中，我是这样做的:

Dim aryTitle(0 To 1, 0 To 1) As String
aryTitle(0, 0) = "Description"
aryTitle(0, 1) = "Value"
aryTitle(1, 0) = "Description2"
aryTitle(1, 1) = "Value2"
MsgBox (aryTitle(0, 0) & aryTitle(0, 1))    

是否有办法在一行代码中设置值?

Answer 1

不是天生的，不是。但是你可以为它写一个函数。Python能做到这一点的唯一原因是有人写了一个函数来做到这一点。不同之处在于，他们可以访问源代码，这样他们就可以随心所欲地编写语法。您将仅限于VBA函数语法。下面是一个创建2-dim数组的函数。从技术上讲，它不是"一行代码"，但把它扔到你的多功能模块中，忘记它，它会感觉就像一行代码。

Public Function FillTwoDim(ParamArray KeyValue() As Variant) As Variant
    Dim aReturn() As Variant
    Dim i As Long
    Dim lCnt As Long
    ReDim aReturn(0 To ((UBound(KeyValue) + 1)  2) - 1, 0 To 1)
    For i = LBound(KeyValue) To UBound(KeyValue) Step 2
        If i + 1 <= UBound(KeyValue) Then
            aReturn(lCnt, 0) = KeyValue(i)
            aReturn(lCnt, 1) = KeyValue(i + 1)
            lCnt = lCnt + 1
        End If
    Next i
    FillTwoDim = aReturn
End Function
Sub test()
    Dim vaArr As Variant
    Dim i As Long
    Dim j As Long
    vaArr = FillTwoDim("Description", "Value", "Description2", "Value2")
    For i = LBound(vaArr, 1) To UBound(vaArr, 1)
        For j = LBound(vaArr, 2) To UBound(vaArr, 2)
            Debug.Print i, j, vaArr(i, j)
        Next j
    Next i
End Sub

如果你提供了奇数个参数，它会忽略最后一个。如果你使用3-dim数组，你可以为此写一个函数。您也可以编写一个奇特的函数来处理任何模糊，但我不确定这样做是否值得。如果你使用超过3-dim的数组，你可能不需要我的帮助来写一个函数。

上面

的输出

 0             0            Description
 0             1            Value
 1             0            Description2
 1             1            Value2

Answer 2

你可以写一个辅助函数:

Function MultiSplit(s As String, Optional delim1 As String = ",", Optional delim2 As String = ";") As Variant
    Dim V As Variant, W As Variant, A As Variant
    Dim i As Long, j As Long, m As Long, n As Long
    V = Split(s, delim2)
    m = UBound(V)
    n = UBound(Split(V(0), delim1))
    ReDim A(0 To m, 0 To n)
    For i = 0 To m
        For j = 0 To n
            W = Split(V(i), delim1)
            A(i, j) = Trim(W(j))
        Next j
    Next i
    MultiSplit = A
End Function

像这样使用:

Sub test()
    Dim A As Variant
    A = MultiSplit("Desciption, Value; Description2, Value2")
    Range("A1:B2").Value = A
End Sub