Iterate over cells contained in range contained in array

Question

I'm trying to iterate over every cell in a range. In the past, I have been able to do this by using the following:

Function iterateOverEverything(someRange) As Integer
    Dim cell As range
    For Each cell In someRange
        doSomething(cell)
    Next
    iterateOverEverything = 3
End Function

The above code works.

The issue is that I am trying to create a function that is given multiple ranges, and iterates over them separately - I do not want to union the ranges, and it is imperative that I be able to compare multiple ranges. My current strategy is to use an array of ranges, then for each every cell in the range. Consider the following code:

Function iterateOverMultipleRanges(rangeZero, rangeOne) As Integer
    Dim ranges(1) As range
    ranges(0) = rangeZero
    ranges(1) = rangeOne
    Dim cell As range

    For i = 0 To UBound(ranges) - 1
        For Each cell In ranges(i)
            doSomething(cell)
        Next
    Next
    iterateOverMultipleRanges = 3
End Function

The above code does not work.

I believe I've determined the issue: the for each is unable to iterate over the contents of the array, for some reason. To run into this issue, attempt to run the following code:

Function iterateOverMultipleRanges(rangeZero, rangeOne) As Integer
    Dim ranges(1) As range
    ranges(0) = rangeZero
    ranges(1) = rangeOne
    Dim cell As range
    For Each cell In ranges(1)
            doSomething(cell)
    Next
    iterateOverMultipleRanges = 3
End Function

The above will throw an error.

How am I supposed to iterate over multiple ranges? What am I missing?

Note that I was unable to find any identical questions, but due to my lack of familiarity with the issue it is completely possible this is closely related to a different question.

Thanks!

EDIT: For additional information regarding answer, see mwolfe02's answer: https://stackoverflow.com/a/5042718/8133062

Property Set is for objects (e.g., class instances),

Property Let is for "normal" datatypes (e.g., string, boolean, long, etc.)

@TimWilliams That looks like it solved my issue. I'm going to double check everything is working - would you mind submitting that as an answer so I can accept it in the meantime? — Cowthulhu, Jan 04 '18 at 18:16
@TimWilliams Yeah, I wasn't assigning it as a reference - you got it! Thank you! — Cowthulhu, Jan 04 '18 at 18:25
Protip: when you post on Stack Overflow about something that "throws an error", include the error message and as much information as possible too, like the specific statement that the VBE is highlighting as it's saying "Object or With block variable not set". Object/reference assignments in VBA always require the `Set` keyword, that specific error message will pop up every time you forget it. — Mathieu Guindon, Jan 04 '18 at 18:37
@Mat'sMug I'm using the excel editor, which only causes cells to pop up with `#VALUE` when an error occurs - no actual error message is displayed, so long as the code compiles (which this code did). I'll look into better error handling, thanks! — Cowthulhu, Jan 04 '18 at 19:23
If you're testing a function which you intend to use as a UDF, then do your initial testing by calling that function from a Sub: that way you'll get full error messages and the offending line(s) instead of just #VALUE — Tim Williams, Jan 04 '18 at 20:05

Tim Williams · Accepted Answer · 2018-01-05T21:54:46.743

You need to use Set when assigning objects:

Function iterateOverMultipleRanges(rangeZero As Range, _
                                   rangeOne As Range) As Integer
    Dim ranges(1) As range
    Set ranges(0) = rangeZero
    Set ranges(1) = rangeOne
    Dim cell As range

    For i = 0 To UBound(ranges) - 1
        For Each cell In ranges(i)
            doSomething(cell)
        Next
    Next
    iterateOverMultipleRanges = 3
End Function

Iterate over cells contained in range contained in array

1 Answers1