vba 如何在vba宏中检查空数组

How to check for empty array in vba macro

提问by Vicky

I want to check for empty arrays. Google gave me varied solutions but nothing worked. Maybe I am not applying them correctly.

我想检查空数组。谷歌给了我各种解决方案，但没有任何效果。也许我没有正确应用它们。

Function GetBoiler(ByVal sFile As String) As String
'Email Signature
    Dim fso As Object
    Dim ts As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
    GetBoiler = ts.ReadAll
    ts.Close
End Function

Dim FileNamesList As Variant, i As Integer
' activate the desired startfolder for the filesearch
FileNamesList = CreateFileList("*.*", False) ' Returns File names
' performs the filesearch, includes any subfolders
' present the result
' If there are Signatures then populate SigString
Range("A:A").ClearContents
For i = 1 To UBound(FileNamesList)
    Cells(i + 1, 1).Formula = FileNamesList(i)
Next i

SigString = FileNamesList(3)

If Dir(SigString) <> "" Then
    Signature = GetBoiler(SigString)
Else
    Signature = ""
End If

Here if FileNamesListarray is empty, GetBoiler(SigString)should not get called at all. When FileNamesListarray is empty, SigStringis also empty and this calls GetBoiler()function with empty string. I get an error at line

如果FileNamesList数组为空，GetBoiler(SigString)则根本不应调用。当FileNamesList数组为空时，SigString也为空，这会调用GetBoiler()带有空字符串的函数。我在行出错

Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)

since sFileis empty. Any way to avoid that?

因为sFile是空的。有什么办法可以避免吗？

回答by Fionnuala

As you are dealing with a string array, have you considered Join?

在处理字符串数组时，是否考虑过加入？

If Len(Join(FileNamesList)) > 0 Then

回答by ahuth

Go with a triple negative:

使用三重否定：

If (Not Not FileNamesList) <> 0 Then
    ' Array has been initialized, so you're good to go.
Else
    ' Array has NOT been initialized
End If

Or just:

要不就：

If (Not FileNamesList) = -1 Then
    ' Array has NOT been initialized
Else
    ' Array has been initialized, so you're good to go.
End If

In VB, for whatever reason, Not myArrayreturns the SafeArray pointer. For uninitialized arrays, this returns -1. You can Notthis to XOR it with -1, thus returning zero, if you prefer.

在 VB 中，无论出于何种原因，都Not myArray返回 SafeArray 指针。对于未初始化的数组，这将返回 -1。Not如果您愿意，您可以将其与 -1 进行异或，从而返回零。

               (Not myArray)   (Not Not myArray)
Uninitialized       -1                 0
Initialized    -someBigNumber   someOtherBigNumber

Source

来源

回答by Lance Roberts

If you test on an array function it'll work for all bounds:

如果您对数组函数进行测试，它将适用于所有边界：

Function IsVarArrayEmpty(anArray As Variant)

Dim i As Integer

On Error Resume Next
    i = UBound(anArray,1)
If Err.number = 0 Then
    IsVarArrayEmpty = False
Else
    IsVarArrayEmpty = True
End If

End Function

回答by Perposterer

I see similar answers on here... but not mine...

我在这里看到类似的答案......但不是我的......

This is how I am unfortunatley going to deal with it... I like the len(join(arr)) > 0 approach, but it wouldn't work if the array was an array of emptystrings...

这就是我不幸要处理它的方式......我喜欢 len(join(arr)) > 0 方法，但如果数组是空字符串数组，它就不起作用......

Public Function arrayLength(arr As Variant) As Long
  On Error GoTo handler

  Dim lngLower As Long
  Dim lngUpper As Long

  lngLower = LBound(arr)
  lngUpper = UBound(arr)

  arrayLength = (lngUpper - lngLower) + 1
  Exit Function

handler:
  arrayLength = 0 'error occured.  must be zero length
End Function

回答by BBQ Chef

When writing VBA there is this sentence in my head: "Could be so easy, but..."

在编写 VBA 时，我的脑海中浮现出这样一句话：“可能很简单，但是……”

Here is what I adopted it to:

这是我采用它的目的：

Private Function IsArrayEmpty(arr As Variant)
  ' This function returns true if array is empty
  Dim l As Long

  On Error Resume Next
  l = Len(Join(arr))
  If l = 0 Then
    IsArrayEmpty = True
  Else
    IsArrayEmpty = False
  End If

  If Err.Number > 0 Then
      IsArrayEmpty = True
  End If

  On Error GoTo 0
End Function

Private Sub IsArrayEmptyTest()
  Dim a As Variant
  a = Array()
  Debug.Print "Array is Empty is " & IsArrayEmpty(a)
  If IsArrayEmpty(a) = False Then
    Debug.Print "  " & Join(a)
  End If
End Sub

回答by Mike Spross

This code doesn't do what you expect:

此代码不符合您的预期：

If Dir(SigString) <> "" Then
    Signature = GetBoiler(SigString) 
Else
    Signature = "" 
End If

If you pass an empty string ("") or vbNullStringto Dir, it will return the name of the first file in the current directory path (the path returned by CurDir$). So, if SigStringis empty, your Ifcondition will evaluate to Truebecause Dirwill return a non-empty string (the name of the first file in the current directory), and GetBoilerwill be called. And if SigStringis empty, the call to fso.GetFilewill fail.

如果传递空字符串 ( "") 或vbNullStringto Dir，它将返回当前目录路径中第一个文件的名称（由 返回的路径CurDir$）。因此，如果SigString为空，您的If条件将评估为，True因为Dir将返回一个非空字符串（当前目录中第一个文件的名称），GetBoiler并将被调用。如果SigString为空，则调用fso.GetFile将失败。

You should either change your condition to check that SigStringisn't empty, or use the FileSystemObject.FileExistsmethod instead of Dirfor checking if the file exists. Diris tricky to use precisely because it does things you might not expect it to do. Personally, I would use Scripting.FileSystemObjectover Dirbecause there's no funny business (FileExistsreturns Trueif the file exists, and, well, Falseif it doesn't). What's more, FileExistsexpresses the intentof your code much clearly than Dir.

您应该更改条件以检查它SigString不为空，或者使用该FileSystemObject.FileExists方法而不是Dir检查文件是否存在。Dir使用起来很棘手，因为它可以完成您可能不希望它做的事情。就个人而言，我会使用Scripting.FileSystemObjectoverDir因为没有什么有趣的事情（如果文件存在则FileExists返回，True如果文件不存在则返回False）。更重要的是，FileExists表达了意向代码的远远清楚Dir。

Method 1: Check that SigStringis non-empty first

方法一：先检查SigString非空

If SigString <> "" And Dir(SigString) <> "" Then
    Signature = GetBoiler(SigString) 
Else
    Signature = "" 
End If

Method 2: Use the FileSystemObject.FileExistsmethod

方法二：使用FileSystemObject.FileExists方法

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

If fso.FileExists(SigString) Then
    Signature = GetBoiler(SigString) 
Else
    Signature = "" 
End If

回答by sancho.s ReinstateMonicaCellio

I am simply pasting below the code by the great Chip Pearson. It works a charm.
Here's his page on array functions.

我只是在伟大的 Chip Pearson 的代码下面粘贴。它很有魅力。
这是他关于数组函数的页面。

I hope this helps.

我希望这有帮助。

Public Function IsArrayEmpty(Arr As Variant) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' IsArrayEmpty
' This function tests whether the array is empty (unallocated). Returns TRUE or FALSE.
'
' The VBA IsArray function indicates whether a variable is an array, but it does not
' distinguish between allocated and unallocated arrays. It will return TRUE for both
' allocated and unallocated arrays. This function tests whether the array has actually
' been allocated.
'
' This function is really the reverse of IsArrayAllocated.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

    Dim LB As Long
    Dim UB As Long

    err.Clear
    On Error Resume Next
    If IsArray(Arr) = False Then
        ' we weren't passed an array, return True
        IsArrayEmpty = True
    End If

    ' Attempt to get the UBound of the array. If the array is
    ' unallocated, an error will occur.
    UB = UBound(Arr, 1)
    If (err.Number <> 0) Then
        IsArrayEmpty = True
    Else
        ''''''''''''''''''''''''''''''''''''''''''
        ' On rare occasion, under circumstances I
        ' cannot reliably replicate, Err.Number
        ' will be 0 for an unallocated, empty array.
        ' On these occasions, LBound is 0 and
        ' UBound is -1.
        ' To accommodate the weird behavior, test to
        ' see if LB > UB. If so, the array is not
        ' allocated.
        ''''''''''''''''''''''''''''''''''''''''''
        err.Clear
        LB = LBound(Arr)
        If LB > UB Then
            IsArrayEmpty = True
        Else
            IsArrayEmpty = False
        End If
    End If

End Function

回答by Mike Bethany

Auth was closest but his answer throws a type mismatch error.

Auth 最接近，但他的回答引发了类型不匹配错误。

As for the other answers you should avoid using an error to test for a condition, if you can, because at the very least it complicates debugging (what if something else is causing that error).

至于其他答案，如果可以的话，您应该避免使用错误来测试条件，因为至少它会使调试复杂化（如果其他原因导致该错误怎么办）。

Here's a simple, complete solution:

这是一个简单而完整的解决方案：

option explicit
Function foo() As Variant

    Dim bar() As String

    If (Not Not bar) Then
        ReDim Preserve bar(0 To UBound(bar) + 1)
    Else
        ReDim Preserve bar(0 To 0)
    End If

    bar(UBound(bar)) = "it works!"

    foo = bar

End Function

回答by Robert S.

Simplified check for Empty Array:

空数组的简化检查：

Dim exampleArray() As Variant 'Any Type

If ((Not Not exampleArray) = 0) Then
      'Array is Empty
Else
      'Array is Not Empty
End If

回答by user425678

Based on ahuth's answer;

基于 ahuth 的回答；

Function AryLen(ary() As Variant, Optional idx_dim As Long = 1) As Long
    If (Not ary) = -1 Then
        AryLen = 0
    Else
        AryLen = UBound(ary, idx_dim) - LBound(ary, idx_dim) + 1
    End If
End Function

Check for an empty array; is_empty = AryLen(some_array)=0

检查空数组； is_empty = AryLen(some_array)=0