CODEWITHSUNDEEP
excelvba
Cowthulhu
Cowthulhu

Reputation: 528

Iterate over cells contained in range contained in array

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

Upvotes: 0

Views: 55

Answers (1)

Tim Williams
Tim Williams

Reputation: 166381

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

Upvotes: 2

Related Questions