CODEWITHSUNDEEP
excelvba
Fred B
Fred B

Reputation: 29

Variable loop scope

I have some vba code I want to loop over different scopes depending on the value of a boolean. I want it to look something like this:

Sub Loop()

ActiveSheet.Select

Dim LoopScope as Collection

If Boolean = True then
     LoopScope = ActiveSheet.ChartObjects
Else
     LoopScope = Selection
End if

For Each ChartObject In LoopScope
     *Some code*
Next ChartObject

End Sub

The error message tells me I can only iterate over collection objects, which makes sense, however I can't figure out how to dim LoopScope as a collection object (?). The loop works both when typing:

For Each ChartObject in Selection

and:

For Each ChartObject in ActiveSheet.ChartObjects

I only can't figure out how to make the scope dependent on my boolean. Thank you in advance for your time.

Upvotes: 0

Views: 327

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

Use late binding, and error handling.

The main thing you're missing is the Set keyword; without it you are let-coercing the objects, and [if you're lucky] end up with an intrinsic value type (e.g. String, Long, etc.)... or a run-time error.

Dim loopScope As Object

If SomeBoolean Then
    Set loopScope = Selection
Else
    Set loopScope = ActiveSheet.ChartObjects
End If

Dim e As Object, c As ChartObject
On Error GoTo ErrHandler
If TypeOf loopScope Is ChartObject Then '<~ selection was just one chartobject
    Set c = loopScope
    '...<~ work with the selected chart here
Else
    ' let's try iterating the loopscope
    For Each e In loopScope '<~ will blow up if loopScope isn't iteratable
        '... <~ member calls against e are late-bound (resolved at run-time)
        If TypeOf e Is ChartObject Then
            Set c = e '<~ cast e to a known interface
            '... <~ member calls against c are early-bound now
        End If
    Next
End If
Exit Sub '<~ "happy path" ends here

ErrHandler: '<~ "error path" starts here
'... <~ couldn't iterate loopScope.. now what?

Upvotes: 2

Related Questions