VBA: Don't go into loop when array is empty

Question

I have a loop that can look like this:

For Each article In artAll
Next

or like this:

For i = 0 To Ubound(artAll)
Next

When the array length is 0, I get an error message. What is a good way to skip the loop when the array is empty? I suspect that I should use

On Error Goto

but I need help finalizing a solution.

did u check ubound(artAll) = 0 condition inside the for loop? — Ashok Raj, Mar 26 '12 at 14:34
Do you mean the array hasn't been dimensionalized? If that is the case, then the only way to handle a `Ubound()` (or `LBound()`) error is with an error handler. — markblandford, Mar 26 '12 at 14:38
What is a good way to handle this using the error handler? Should I catch a specific exception and use if err.number after the loop? Or is it better to goto a label? — user1283776, Mar 26 '12 at 14:41
possible duplicate of [How do I determine if an array is initialized in VB6?](http://stackoverflow.com/questions/183353/how-do-i-determine-if-an-array-is-initialized-in-vb6) — GSerg, Mar 26 '12 at 15:54

score 11 · Accepted Answer · answered Mar 26 '12 at 14:48

11

If Len(Join(artAll, "")) = 0 Then
     'your for loops here

Should work

answered Mar 26 '12 at 14:48

Dan

45,079
17
88
157

9

This is not advisable for large arrays. – assylias Mar 26 '12 at 16:29
Yeah the "If IsArray() Then" suggestions make more sense. – Dan Mar 27 '12 at 10:03

score 7 · Answer 2 · answered Mar 26 '12 at 16:28

I use this function to test for empty arrays:

Public Function isArrayEmpty(parArray As Variant) As Boolean
'Returns false if not an array or dynamic array that has not been initialised (ReDim) or has been erased (Erase)

    If IsArray(parArray) = False Then isArrayEmpty = True
    On Error Resume Next
    If UBound(parArray) < LBound(parArray) Then isArrayEmpty = True: Exit Function Else: isArrayEmpty = False

End Function

Then in your main code:

If isArrayEmpty(yourArray) Then
   'do something - typically:
   MsgBox "Empty Array"
   Exit Function
End If

For i = LBound(yourArray,1) To UBound(yourArray,1)
   'do something
Next i

score 2 · Answer 3 · answered Jul 01 '14 at 21:05

2

This is an old question, but I found this solution to the problem, and it could be helpful to others:

If (Not myArray) = True Then

    'Undimensionalized array. Respond as needed.

Else

    'Array isn't empty, you can run your loop.

End If

It helped my out in a recent project, and found it to be very handy.

answered Jul 01 '14 at 21:05

DavitosanX

51
4

1

Very nice! Interestingly If (myArray) Then doesn't work? Oh well... The IsEmpty() didn't work in my circumstance as I was working with an array of User Defined Types... but this solution did the trick! – Anthony Griggs Oct 11 '17 at 01:50
it looks good at first, but it did not work for me, because I may have run into the problems solved by @assylias function `isArrayEmpty(parArray as Variant)`: https://stackoverflow.com/a/9875956/1915920 – Andreas Covidiot Dec 21 '18 at 15:47

score 2 · Answer 4 · answered Mar 26 '12 at 15:47

I like the solution given by @Dan but thought I would throw out there how I would normally handle an undimensionalized array:

Dim lngUboundTest As Long

lngUboundTest = -1
On Error Resume Next
lngUboundTest = UBound(artAll)
On Error GoTo 0

If lngUboundTest >= 0 Then
    'Your loop...

score 0 · Answer 5 · answered Sep 11 '17 at 19:24

I found this thread looking for a solution to a problem where looping through a multidimensional array would fail if a dimensioned element was empty. I created the array by looping through a source that could have up to 6 datasets. Then after processing I would repeat this 19 more times.

Dim varDeskData As Variant
Dim varDesk As Variant
ReDim varDesk(1 To 6)

For y = 1 To 6
    ReDim varDeskData(1 To 4)
    varDeskData(1) = "nifty integer from source(y)"
    varDeskData(2) = "nifty string from source(y)"
    varDeskData(3) = "another nifty string from source(y)"
    varDeskData(4) = "another nifty string from source(y)"
    varDesk(y) = varDeskData
Next y

When I ran the following, I would get the first three processed but then it would fail on the fourth, because I had only loaded three into the parent array:

For y = 1 To 6
    If varDesk(y)(1) > 0 Then

        ... do nifty stuff ...

    End If
End If

Using the IsEmpty procedure on the top level elements of the parent array fixed this:

For y = 1 To 6
    If IsEmpty(varDesk(y)) = False Then
        If varDesk(y)(1) > 0 Then

        ... do nifty stuff ...

        End If
    End If
End If

VBA: Don't go into loop when array is empty

5 Answers5

Linked