如何在vba中为excel初始化多维数组变量 [英] How to initialize a multidimensional array variable in vba for excel

问题描述

Microsoft网站建议以下代码应该有效：

Dim numbers = {{1，2}，{3，4}，{5，6}} p>

但是，当我尝试在excel VBA模块中使用它时，我会收到一个错误的错误。
以下内容适用于1D数组：

A =数组（1，2，3，4，5） code>

但是我没有找到一种为2D数组做同样的方法。
任何想法？

解决方案

您还可以使用速记格式，利用 Evaluate 函数和静态数组。在下面的代码中， varData 设置为 [] 是 Evaluate 函数和 {...} 表达式表示一个静态数组。每行都用; 分隔，每个字段都用，分隔。它使您得到与simoco代码相同的最终结果，但语法更接近您的原始问题：

  Sub ArrayShorthand（） 
 
 Dim varData As Variant 
 Dim intCounter1 As Integer 
 Dim intCounter2 As Integer 
 
'设置数组
 varData = [{1， 2，3; 4，5，6; 7，8，9}] 
 
'test 
对于intCounter1 = 1 To UBound（varData，1）
对于intCounter2 = 1 To UBound（varData，2）
 Debug.Print varData（intCounter1，intCounter2）
下一个intCounter2 
下一个intCounter1 
 
 End Sub 
  




The Microsoft site suggests the following code should work:

Dim numbers = {{1, 2}, {3, 4}, {5, 6}}

However I get a complile error when I try to use it in an excel VBA module. The following does work for a 1D array:

A = Array(1, 2, 3, 4, 5)

However I have not managed to find a way of doing the same for a 2D array. Any ideas?

解决方案

You can also use a shorthand format leveraging the Evaluate function and a static array. In the code below, varData is set where [] is the shorthand for the Evaluate function and the {...} expression indicates a static array. Each row is delimited with a ; and each field delimited with a ,. It gets you to the same end result as simoco's code, but with a syntax closer to your original question:

Sub ArrayShorthand()

    Dim varData As Variant
    Dim intCounter1 As Integer
    Dim intCounter2 As Integer

    ' set the array
    varData = [{1, 2, 3; 4, 5, 6; 7, 8, 9}]

    ' test
    For intCounter1 = 1 To UBound(varData, 1)
        For intCounter2 = 1 To UBound(varData, 2)
            Debug.Print varData(intCounter1, intCounter2)
        Next intCounter2
    Next intCounter1

End Sub

这篇关于如何在vba中为excel初始化多维数组变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！