vba 如何检查变体数组是否未分配？

How to check whether a variant array is unallocated?

提问by jason m

   Dim Result() As Variant

In my watch window, this appears as

在我的观察窗口中，这显示为

Expression | Value | Type
Result     |       | Variant/Variant()

How do I check the following:

我如何检查以下内容：

   if Result is nothing then

or

或者

   if Result is Not Set then

This is basically what I am trying to accomplish, but the first one does not work and the second does not exist.

这基本上就是我想要完成的，但第一个不起作用，第二个不存在。

采纳答案by Jean-Fran?ois Corbett

Chip Pearson made a useful module called modArraySupportthat contains a bunch of functions to test for things like this. In your case, you would want to use IsArrayAllocated.

Chip Pearson 制作了一个名为modArraySupport的有用模块，其中包含一系列用于测试此类问题的函数。在您的情况下，您会想要使用IsArrayAllocated.

Public Function IsArrayAllocated(Arr As Variant) As Boolean

This function returns TRUE or FALSE indicating whether the specified array is allocated (not empty). Returns TRUE of the array is a static array or a dynamic that has been allocated with a Redim statement. Returns FALSE if the array is a dynamic array that has not yet been sized with ReDim or that has been deallocated with the Erase statement. This function is basically the opposite of ArrayIsEmpty. For example,

此函数返回 TRUE 或 FALSE，指示指定的数组是否已分配（非空）。返回 TRUE 的数组是静态数组或已使用 Redim 语句分配的动态数组。如果数组是尚未使用 ReDim 调整大小或已使用 Erase 语句解除分配的动态数组，则返回 FALSE。这个函数基本上与ArrayIsEmpty 相反。例如，

Dim Result() As Variant
Dim R As Boolean
R = IsArrayAllocated(Result)  ' returns false
ReDim V(1 To 10)
R = IsArrayAllocated(Result)  ' returns true

The technique used is basically to test the array bounds (as suggested by @Tim Williams) BUT with an extra gotcha.

所使用的技术基本上是为了测试数组边界（如@Tim Williams 所建议的那样）但是有一个额外的问题。

To test in your immediate window:

要在您的即时窗口中进行测试：

?IsArrayAllocated(Result)

Testing in Watch window: there are may ways to do this; for example, add a watch on Rand under "Watch Type" select "Break When Value Changes".

在观察窗口中测试：有多种方法可以做到这一点；例如，R在“Watch Type”上和下添加一个 watch，选择“Break When Value Changes”。

回答by RandomCoder

To avoid error handling, I used this, seen on a forum long time ago and used sucessfully since then:

为了避免错误处理，我使用了这个，很久以前在论坛上看到的，从那时起就成功使用了：

If (Not Not Result) <> 0 Then 'Means it is allocated

or alternatively

或者

If (Not Not Result) = 0 Then 'Means it is not allocated

I used this mainly to extend array size from unset array this way

我用它主要是通过这种方式从未设置的数组扩展数组大小

'Declare array
Dim arrIndex() As Variant        

'Extend array
If (Not Not Result) = 0 Then
    ReDim Preserve Result(0 To 0)
Else
    ReDim Preserve Result(0 To UBound(Result) + 1)
End If

回答by Thomas

You can use the following in the immediate window:

您可以在即时窗口中使用以下内容：

?Result Is Nothing
?IsNull( Result )
?IsEmpty( Result )
?IsMissing( Result )

The first is simply for completeness. Since Result is not an object, Result Is Nothingwill throw an error. Emptyis for variants that have not been initialized including arrays which have not been dimensioned..

第一个只是为了完整性。由于 Result 不是对象，因此Result Is Nothing会抛出错误。Empty用于尚未初始化的变体，包括尚未确定尺寸的数组。.

(Update) In doing some additional checking, I have discovered that IsEmpty will never return true on a declared array (whether Redim'd or not) with only one exception. The only exception I found is when the array is declared at the module level and not as Public and then only when you check it in the immediate window.

（更新）在做一些额外的检查时，我发现 IsEmpty 永远不会在声明的数组（无论是否 Redim'd）上返回 true，只有一个例外。我发现的唯一例外是当数组在模块级别声明而不是 Public 时，然后仅当您在即时窗口中检查它时。

Missingif for optional values passed to a function or sub. While you cannot declare Optional Foo() As Variant, you could have something like ParamArray Foo() As Variantin which case if nothing is passed, IsMissingwould return true.

Missing如果用于传递给函数或子的可选值。虽然您不能声明Optional Foo() As Variant，但您可能会遇到类似ParamArray Foo() As Variant情况，如果没有传递任何内容，IsMissing将返回 true。

Thus, the only way to determine if the array is initialized is to write a procedure that would check:

因此，确定数组是否已初始化的唯一方法是编写一个程序来检查：

Public Function IsDimensioned(vValue As Variant) As Boolean
    On Error Resume Next
    If Not IsArray(vValue) Then Exit Function
    Dim i As Integer
    i = UBound(Bar)
    IsDimensioned = Err.Number = 0
End Function

Btw, it should be noted that this routine (or the library posted by Jean-Fran?ois Corbett) will return false if the array is dimensioned and then erased.

顺便说一句，应该注意这个例程（或由 Jean-Fran?ois Corbett 发布的库）如果数组被标注然后被擦除将返回 false。

回答by Tmdean

I recommend a slightly different approach because I think using language artifacts like (Not Array) = -1to check for initialization is difficult to read and causes maintenance headaches.

我推荐一种稍微不同的方法，因为我认为使用像(Not Array) = -1检查初始化这样的语言工件很难阅读并导致维护问题。

If you are needing to check for array allocation, most likely it's because you're trying to make your own "vector" type: an array that grows during runtime to accommodate data as it is being added. VBA makes it fairly easy to implement a vector type, if you take advantage of the type system.

如果您需要检查数组分配，很可能是因为您正在尝试创建自己的“向量”类型：一个在运行时增长以容纳添加数据的数组。如果您利用类型系统，VBA 可以很容易地实现向量类型。

Type Vector
    VectorData() As Variant
    VectorCount As Long
End Type

Dim MyData As Vector

Sub AddData(NewData As Variant)
    With MyData
        ' If .VectorData hasn't been allocated yet, allocate it with an
        ' initial size of 16 elements.
        If .VectorCount = 0 Then ReDim .VectorData(1 To 16)

        .VectorCount = .VectorCount + 1

        ' If there is not enough storage for the new element, double the
        ' storage of the vector.
        If .VectorCount > UBound(.VectorData) Then
            ReDim Preserve .VectorData(1 To UBound(.VectorData) * 2)
        End If

        .VectorData(.VectorCount) = NewData
    End With
End Sub

' Example of looping through the vector:
For I = 1 To MyData.VectorCount
    ' Process MyData.VectorData(I)
Next

Notice how there's no need to check for array allocation in this code, because we can just check the VectorCountvariable. If it's 0, we know that nothing has been added to the vector yet and therefore the array is unallocated.

请注意，在此代码中无需检查数组分配，因为我们只需检查VectorCount变量即可。如果它是 0，我们知道还没有向向量添加任何内容，因此该数组是未分配的。

Not only is this code simple and straightforward, vectors also have all the performance advantages of an array, and the amortized cost for adding elements is actually O(1), which is very efficient. The only tradeoff is that, due to how the storage is doubled every time the vector runs out of space, in the worst case 50% of the vector's storage is wasted.

这段代码不仅简单明了，向量还具备数组的所有性能优势，而且添加元素的摊销成本实际上是O(1)，非常高效。唯一的权衡是，由于每次矢量空间用完时存储空间都会翻倍，在最坏的情况下，矢量存储空间的 50% 被浪费了。

回答by Andras Dorko

I think Line 4 of the accepted answer should say:

我认为已接受答案的第 4 行应该说：

ReDim Result(1 To 10)

Instead of:

代替：

ReDim V(1 To 10)

I believe Line 5:

我相信第 5 行：

R = IsArrayAllocated(Result)  ' returns true

is going to return FALSE just like Line 3:

将像第 3 行一样返回 FALSE：

R = IsArrayAllocated(Result)  ' returns false

because Result is not what is redimensioned in Line 4.

因为 Result 不是第 4 行中重新定义的。

In Line 4 an Array called V is attempted to be redimensioned, but it is not even declared.

在第 4 行中，尝试重新定义名为 V 的数组，但它甚至没有声明。

回答by Tim Williams

Check the LBoundof the array. If you get an error then it's uninitialized.

检查LBound阵列的 。如果出现错误，则说明它未初始化。