可变循环作用域

Question

我有一些 vba 代码，我想根据布尔值遍历不同的范围。我希望它看起来像这样：

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

错误消息告诉我我只能迭代集合对象，这是有道理的，但是我不知道如何将 LoopScope 调暗为集合对象 (？(。该循环在键入时同时有效：

For Each ChartObject in Selection

和：

For Each ChartObject in ActiveSheet.ChartObjects

我只是无法弄清楚如何使范围依赖于我的布尔值。提前感谢您的时间。

Answer 1

使用后期绑定和错误处理。

你缺少的主要内容是Set关键字;没有它，你就是在强制对象，[如果你幸运的话]最终会得到一个内在值类型(例如String、Long等(...或运行时错误。

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?