Turn Excel VBA Object Browser into a 4-column long list

[RomulusMilea]

Hello All,

I need an Excel macro that will loop through following object libraries: Excel, Office, stdole, and VBA, then for each library will loop through all object Classes, on each class it will loop through all class members and create a table having following 4 columns: Library, Class, Member and Type, where type can be Function, Constant, Sub, Property, Enum, etc.

Table must start from cell A1 of the Sheet1 tab.

I suppose I need to activate/tick first Microsoft Visual Basic for Applications Extensibility 5.3.

I expect the list will contain thousands of rows, maybe more.

This would basically be an exact replica of Excel Object Browser, but turned into an Excel list. I need it for a personal project.

Could anyone please generate the code ? Or is the code available somewhere else ?

Thank you in advance !

Regards,
Romulus.

 

[Jaafar Tribak]

@John_w
Thanks for the feedabck and for sharing your code.

Jaafar. You must have put a lot of effort into writing and testing it.

Yes. It took me some researching of the ITypeLib and ITypeInfo interfaces as well as some trial and error to reach optimal results particularly when trying to extract Event routines.

I've written the code below which uses UIAutomation to scrape the Object Browser and output the details to a sheet.

That was the first thought that came to my mind when I first read the question. Unfortunately, scraping the vbe object browser via UIAutomation is very slow as you mentioned and most importantly it relies on the browser being on display which makes it kind of clumsy & messy.

Just so you know, I have tested your code on the small stdole library (11 classes) and got runtime error 91 (object variable or With block variable no set) on the following line:

in the Automate_Object_Browser routine.

    'Ensure the combo box is collapsed (closed)
    Set librariesComboExpColPattern = librariesCombo.GetCurrentPattern(UIA_ExpandCollapsePatternId)

I added On Error Resume Next to skip the above error and noticed that the classes are scraped 3 consecutive times. I haven't studied your code in detail but the worksheet doesn't get populated with any info except for the table headings.

 

[John_w]

Just so you know, I have tested your code on the small stdole library (11 classes) and got runtime error 91 (object variable or With block variable no set) on the following line:

in the Automate_Object_Browser routine.

'Ensure the combo box is collapsed (closed)
Set librariesComboExpColPattern = librariesCombo.GetCurrentPattern(UIA_ExpandCollapsePatternId)

I added On Error Resume Next to skip the above error and noticed that the classes are scraped 3 consecutive times. I haven't studied your code in detail but the worksheet doesn't get populated with any info except for the table headings.

Thanks for trying my code, @Jaafar Tribak. That error indicates librariesCombo isn't set, which might be due to timing issues with the OB UI. Try replacing this line:

        Set librariesCombo = OBwindow.FindFirst(TreeScope_Descendants, ControlTypeAndNameCond)

with this delay loop:

    Do
        Set librariesCombo = OBwindow.FindFirst(TreeScope_Descendants, ControlTypeAndNameCond)
        DoEvents
        Sleep 100
    Loop While librariesCombo Is Nothing

You might need to add more Sleep delays where the code is controlling the UI, for example this part:

            'Select this Library item, causing the Library combobox to scroll to it, if necessary
           
            Set libraryItemSelectPattern = librariesListItem.GetCurrentPattern(UIA_SelectionItemPatternId)
            libraryItemSelectPattern.Select
            DoEvents
            Sleep 100

            'Click Open button to open and expand Libraries combo box

            OpenPattern.DoDefaultAction
            DoEvents
            Sleep 100