如何在vba宏中检查空数组

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

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

这里如果FileNamesList数组为空,则GetBoiler(SigString)根本不应该被调用.当FileNamesList数组为空时,SigString也是空的,这会调用GetBoiler()带有空字符串的函数.我在线上得到了一个错误

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

既然sFile是空的.有什么办法可以避免吗？

1> Fionnuala..：

---

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

If Len(Join(FileNamesList)) > 0 Then

---

是的它很短.但它可能会做很多不必要的工作.

---

@VBOG不,因为JOIN刚从数组中创建了一个字符串.

---

值得注意的是，如果我有一个空字符串数组，这将不起作用...这并不是真的告诉我该数组是否已初始化。

2> ahuth..：

---

选择三重否定:

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

要不就:

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

在VB中,无论出于何种原因,Not myArray返回SafeArray指针.对于未初始化的数组,返回-1.你可以Not用-1对它进行异或,如果你愿意,可以返回零.

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

资源

---

惊人的方案.从来不知道这个黑客.干杯!

3> Lance Robert..：

---

如果你测试一个数组函数它将适用于所有边界:

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

---

还不错,但试试``arr`就像这样定义`Dim arr()As String:arr = Split(""):Debug.Print IsVarArrayEmpty(arr)`...这会返回`False`,但所有帐户,`arr`*是*一个空数组(或者还有什么叫它？),奇怪的是,`UBound(arr)= - 1`和`LBound(arr)= 0`.这个问题在Chip Pearson的[IsArrayEmpty](http://www.cpearson.com/excel/vbaarrays.htm)中得到了解决.这就是我在对这个问题的评论中与之相关的原因.

4> Perposterer..：

---

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

这就是我不幸要处理它的方法......我喜欢len(join(arr))> 0方法,但是如果数组是一个emptystrings数组就行不通......

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

5> BBQ Chef..：

---

在写VBA的时候,我脑子里有这句话:"可能这么容易,但......"

以下是我采用它:

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

---

关于检查数组在装配中是否空虚的讨论较短.这太荒谬了.