创建一个空的二维数组 [英] Create an empty 2d array

问题描述

我不喜欢未初始化的VBA数组，因为每次使用前都要检查

有没有更优雅的方法来创建空的2d数组，而无需使用 ComboBox 或 UserForm 控件？

解决方案

爱德克（男士）-我认为您绊倒这个物业真是太疯狂了。

我可能会在这里停下来，然后做：

 函数Empty2DArray（）作为具有CreateObject（ Forms.ComboBox.1）的
变体
 .List = Array（）
 Empty2DArray = .List 
结尾为
结束函数
  

并像这样使用它： a = Empty2DArray

无需创建用户窗体或组合框-您只需使用 CreateObject 。

但是正如其他人所说的，在检查数组是否已初始化时，进行错误处理可能更有意义。

I don't like uninitialized VBA arrays, since it's necessary to check if array is initialized, each time prior using UBound() or For Each to avoid an exception, and there is no native VBA function to check it. That is why I initialize arrays, at least doing them empty with a = Array(). This eliminates the need for extra check in most of cases, so there are no problems with 1d arrays.

For the same reason I tried to create an empty 2d array. It's not possible simply do ReDim a(0 To -1, 0 To 0), transpose 1d empty array or something similar. The only way I came across by chance, is to use MSForms.ComboBox, assign empty array to .List property and read it back. Here is the example, which works in Excel and Word, you need to insert UserForm to VBA Project, place ComboBox on it, and add the below code:

Private Sub ComboBox1_Change()

    Dim a()
    ComboBox1.List = Array()
    a = ComboBox1.List
    Debug.Print "1st dimension upper bound = " & UBound(a, 1)
    Debug.Print "2nd dimension upper bound = " & UBound(a, 2)

End Sub

After combo change the output is:

1st dimension upper bound = -1
2nd dimension upper bound = 0

Actually it's really the empty 2d array in debug:

Is there more elegant way to create an empty 2d array, without using ComboBox, or UserForm controls in general?

解决方案

Idk man - I think you stumbling onto this property was pretty wild.

I'd probably stop here and just do:

Function Empty2DArray() As Variant
With CreateObject("Forms.ComboBox.1")
    .List = Array()
    Empty2DArray = .List
End With
End Function

And use it like: a = Empty2DArray

You don't need to create the userform or combobox - you can just use CreateObject.

But as others have said, it probably makes more sense to do error handling when checking whether or not your arrays are initialized.

这篇关于创建一个空的二维数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！