0

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.

Improve this question
5
  • You could have have a For/Each in the If part and one in the Else part 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? 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? 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. Commented May 23, 2020 at 21:27
  • 1
    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. Commented May 23, 2020 at 21:50

1 Answer 1

Reset to default
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?
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.