How to check for empty array in vba macro [duplicate]

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 an error at line

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

since sFile is empty. Any way to avoid that?

Answer 1

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

If Len(Join(FileNamesList)) > 0 Then 

Answer 2

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 myArray returns the SafeArray pointer. For uninitialized arrays, this returns -1. You can Not this to XOR it with -1, thus returning zero, if you prefer.

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

Source