How to convert string to corresponding Excel object?

[G Lykos]

Greetings! Would like to do something along the lines of the following:

Dim o as Object
Dim s as String
Dim i as Integer

s = InputBox ("Target collection?", , "ActiveWorkbook.Styles")
Set o = GetByName(s) ' to be the same as though the line read <Set o =
ActiveWorkbook.Styles>

' to be followed by something like -

Debug.Print s & " has " & i & " items:"
For i = 1 to o.Count
Debug.Print o(i).Name
Next i

Need an Excel object GetByName method. Thanks for any ideas!
George

 

[Tim Williams]

Sub Test2()

Dim s
Dim o, o2, v

s = "ActiveWorkbook.Styles"
v = Split(s, ".")
Set o2 = CallByName(CallByName(Application, v(0), VbGet), v(1), VbGet)
Debug.Print o2.Count
End Sub

....for what it's worth.

Tim.

 

[G Lykos]

Tim, works perfectly with the example target string/object (Excel 2003) -
thanks!!

As I recall, Application is the top of the Excel object tree. In the
example target string, ActiveWorkbook is a child to Application, and the
target collection one more step down. Any query string with this structure
should therefore work.

Would you have thoughts as to how this might be further generalized such
that any model collection could be thus listed? Modifying the Set command
so it nests as deep as the layers present in the target string would at a
glance seem straight forward, but how to deal with needing to always start
at Application and work down? Maybe this aspect will turn out to be a
non-issue when I look at different collections of interest.

In the meantime, I admired your example - Split was a neat way to parse the
chain, and I'll study CallByName to better understand how it interacts with
the Excel model. Your use of variants is not an approach I would have
thought to use, but fits the sub with elegant simplicity - I learned
something there, too.

Thanks again,
George

 

[G Lykos]

I'll add regarding generalization - the target string would be one that is
valid as a designator on the right side of a Set statement.

 

[G Lykos]

Tim, via help and a little experimentation, discovered that Application in
the context of CallByName in this usage apparently is not fixed at the top
of the model but rather causes a search of the tree looking for the parent,
and as such works very nicely here in a generalized manner. A second target
string/collection object just tried successfully is ActiveSheet.PivotTables.

Neat stuff - thanks again!

 

[Tim Williams]

glad to hear the approach was useful.

My use of variants was more a laziness thing than anything. No reason not
to dim s as string and o2 as object (since the code uses "set" to assign the
value).

Here's a slightly more generic version (with purposeful use of variant for o
and v)

'*******************************
Sub Test3()

Dim s As String, i As Integer
Dim o, v

s = "Selection.Cells.Count"

Set o = Application

v = Split(s, ".")

For i = LBound(v) To UBound(v)
If IsObject(CallByName(o, v(i), VbGet)) Then
Set o = CallByName(o, v(i), VbGet)
Else
o = CallByName(o, v(i), VbGet)
End If
Next i

Debug.Print o
End Sub
'******************************


Tim

 

[G Lykos]

Perfect! Tim, thanks so much for the additional guidance.

George