VBA Traverse through each non-empty element of 2-d array

Question

I would like to perform some action on each element of a 2D array where the second dimension of the array varies on each of the first dimensional elements. I would like the loop to skip empty elements.

A 1D array would make it run through the loop without wasting any time on empty elements of the array, but I would like to use a 2D array to make it easier to associate data into a certain group and make the code easier to modify later by somebody else if needed. The 1D array runs much faster than the 2D array.

Application.EnableCancelKey = xlDisabled

On Error Resume Next

For i = 0 To UBound(somearray,1)    
    For j = 0 To UBound(somearray,2)
        perform some action on somearray(i,j)    
    Next j
Next i

It should be noted that I do get a subscript 9 out of range error without the On Error Resume Next. I assume this is because it is attempting to perform an action on something that doesn't exist. It works as intended but is just slower.

I apologize if the question is trivial. I wasn't able to find an answer to this and I know nothing about VBA.

How do you define `somearray`? Think we need a bit more code. Nothing obviously wrong with the code you've posted. — SJR, Jul 15 '19 at 16:42
What are the lower bounds of the array? Also, `On Error Resume Next` when used like that is just awful. Errors should be fixed or at least handled, not suppressed. — John Coleman, Jul 15 '19 at 16:44
Can you elaborate on both part of *where the second dimension of the array varies on each of the first dimensional elements. I would like the loop to skip empty elements.*? I don't want to be wrong in assuming where the empty elements are (,1 or ,2) — Cyril, Jul 15 '19 at 16:46
If your array indeed has [different number of elements in the second dimension](https://stackoverflow.com/q/9435608/11683), you cannot access it as `somearray(i,j)`, but rather as `somearray(i)(j)`. If your array is a normal array where some of the last elements in each row are `Empty`, then use `IsEmpty()` to not "loop over them". — GSerg, Jul 15 '19 at 16:48
I managed to figure it out and the comments were helpful. For some reason IsEmpty() didn't work for me but = "" did. — Matthew Liu, Jul 15 '19 at 20:42

score 2 · Answer 1 · answered Jul 15 '19 at 17:16

Remove On Error Resume Next, and don't hard-code the lower bounds:

Dim currentRow As Long
For currentRow = LBound(somearray, 1) To UBound(somearray, 1)
    Dim currentColumn As Long
    For currentColumn = LBound(somearray, 2) To UBound(somearray, 2)
        DoSomething somearray(currentRow, currentColumn)
    Next
Next

If you got your 2D array from a Range, it's a 1-based Variant array, so subscript 0 is out of range.

VBA Traverse through each non-empty element of 2-d array

1 Answers1