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   lt  gt     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

User · Answer

This code doesn t do what you expect   If Dir SigString   lt  gt     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  lt  gt     And Dir SigString   lt  gt     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

User · Answer

Another method would be to do it sooner   You can create a Boolean variable and set it to true once you load data to the array   so all you really need is a simple if statement of when you load data into the array

User · Answer

I am simply pasting below the code by the great Chip Pearson  It works a charm  Here s his page on array functions   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  lt  gt  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  gt  UB  If so  the array is not           allocated                                                             err Clear         LB   LBound Arr          If LB  gt  UB Then             IsArrayEmpty   True         Else             IsArrayEmpty   False         End If     End If  End Function

User · Answer

As you are dealing with a string array  have you considered Join   If Len Join FileNamesList    gt  0 Then

User · Answer

Based on ahuth s 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

User · Answer

You can check its count   Here cid is an array   if  jsonObject  result    cid   Count    0 them MsgBox  Empty Array    I hope this helps  Have a nice day

User · Answer

Go with a triple negative   If  Not Not FileNamesList   lt  gt  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

User · Answer

if Ubound yourArray  gt -1 then  debug print  The array is not empty  else  debug print  EMPTY  end if

User · Answer

You can use the below function to check if variant or string array is empty 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   lt   UBound Arr  1  End Function   Sample usage  Public Function test   Dim Arr 1  As String Arr 0     d  Dim x As Boolean x   IsArrayAllocated Arr  End Function

User · Answer

Based on ahuth s 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

User · Answer

You can use the below function to check if variant or string array is empty 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   lt   UBound Arr  1  End Function   Sample usage  Public Function test   Dim Arr 1  As String Arr 0     d  Dim x As Boolean x   IsArrayAllocated Arr  End Function

User · Answer

I ll generalize the problem and the Question as intended  Test assingment 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  in weirdland  yes

User · Answer

Here is another way to do it  I have used it in some cases and it s working   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

User · Answer

Another solution to test for empty array  if UBound ar   lt  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 didn t check with negative indexes   Here is my sample to filter 2 string arrays so they do not share 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   lt  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

User · Answer

I ll generalize the problem and the Question as intended  Test assingment 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  in weirdland  yes

User · Answer

Another solution to test for empty array  if UBound ar   lt  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 didn t check with negative indexes   Here is my sample to filter 2 string arrays so they do not share 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   lt  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

User · Answer

Personally  I think one of the answers above can be modified to check if the array has contents   if UBound ar   gt  LBound ar  Then   This handles negative number references and takes less time than some of the other options

User · Answer

As you are dealing with a string array  have you considered Join   If Len Join FileNamesList    gt  0 Then

User · Answer

Function IsVarArrayEmpty anArray As Variant  as boolean     On Error Resume Next     IsVarArrayEmpty   true     IsVarArrayEmpty   UBound anArray   lt  LBound anArray  End Function   Maybe ubound crashes and it remains at true  and if ubound  lt  lbound  it s empty

User · Answer

Go with a triple negative   If  Not Not FileNamesList   lt  gt  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

User · Answer

Another method would be to do it sooner   You can create a Boolean variable and set it to true once you load data to the array   so all you really need is a simple if statement of when you load data into the array

User · Answer

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

User · Answer

Public Function IsEmptyArray InputArray As Variant  As Boolean     On Error GoTo ErrHandler     IsEmptyArray   Not  UBound InputArray   gt   0     Exit Function     ErrHandler     IsEmptyArray   True  End Function

User · Answer

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

User · Answer

You can check if the array is empty by retrieving total elements count using JScript s VBArray   object  works with arrays of variant type  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   For me it takes about 0 3 mksec for each element   15 msec initialization  so the array of 10M elements takes about 3 sec  The same functionality could be implemented via ScriptControl ActiveX  it is not available in 64-bit MS Office versions  so you can use workaround like this

User · Answer

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

User · Answer

if Ubound yourArray  gt -1 then  debug print  The array is not empty  else  debug print  EMPTY  end if

User · Answer

To check whether a Byte array is empty  the simplest way is to use the VBA function StrPtr     If the Byte array is empty  StrPtr   returns 0  otherwise  it returns a non-zero value  however  it s not the address to the first element    Dim ar   As Byte Debug Assert StrPtr ar    0  ReDim ar 0 to 3  As Byte Debug Assert StrPtr ar   lt  gt  0   However  it only works with Byte array

User · Answer

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

User · Answer

As you are dealing with a string array  have you considered Join   If Len Join FileNamesList    gt  0 Then

User · Answer

This code doesn t do what you expect   If Dir SigString   lt  gt     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  lt  gt     And Dir SigString   lt  gt     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

User · Answer

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     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

User · Answer

You can check its count   Here cid is an array   if  jsonObject  result    cid   Count    0 them MsgBox  Empty Array    I hope this helps  Have a nice day

User · Answer

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

User · Answer

Auth was closest but his answer throws a type mismatch error   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

User · Answer

Auth was closest but his answer throws a type mismatch error   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

User · Answer

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     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

User · Answer

Here is another way to do it  I have used it in some cases and it s working   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

User · Answer

I ll generalize the problem and the Question as intended  Test assingment 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  in weirdland  yes

User · Answer

As you are dealing with a string array  have you considered Join   If Len Join FileNamesList    gt  0 Then

User · Answer

Public Function IsEmptyArray InputArray As Variant  As Boolean     On Error GoTo ErrHandler     IsEmptyArray   Not  UBound InputArray   gt   0     Exit Function     ErrHandler     IsEmptyArray   True  End Function

User · Answer

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

User · Answer

I ll generalize the problem and the Question as intended  Test assingment 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  in weirdland  yes

User · Answer

To check whether a Byte array is empty  the simplest way is to use the VBA function StrPtr     If the Byte array is empty  StrPtr   returns 0  otherwise  it returns a non-zero value  however  it s not the address to the first element    Dim ar   As Byte Debug Assert StrPtr ar    0  ReDim ar 0 to 3  As Byte Debug Assert StrPtr ar   lt  gt  0   However  it only works with Byte array

User · Answer

This code doesn t do what you expect   If Dir SigString   lt  gt     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  lt  gt     And Dir SigString   lt  gt     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

User · Answer

I am simply pasting below the code by the great Chip Pearson  It works a charm  Here s his page on array functions   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  lt  gt  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  gt  UB  If so  the array is not           allocated                                                             err Clear         LB   LBound Arr          If LB  gt  UB Then             IsArrayEmpty   True         Else             IsArrayEmpty   False         End If     End If  End Function

User · Answer

This code doesn t do what you expect   If Dir SigString   lt  gt     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  lt  gt     And Dir SigString   lt  gt     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

User · Answer

You can check if the array is empty by retrieving total elements count using JScript s VBArray   object  works with arrays of variant type  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   For me it takes about 0 3 mksec for each element   15 msec initialization  so the array of 10M elements takes about 3 sec  The same functionality could be implemented via ScriptControl ActiveX  it is not available in 64-bit MS Office versions  so you can use workaround like this

User · Answer

When writing VBA there is this sentence in my head   Could be so easy  but      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  gt  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    amp  IsArrayEmpty a    If IsArrayEmpty a    False Then     Debug Print       amp  Join a    End If End Sub

User · Answer

Function IsVarArrayEmpty anArray As Variant  as boolean     On Error Resume Next     IsVarArrayEmpty   true     IsVarArrayEmpty   UBound anArray   lt  LBound anArray  End Function   Maybe ubound crashes and it remains at true  and if ubound  lt  lbound  it s empty

User · Answer

When writing VBA there is this sentence in my head   Could be so easy  but      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  gt  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    amp  IsArrayEmpty a    If IsArrayEmpty a    False Then     Debug Print       amp  Join a    End If End Sub

User · Answer

Personally  I think one of the answers above can be modified to check if the array has contents   if UBound ar   gt  LBound ar  Then   This handles negative number references and takes less time than some of the other options

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

Examples related to vba

Examples related to excel