VBA - 在一行中设置多维数组值 [英] VBA - Setting multidimensional array values in one line

问题描述

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

Right, so using Python I would create a multidimensional list and set the values on one line of code (as per the below).

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

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

I like the way I can set the values on one line. In VBA I am doing this by:

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))    

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

Is there a way to set the values in one line of code?

推荐答案

不是原生的，不是.但是你可以为它编写一个函数.Python 能做到这一点的唯一原因是有人编写了一个函数来做到这一点.不同之处在于他们可以访问源代码，因此他们可以随心所欲地编写语法.您将仅限于 VBA 函数语法.这是一个创建二维数组的函数.从技术上讲，这不是一行代码"，而是将它扔到您的 MUtilities 模块中然后忘记它，感觉就像是一行代码.

Not natively, no. But you can write a function for it. The only reason Python can do that is someone wrote a function to do it. The difference is that they had access to the source so they could make the syntax whatever they like. You'll be limited to VBA function syntax. Here's a function to create a 2-dim array. It's not technically 'one line of code', but throw it in your MUtilities module and forget about it and it will feel like one line of code.

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 维数组，您可能不需要我的帮助来编写函数.

If you supply an odd number of arguments, it ignores the last one. If you use 3-dim arrays, you could write a function for that. You could also write a fancy function that could handle any dims, but I'm not sure it's worth it. And if you're using more than 3-dim arrays, you probably don't need my help writing a function.

上面的输出

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

这篇关于VBA - 在一行中设置多维数组值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！