Programmatically Walk the Object Model

[robhillier]

Greetings!

I'm trying to find a way to programmatically traverse the object model at run-time, and I'm curious if anyone knows how to accomplish this.

Perhaps some context would help. I have a 2007 workbook (a series of workbooks, actually) that have 'Styles' applied to cells. I need to find a way to get references to all cells that have had the 'Input' Style applied to them. (I actually have to loop through a series of custom Styles, but I'll use the 'Input' Style throughout this post so I'm speaking on common ground)

In Word, this is easy. The Find object has a Style property that can be set to a specific style name. One can then use Find.Execute to get instances of text that have that Style applied. Excel's Find object has no such property.

One solution would be to loop through each cell and check its Style property with something like:

For Each rngLoop In ActiveSheet.UsedRange
    If rngLoop.Style = "Input" Then
        '... My logic ...
    End If
Next rngLoop

but some worksheets are quite large (over 100,000 cells in one case, with a range of A1:EK788), so looping through each cell would be time-prohibitive for ONE Style, let alone many Styles, which I have to do (8 Styles, at the moment).

Excel's Application.Find is quite fast, and I would prefer to use this. To accomplish this, I would have to read the properties of the Style object, see if they have been set, and if so apply them to Application.FindFormat, and then execute the Find. The problem here is that I would have to hard code these checks and applications, like:

Application.FindFormat.Clear
With ThisWorkbook.Styles("Input")
 
    ' Does the Style include Borders properties?
    If .IncludeBorder Then
 
        ' Border colours
        Application.FindFormat.Borders(xlDiagonalDown).Color = .Borders(xlDiagonalDown).Color
        Application.FindFormat.Borders(xlDiagonalUp).Color = .Borders(xlDiagonalUp).Color
        Application.FindFormat.Borders(xlEdgeBottom).Color = .Borders(xlEdgeBottom).Color
        '...
 
        ' Border line Styles
        Application.FindFormat.Borders(xlDiagonalDown).LineStyle = .Borders(xlDiagonalDown).LineStyle
        Application.FindFormat.Borders(xlDiagonalUp).LineStyle = .Borders(xlDiagonalUp).LineStyle
        Application.FindFormat.Borders(xlEdgeBottom).LineStyle = .Borders(xlEdgeBottom).LineStyle
        '...
 
        ' Border line weights
        Application.FindFormat.Borders(xlDiagonalDown).Weight = .Borders(xlDiagonalDown).Weight
        Application.FindFormat.Borders(xlDiagonalUp).Weight = .Borders(xlDiagonalUp).Weight
        Application.FindFormat.Borders(xlEdgeBottom).Weight = .Borders(xlEdgeBottom).Weight
        '...
 
        ' And all other border properties...
 
    End If
End With

and then do the same for the Alignment, Font, Number, Patterns, and Protection properties, and each property of their sub-objects (Style.Interior.ColorIndex, for example). Labourious and not at all future-proof, as you can see.

This is why I would like to programmatically traverse the object model at run-time. It would allow me to recursively loop through the Style object's properties and sub-properties, and apply them to the FindFormat object's properties and sub-properties.

Does anyone know of a way? (And thanks for reading this far, by the way!!!)

Rob

 

[Norie]

Dan

Is there no other way you can search for these cells?

 

[robhillier]

If you're asking if there are any other defining characteristics about these cells, then no, unfortunately.

I should have mentioned, for those who have heard of TLI (TypeLib Info), I have looked into this, but TLI only allows me to iterate through each class in the object model; it does not allow me to read the relationships between each class.

For example, I can iterate through the Application class, the Workbook class, the Worksheet class, the Range class, and the Interior class, and each of their respective direct properties, but I can't find a way to read the relationship map of Application.Workbook.Worksheet.Range.Interior. Perhaps I'm missing something though?

Excel is obviously building and reading this relationship tree somehow. I just can't figure out how to read it myself, programmatically.

If anyone has any suggestions on how to read the relationships, or any other way in general that I can achieve the same result, I'm all ears... err... eyes.

 

[robhillier]

Sorry to bump, but my search is proving fruitless.

Anyone have any suggestions on how I can accomplish this?

 

[Norie]

Rob

The reason you might not be getting too many responses could be because either it's not possible, or it is possible but the code/work do to it isn't really worth it.

By the way how are you iterating through the classes using TypeLib Info.

Perhaps if you posted that it might inspire someone to look into the whole thing further.

 

[robhillier]

Fair enough. I hope this inspires you (or someone else)!

TLI is something that few people seem to know about. I've been a programmer for years, but had never heard of it until looking for a solution to this problem, which is surprising considering its potential usefulness. There have been other posts about it on this site, but none as thorough as what I'm about to post.

Chip Pearson used to have a fantastic page about TLI, but it has disappeared (apparently) from his website for some reason. I was lucky enough to come across a copy/paste of the page that someone had posted on another website (http://www.officekb.com/Uwe/Forum.aspx/excel-prog/2695/iterating-through-all-properties-of-an-object). At the expense of redundancy, I'll repost it myself, in case that link also dies one day (long live MrExcel! ).

-- Beginning of Original Post --

Download XLTLI - Type Lib Utilities
This download provides about 20 VBA utility functions for working with the TypeLib Information object library, which is used to read type libraries. TypeLib Info is the "engine" behind the VBA Object Browse, and this library gives you programmatic access to type libraries, allowing you to determine the various arguments for methods, the symbolic names of constants, the methods of an object and so on. Complete documentation is in the VBA code of this workbook. This code is for advanced VBA programmers, not novices. An advanced version of XLTLI is available for commercial customers.

Download http://www.cpearson.com/Zips/TLIUtils.ZIP (RH: this download link still works, even though its information page is missing)

You'll need TLBINF32 typelibrary from Bill G. et.al.
http://support.microsoft.com/default.aspx?scid=kb;en-us;224331

This contains a workbook called XLTLI.xls which has about 1200 lines of VBA code related to working with the Excel and Office type libraries. One of the procedures is called ParametersOfMethod which returns a Collection object containing CParam objects (CParam is a class defined in the VBProject), one CParam object for each parameter to the specified method.

Note: You must have the TypeLib Information DLL installed on your machine (typically C:\WINDOWS\SYSTEM32\TLBINF32.DLL) and referenced in the VBProject. (RH: The following works fine for my installation of Office 2007: C:\Program Files\Common Files\Microsoft Shared\OFFICE12\VS Runtime\VSTLBINF.DLL)

For example to get all the methods for the Sort method of the Range object, use code like:

 Sub ListRangeSortParamaters()
 
    Dim colParamaters As Collection
    Dim clsParamater As CParam
 
    Set colParamaters = ParametersOfMethod("Range", "Sort", SearchAll)
    For Each clsParamater In colParamaters
        With clsParamater
            Debug.Print .Position, .Name, .DataType, .IsOptional, .DefaultValue
        End With
    Next clsParamater
 
    Set colParamaters = Nothing
    Set clsParamater = Nothing
 
End Sub

(RH: I've modified the code example from what it was in the original post. The original had lazy variable names, a For...Next loop structure where a For Each... was more appropriate, and didn't release the object variables at the end.)

-- End of Original Post --

This code produces the following output (in Excel 2007, at least!):

 1            Key1          Variant       False         
 2            Order1        XlSortOrder   False          1 
 3            Key2          Variant       False         
 4            Type          Variant       False         
 5            Order2        XlSortOrder   False          1 
 6            Key3          Variant       False         
 7            Order3        XlSortOrder   False          1 
 8            Header        XlYesNoGuess  False          2 
 9            OrderCustom   Variant       False         
 10           MatchCase     Variant       False         
 11           Orientation   XlSortOrientation           False          2 
 12           SortMethod    XlSortMethod  False          1 
 13           DataOption1   XlSortDataOption            False          0 
 14           DataOption2   XlSortDataOption            False          0 
 15           DataOption3   XlSortDataOption            False          0

Another useful page on TLI is: http://msdn.microsoft.com/en-us/magazine/bb985086.aspx

One may also search this forum for TLI and / or TypeLib for other useful posts on this subject.

 

[Norie]

Rob

To be honest that looks like too much trouble to look into right now, and I'm wondering if it's actually worthwhile pursuing this approach.

Have you actually tried looping through all the cells?

Do you actually need to loop through all of them?

Is there no way to narrow the range(s) to go through?

PS What are you going to do with the cells once you've found them?

 

[robhillier]

To be honest that looks like too much trouble to look into right now...

I certainly understand! I didn't expect anyone to really look into it for me; I was just hoping that it would jog someone's memory, and they could point me in a new direction.

I really just re-posted all of that information about TLI because you asked, and so that other users can find it if and when they need to.

Have you actually tried looping through all the cells?

Yes. That's why I'm looking into this so intently. Examining each cell in the used range (sometimes more than 100,000 cells) of each worksheet (50+) of each workbook (tens) takes HOURS. Literally. This is why I'd rather use Range.Find with FindFormat set to the properties of the Style - it would be much faster.

Is there no way to narrow the range(s) to go through?

Other than UsedRange, no.

Do you actually need to loop through all of them?
PS What are you going to do with the cells once you've found them?

There are various reasons I need to build collections of cells of particular Styles at run time. One simple but laborious example is that pairs of cells throughout the worksheets have been set to custom Styles named "Opening Balance" and "Closing Balance". I need to dynamically locate all opening balance cells, all closing balance cells (with variable numbers of rows and/or columns between them), pair them up, and then roll the closing balance values to the opening balance cells. Since the layout of the input data is variable, I have to identify the cells on the fly.

Thanks for checking back with me though. I'm going to give it the rest of the day, and then move on tomorrow. I'll post back if I find a way. (I actually have an idea that I'm going to try right now)

 

[Norie]

Rob

Sorry to go on about it, but is there definitely no way you can identify the cells of interest?

Perhaps based on the surrounding cells content.

How variable is the input data?