Variable loop scope

Question

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.

You could have have a For/Each in the If part and one in the Else part — Doug Glancy
– Doug Glancy, Commented May 23, 2020 at 20:33
What type of Selection are you anticipating here? Are you just missing Set on your LoopScope assignment? If that's just pseudocode can you post the actual code you tried? — Tim Williams
– Tim Williams, Commented May 23, 2020 at 20:40
@TimWilliams - The Selection is a selection of a couple of graphs. Unfortunately I cannot publish the actual code on here, I hope you understand. When I try to do Set LoopScope = ActiveSheet.ChartObjects I get a "Type mismatch" error message. The LoopScope is declared as a collection. Am I wrong in thinking the problem seems to be that my LoopScope variable is not interchangable with ActiveSheet.ChartObjects because it is of the wrong datatype? — Fred B
– Fred B, Commented May 23, 2020 at 21:22
@DougGlancy - Yes, if only the code I was trying to run was shorter that would be an excellent solution. However, there are a couple of hundred lines... haha. Your solution requires all the code to be duplicated into the if-part and the else-part, if you get what I mean? I'm simply trying to run the same hundred lines of code, iterating over one scope or the other depending if the boolean is true or false. — Fred B
– Fred B, Commented May 23, 2020 at 21:27
I thought about that, but thought that perhaps you could put most of the code that's called into a separate subroutine it's called from each branch of the If/Else. I called subroutine would have to have a parameter to distinguish which half it was called from, something like that. Hard to know for sure without seeing your code, but I would be surprised if it wasn't possible. — Doug Glancy
– Doug Glancy, Commented May 23, 2020 at 21:50

Mathieu Guindon · Accepted Answer · 2020-05-23 22:11:40Z

2

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?

answered May 23, 2020 at 22:11

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

1 Comment

Fred B Over a year ago

Thank you Mathieu. The object declaration and the Set keyword did the trick. Thank you so much for taking the time to write a thorough explanation, it works like a charm.

Collectives™ on Stack Overflow

Variable loop scope

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related