How to check empty array in vba macro

Question

I want to check for empty arrays. Google gave me a variety of solutions, but nothing worked. Perhaps 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 the FileNamesList array is empty, GetBoiler(SigString) should not be called at all. When the FileNamesList array is empty, SigString also empty and calls the GetBoiler() function with an empty string. I get an error in the line

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

since sFile empty. Any way to avoid this?

Answer 1

How do you deal with a string array, do you consider Join?

 If Len(Join(FileNamesList)) > 0 Then 

Answer 2

Go with 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 simply:

 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 some reason Not myArray returns a SafeArray pointer. For uninitialized arrays, -1 is returned. You can use Not for XOR with -1, returning zero if you want.

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

Source

Answer 3

If you check the array function, it will work for all borders:

 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 

Answer 4

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

This is how I, unfortunately, am going to deal with this ... I like the approach len (join (arr))> 0, but it will not work if the array was an array of 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 

Answer 5

When writing VBA, this sentence in my head: "It may be so simple, but ..."

Here is what I accepted this:

 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 

Answer 6

I just paste under the code of the great Pearson Chip. It works. Here is his page about array functions .

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 

Answer 7

This code does not fulfill 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 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 empty, the fso.GetFile call will fail.

You must either change your condition to verify that SigString not empty, or use FileSystemObject.FileExists instead of Dir to check for the presence of a file. Dir difficult to use precisely because it does what you do not expect from it. Personally, I would use Scripting.FileSystemObject over Dir because there is no funny business ( FileExists returns True if the file exists, and, well, False if it does not). What's more, FileExists expresses the intent of your code much more clearly than Dir .

Method 1: Make SigString Not SigString 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 8

Auth was closest, but its answer causes a type mismatch error.

As with the other answers, you should avoid using an error to check the condition if you can, because at least that complicates debugging (which if something else causes this 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 

Answer 9

A simplified check for an empty array:

 Dim exampleArray() As Variant 'Any Type If ((Not Not exampleArray) = 0) Then 'Array is Empty Else 'Array is Not Empty End If 

Answer 10

 Public Function IsEmptyArray(InputArray As Variant) As Boolean On Error GoTo ErrHandler: IsEmptyArray = Not (UBound(InputArray) >= 0) Exit Function ErrHandler: IsEmptyArray = True End Function 

Answer 11

Another method would be to do this sooner. You can create a boolean variable and set it to true after loading the data into an array. so all you really need is a simple if statement when you load data into an array.

Answer 12

Here is another way to do this. I used it in some cases and it works.

 Function IsArrayEmpty(arr As Variant) As Boolean Dim index As Integer index = -1 On Error Resume Next index = UBound(arr) On Error GoTo 0 If (index = -1) Then IsArrayEmpty = True Else IsArrayEmpty = False End Function 

Answer 13

To check if the byte array is empty, the easiest way is to use the VBA function StrPtr() .

If the byte array is empty, StrPtr() returns 0 ; otherwise, it returns a nonzero value (however, it is not an address for the first element).

 Dim ar() As Byte Debug.Assert StrPtr(ar) = 0 ReDim ar(0 to 3) As Byte Debug.Assert StrPtr(ar) <> 0 

However, it only works with the Byte array.

Answer 14

Based on ahuth answer ;

 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

Answer 15

I will summarize the problem and the question as intended. Testing in an array and catching a possible 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 

I am sure that it skips arrays with all negative indices or all> 1 ... is this likely? in weirdland, yes.

Answer 16

Personally, I believe that one of the answers above can be modified to check if the array has content:

 if UBound(ar) > LBound(ar) Then 

This applies to negative link numbers and takes less time than some other parameters.

Answer 17

You can use the following function to check if there is no empty or string array in vba

 Function IsArrayAllocated(Arr As Variant) As Boolean On Error Resume Next IsArrayAllocated = IsArray(Arr) And _ Not IsError(LBound(Arr, 1)) And _ LBound(Arr, 1) <= UBound(Arr, 1) End Function 

Using example

 Public Function test() Dim Arr(1) As String Arr(0) = "d" Dim x As Boolean x = IsArrayAllocated(Arr) End Function 

Answer 18

You can check if the array is empty by counting the total number of elements using the JScript VBArray() object (works with variant type arrays, single or multidimensional):

 Sub Test() Dim a() As Variant Dim b As Variant Dim c As Long ' Uninitialized array of variant ' MsgBox UBound(a) ' gives 'Subscript out of range' error MsgBox GetElementsCount(a) ' 0 ' Variant containing an empty array b = Array() MsgBox GetElementsCount(b) ' 0 ' Any other types, eg Long or not Variant type arrays MsgBox GetElementsCount(c) ' -1 End Sub Function GetElementsCount(aSample) As Long Static oHtmlfile As Object ' instantiate once If oHtmlfile Is Nothing Then Set oHtmlfile = CreateObject("htmlfile") oHtmlfile.parentWindow.execScript ("function arrlength(arr) {try {return (new VBArray(arr)).toArray().length} catch(e) {return -1}}"), "jscript" End If GetElementsCount = oHtmlfile.parentWindow.arrlength(aSample) End Function 

It takes me about 0.3 microseconds for each element + initialization of 15 ms, so an array of 10M elements takes about 3 seconds. The same functionality can be implemented through ScriptControl ActiveX (it is not available in 64-bit versions of MS Office, so you can use a workaround, for example this ).

Answer 19

 Function IsVarArrayEmpty(anArray As Variant) as boolean On Error Resume Next IsVarArrayEmpty = true IsVarArrayEmpty = UBound(anArray) < LBound(anArray) End Function 

==> perhaps ubound crashes and it remains true, and if ubound

Answer 20

 if Ubound(yourArray)>-1 then debug.print "The array is not empty" else debug.print "EMPTY" end if 

Answer 21

Another solution for checking an empty array

 if UBound(ar) < LBound(ar) then msgbox "Your array is empty!" 

Or, if you already know that LBound is 0

 if -1 = UBound(ar) then msgbox "Your array is empty!" 

This may be faster than join (). (And I did not check with negative indices)

Here is my example for filtering 2 string arrays, so they don't use the same strings.

 ' Filtering ar2 out of strings that exists in ar1 For i = 0 To UBound(ar1) ' filter out any ar2.string that exists in ar1 ar2 = Filter(ar2 , ar1(i), False) If UBound(ar2) < LBound(ar2) Then MsgBox "All strings are the same.", vbExclamation, "Operation ignored": Exit Sub End If Next ' At this point, we know that ar2 is not empty and it is filtered ' 

Answer 22

 Public Function arrayIsEmpty(arrayToCheck() As Variant) As Boolean On Error GoTo Err: Dim forCheck forCheck = arrayToCheck(0) arrayIsEmpty = False Exit Function Err: arrayIsEmpty = True End Function