Check if array has contents

[Glory]

I made a post about this a little while back. The responses there aren’t helping me now.

I’m using an array in my code. The array may have contents, or it may not. I need to know how to check to see if the array is initialized, and lacks contents.

All my attempts result in error 9, “Subscript out of range”. Even IsError(myarray(0)) and If IsEmpty(MyArray(0)) result in this same break.

I’m currently checking whether the source of the array is full of contents, but I want to know once and for all how to check if an array has contents.

Please help me figure this out.

 

[bertie]

After your code populates the array insert a line containing a STOP command.

If it is not visible open the LOCALS WINDOW
View => Locals Wondow
And expand the array variable.

This lets you see how the array stores the values. You can determine from here how to impliment code to reference the array elements.

Hope this helps,
Bertie


Edit:
A simple check to see if an array has contents would be to check the upper bound of the array
'populate array
'if UBound(MyArray)>0 then
' array has contents

 

[bertie]

I had another wee think about your problem and here is some code I used to check if each item in an array is empty.

Test this by stepping through the code (F8) with the Locals Window opened.
Test with range "A2:A4" containing values and with no values.

[COLOR=darkblue]Sub[/COLOR] TestArray()
   [COLOR=darkblue]Dim[/COLOR] myArray [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] item [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
 
   myArray = Sheets("Sheet1").Range("[COLOR=red]a2:a4[/COLOR]").Value
 
 
   [COLOR=green]'test each item[/COLOR]
   [COLOR=darkblue]For[/COLOR] item = [COLOR=darkblue]LBound[/COLOR](myArray) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](myArray)
      [COLOR=darkblue]If[/COLOR] IsEmpty(myArray(item, 1)) [COLOR=darkblue]Then[/COLOR]
         MsgBox "Array item is empty"
      [COLOR=darkblue]Else[/COLOR]
         MsgBox "Array item is not empty"
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
   [COLOR=darkblue]Next[/COLOR] item
 
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[jasonb75]

When you debug is the error showing on the Array or a subsequent line dependant on a result returned from the array?

 

[jasonb75]

When you debug is the error showing on the Array or a subsequent line dependant on a result returned from the array?

To give an example of what I was thinking,

Sub test()
    Dim MyArray
        MyArray = Array("Sheet10", "Sheet2")
    With Sheets(MyArray(0))
        ' do something with sheet
    End With
End Sub

If you don't have a sheet named "Sheet10" in your workbook then MyArray(0) is not empty, nor is it an error, but because the sheet doesn't exist, it will create an error 9 when you try to reference it later.

To handle this you would need to trap the error by checking if the sheet exists with something like

Sub test()
    Dim MyArray
        MyArray = Array("Sheet10", "Sheet2")
    If Not (Sheets(MyArray(0))) Is Nothing Then
        With Sheets(MyArray(0))
            ' do something with sheet
        End With
    End If
End Sub

Please note that the code above is only for example and syntax may not be perfect.

 

[Glory]

I appreciate the responses.

Bertie, the error occurs as soon as VBA tries to work with the array variable in any native function. Like, I can't even get VBA to recognize that the lower bound is zero when there is no lower bound at all; all I get is error nine, subscript out of range.

Same deal as above, Jason. It looks like this:

If IsEmpty(MyArray) Then Exit Sub

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o>If MyArray(0) = "" Then Exit Sub

If MyArray(0) = 0 Then Exit Sub

If IsEmpty(MyArray(0)) Then Exit Sub

Edit: I had all those highlighted yellow, but the forum didn't preserve the formatting. Anyway, you get the idea. The failure occurs on that very same line, whatever I try.</o>

 

[mikerickson]

How are you declaring the array? Is it an array of variants or a variant array?

If you are getting a subscript error perhaps

On Error Resume Next

test = IsEmpty(myARray(0))

If Err = 9 Then
    MsgBox "Array is empty"
End If

On Error Goto 0

 

[bertie]

Please post your code.

It would help if we could see how you are populating the array and how you reference the array it in your code.

Remember to use code tags (# in the toolbar).

 

[Jaafar Tribak]

Take a look at the clever solution by James B (Post #15 ) here

 

[Glory]

Static Sub test()

Dim myarray() As Variant
Dim Toggle As Boolean

If Not Toggle Then ReDim Preserve myarray(1 To 1): myarray(1) = "asdf"
If Toggle Then Erase myarray

Toggle = True
MsgBox UBound(myarray)

End Sub


Jafaar, that's awesome, it's exactly the sort of thing I was looking for. Thanks.