How to check for empty array in vba macro

Question

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 FileNamesList array is empty, GetBoiler(SigString) should not get called at all. When FileNamesList array is empty, SigString is also empty and this calls GetBoiler() function with empty string. I get error at line

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

since sFile is empty. Any way to avoid that.

Answer 1

Well to some extent it will depend on how the array is created, but a combination of IsArray() and UBound should work. So in PseudoCode:

If IsArray(FileNamesList) AND UBound(FileNamesList) >= 3 Then
    'It exists and is populated
End If

Answer 2

That code gives me a Type mismatch error.

Answer 3

Then you have to nest the If thens to make sure it is an array before you check the UBound.

If IsArray(FileNamesList) Then
    If UBound(FileNamesList) >= 3 Then
        'It exists and is populated
    End If
End If

Answer 4

After nesting IF statements, it still somehow calls GetBoiler() function with empty string and breaks at line - Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)

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

The above IF statement does not work and getBoiler function gets called even if SigString is empty.

Answer 5

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 vbNullString to Dir, it will return the name of the first file in the current directory path (the path returned by CurDir$). So, if SigString is empty, your If condition will evaluate to True because Dir will return a non-empty string (the name of the first file in the current directory), and GetBoiler will be called. And if SigString is empty, the call to fso.GetFile will fail.

You should either change your condition to check that SigString isn't empty, or use the FileSystemObject.FileExists method instead of Dir for checking if the file exists. Dir is tricky to use precisely because it does things you might not expect it to do. Personally, I would use Scripting.FileSystemObject over Dir because there's no funny business (FileExists returns True if the file exists, and, well, False if it doesn't). What's more, FileExists expresses the intent of your code much clearly than Dir.

Method 1: Check that SigString is non-empty first

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

Method 2: Use the FileSystemObject.FileExists method

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

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

Answer 6

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

If Len(Join(FileNamesList)) > 0 Then

Answer 7

You need to do some debugging. Put a break in and use the watch window to be sure that all the variables are what you think they are and they hold the value that you think they hold.

Answer 8

Thanks a lot... your answer worked .....

Answer 9

I'll generalize the problem and the Question should intended. Test assigment on the array, and catch the eventual error

Function IsVarArrayEmpty(anArray as Variant)
Dim aVar as Variant

IsVarArrayEmpty=False
On error resume next
aVar=anArray(1)
If Err.number then '...still, it might not start at this index
    aVar=anArray(0)
    If Err.number then IsVarArrayEmpty=True ' neither 0 or 1 yields good assignment
EndIF
End Function

Sure it misses arrays with all negative indexes or all > 1... is that likely? on weirdland, yes.

Answer 10

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