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
– Ashok Raj, Commented Mar 26, 2012 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
– markblandford, Commented Mar 26, 2012 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
– user1283776, Commented Mar 26, 2012 at 14:41
possible duplicate of How do I determine if an array is initialized in VB6? — GSerg
– GSerg, Commented Mar 26, 2012 at 15:54

Dan · Accepted Answer · 2012-03-26 14:48:57Z

11

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

Should work

answered Mar 26, 2012 at 14:48

Dan

45.8k20 gold badges98 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

assylias Over a year ago

This is not advisable for large arrays.

Dan Over a year ago

Yeah the "If IsArray() Then" suggestions make more sense.

assylias · Accepted Answer · 2012-03-26 16:28:30Z

7

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

answered Mar 26, 2012 at 16:28

assylias

330k84 gold badges680 silver badges806 bronze badges

Comments

markblandford · Accepted Answer · 2012-03-26 15:47:20Z

2

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

answered Mar 26, 2012 at 15:47

markblandford

3,2133 gold badges22 silver badges28 bronze badges

Comments

DavitosanX · Accepted Answer · 2014-07-01 21:05:53Z

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 1, 2014 at 21:05

DavitosanX

514 bronze badges

2 Comments

Anthony Griggs Over a year ago

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!

Andreas Covidiot Over a year ago

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): stackoverflow.com/a/9875956/1915920

TorontoJim · Accepted Answer · 2017-09-11 19:24:45Z

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

Collectives™ on Stack Overflow

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

5 Answers 5

2 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related