check if array is empty vba excel

Question

These if     then statements are getting the wrong results in my opinion  The first is returning the value  false  when it should be  true   The fourth returns the right value  The second and third return an error        Sub empty array     Dim arr1   As Variant    If IsEmpty arr1  Then     MsgBox  hey    End If    If IsError UBound arr1   Then     MsgBox  hey    End If    If IsError Application match       arr1   0   Then     MsgBox  hey    End If    ReDim arr1 1    arr1 1     hey     If IsEmpty arr1  Then     MsgBox  hey    End If End Sub

User · Answer

Above methods didn  t work for me  This did     Dim arrayIsNothing As Boolean      On Error Resume Next     arrayIsNothing   IsNumeric UBound YOUR ARRAY   And False     If Err Number  lt  gt  0 Then arrayIsNothing   True     Err Clear     On Error GoTo 0       Now you can test      if arrayIsNothing then

User · Answer

Adding into this  it depends on what your array is defined as  Consider   dim a   as integer dim b   as string dim c   as variant   these doesn t work if isempty a  then msgbox  integer arrays can be empty  if isempty b  then msgbox  string arrays can be empty    this is because isempty can only be tested on classes which have an  empty property   this do work if isempty c  then msgbox  variants can be empty    So  what can we do  In VBA  we can see if we can trigger an error and somehow handle it  for example  dim a   as integer dim bEmpty as boolean  bempty false  on error resume next bempty not isnumeric ubound a   on error goto 0   But this is really clumsy    A nicer solution is to declare a boolean variable  a public or module level is best   When the array is first initialised  then set this variable  Because it s a variable declared at the same time  if it loses it s value  then you know that you need to reinitialise your array  However  if it is initialised  then all you re doing is checking the value of a boolean  which is low cost  It depends on whether being low cost matters  and if you re going to be needing to check it often   option explicit   declared at module level dim a   as integer dim aInitialised as boolean  sub DoSomethingWithA   if not aInitialised then InitialiseA  you can now proceed confident that a   is intialised end sub  sub InitialiseA    insert code to do whatever is required to initialise A  e g   redim a 10  a 1  123      aInitialised true end sub   The last thing you can do is create a function  which in this case will need to be dependent on the clumsy on error method   function isInitialised byref a   as variant  as boolean isInitialised false on error resume next isinitialised isnumeric ubound a   end function

User · Answer

I would do this as  if isnumeric ubound a     False then msgbox  a is empty

User · Answer

The problem with VBA is that there are both dynamic and static arrays    Dynamic Array Example Dim myDynamicArray   as Variant  Static Array Example Dim myStaticArray 10  as Variant Dim myOtherStaticArray 0 To 10  as Variant  Using error handling to check if the array is empty works for a Dynamic Array  but a static array is by definition not empty  there are entries in the array  even if all those entries are empty  So for clarity s sake  I named my function  quot IsZeroLengthArray quot       Public Function IsZeroLengthArray ByRef subject   As Variant  As Boolean          Tell VBA to proceed if there is an error to the next line          On Error Resume Next              Dim UpperBound As Integer         Dim ErrorNumber As Long         Dim ErrorDescription As String         Dim ErrorSource As String               If the array is empty this will throw an error because a zero-length             array has no UpperBound  or LowerBound            This only works for dynamic arrays   If this was a static array there             would be both an upper and lower bound          UpperBound   UBound subject             Store the Error Number and then clear the Error object            because we want VBA to treat unintended errors normally         ErrorNumber   Err Number         ErrorDescription   Err Description         ErrorSource   Err Source         Err Clear         On Error GoTo 0               Check the Error Object to see if we have a  quot subscript out of range quot  error               If we do  the number is 9  then we can assume that the array is zero-length          If ErrorNumber   9 Then             IsZeroLengthArray   True           If the Error number is something else then 9 we want to raise           that error again            ElseIf ErrorNumber  lt  gt  0 Then             Err Raise ErrorNumber  ErrorSource  ErrorDescription           If the Error number is 0 then we have no error and can assume that the           array is not of zero-length         ElseIf ErrorNumber   0 Then             IsZeroLengthArray   False         End If     End Function  I hope that this helps others as it helped me

User · Answer

jeminar has the best solution above   I cleaned it up a bit though    I recommend adding this to a FunctionsArray module   isInitialised false is not needed because Booleans are false when created On Error GoTo 0 wrap and indent code inside error blocks similar to with blocks for visibility  these methods should be avoided as much as possible but     VBA       Function isInitialised ByRef a   As Variant  As Boolean     On Error Resume Next     isInitialised   IsNumeric UBound a       On Error GoTo 0 End Function

User · Answer

this worked for me  Private Function arrIsEmpty arr as variant      On Error Resume Next     arrIsEmpty   False     arrIsEmpty   IsNumeric UBound arr   End Function

User · Answer

Arr1 becomes an array of  Variant  by the first statement of your code   Dim arr1   As Variant   Array of size zero is not empty  as like an empty box exists in real world   If you define a variable of  Variant   that will be empty when it is created   Following code will display  Empty    Dim a as Variant  If IsEmpty a  then   MsgBox  Empty   Else   MsgBox  Not Empty   End If

[excel] check if array is empty (vba excel)

Examples related to excel

Examples related to vba