检查数组是否为空（vba excel）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26290781/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
check if array is empty (vba excel)
提问by user147178
These if ... then statements are getting the wrong results in my opinion. The first is returning the value 'false' when it should be 'true'. The fourth returns the right value. The second and third return an error.
这些 if ... then 语句在我看来得到了错误的结果。第一个是当它应该是“真”时返回值“假”。第四个返回正确的值。第二个和第三个返回错误。
Sub empty_array()
Dim arr1() As Variant
If IsEmpty(arr1) Then
MsgBox "hey"
End If
If IsError(UBound(arr1)) Then
MsgBox "hey"
End If
If IsError(Application.match("*", (arr1), 0)) Then
MsgBox "hey"
End If
ReDim arr1(1)
arr1(1) = "hey"
If IsEmpty(arr1) Then
MsgBox "hey"
End If
End Sub
回答by Fumu 7
Arr1 becomes an array of 'Variant' by the first statement of your code:
Arr1 通过您的代码的第一条语句成为“Variant”数组：
Dim arr1() As Variant
Array of size zero is not empty, as like an empty box exists in real world.
大小为零的数组不是空的，就像现实世界中存在一个空盒子一样。
If you define a variable of 'Variant', that will be empty when it is created.
如果您定义了一个“Variant”变量，那么它在创建时将为空。
Following code will display "Empty".
以下代码将显示“空”。
Dim a as Variant
If IsEmpty(a) then
MsgBox("Empty")
Else
MsgBox("Not Empty")
End If
回答by klobster
I would do this as
我会这样做
if isnumeric(ubound(a)) = False then msgbox "a is empty!"
回答by jeminar
Adding into this: it depends on what your array is defined as. Consider:
添加到此：这取决于您的数组定义为什么。考虑：
dim a() as integer
dim b() as string
dim c() as variant
'these doesn't work
if isempty(a) then msgbox "integer arrays can be empty"
if isempty(b) then msgbox "string arrays can be empty"
'this is because isempty can only be tested on classes which have an .empty property
'this do work
if isempty(c) then msgbox "variants can be empty"
So, what can we do? In VBA, we can see if we can trigger an error and somehow handle it, for example
所以，我们能做些什么？在 VBA 中，我们可以查看是否可以触发错误并以某种方式处理它，例如
dim a() as integer
dim bEmpty as boolean
bempty=false
on error resume next
bempty=not isnumeric(ubound(a))
on error goto 0
But this is really clumsy... A nicer solution is to declare a boolean variable (a public or module level is best). When the array is first initialised, then set this variable. Because it's a variable declared at the same time, if it loses it's value, then you know that you need to reinitialise your array. However, if it is initialised, then all you're doing is checking the value of a boolean, which is low cost. It depends on whether being low cost matters, and if you're going to be needing to check it often.
但这真的很笨拙......更好的解决方案是声明一个布尔变量（公共或模块级别最好）。当数组第一次初始化时，然后设置这个变量。因为它是一个同时声明的变量，如果它失去了它的值，那么你知道你需要重新初始化你的数组。但是，如果它已初始化，那么您所做的就是检查布尔值的值，这是低成本的。这取决于低成本是否重要，以及您是否需要经常检查它。
option explicit
'declared at module level
dim a() as integer
dim aInitialised as boolean
sub DoSomethingWithA()
if not aInitialised then InitialiseA
'you can now proceed confident that a() is intialised
end sub
sub InitialiseA()
'insert code to do whatever is required to initialise A
'e.g.
redim a(10)
a(1)=123
'...
aInitialised=true
end sub
The last thing you can do is create a function; which in this case will need to be dependent on the clumsy on error method.
您可以做的最后一件事是创建一个函数；在这种情况下，这将需要依赖于笨拙的错误方法。
function isInitialised(byref a() as variant) as boolean
isInitialised=false
on error resume next
isinitialised=isnumeric(ubound(a))
end function
回答by Kevin Conner
@jeminar has the best solution above.
@jeminar 有上面的最佳解决方案。
I cleaned it up a bit though.
不过我清理了一下。
I recommend adding this to a FunctionsArray module
我建议将其添加到 FunctionsArray 模块中
isInitialised=falseis not needed because Booleans are false when createdOn Error GoTo 0wrap and indent code inside error blocks similar towithblocks for visibility. these methods should be avoided as much as possible but ... VBA ...
isInitialised=false不需要，因为创建时布尔值是假的On Error GoTo 0在错误块内包装和缩进类似于with可见性块的代码。应尽可能避免使用这些方法，但是... VBA ...
Function isInitialised(ByRef a() As Variant) As Boolean
On Error Resume Next
isInitialised = IsNumeric(UBound(a))
On Error GoTo 0
End Function
回答by cyberponk
Above methods didn′t work for me. This did:
以上方法对我不起作用。这做到了：
Dim arrayIsNothing As Boolean
On Error Resume Next
arrayIsNothing = IsNumeric(UBound(YOUR_ARRAY)) And False
If Err.Number <> 0 Then arrayIsNothing = True
Err.Clear
On Error GoTo 0
'Now you can test:
if arrayIsNothing then ...
回答by Egaeus
cyberponk's method worked for me (upvoted, I swear!). The others had various problems. However, you do need to remember to add an Err.Clearafter checking Err.Numberto be able to reuse this method because the error number is persistent, which will cause false positives when checking that the arrayIsNothing.
Cyberponk 的方法对我有用（赞成，我发誓！）。其他人有各种各样的问题。但是，您确实需要记住添加Err.Clearafter 检查Err.Number才能重用此方法，因为错误号是持久的，这会在检查arrayIsNothing.
