Listing an Objects Properties and Methods
Introduction
If you do some VBA programming regularly, you'll be acquainted with the Object browser, which neatly shows you all objects, properties and methods belonging to any object in Excel (or any other library you have referenced in your VBA project).
Whilst this is a tremendous tool to look for information on just a couple of properties I find it not very handy when you e.g. need to get all of an objects properties into your VBA code.
So because I like a challenge I decided to build my own object browser, which is shown to you here.
What does it do?
This tool uses the tlbinf32.dll to fetch data from typelibraries. I grabbed some code from various places (amongst which Chip Pearson's great site and some newsgroup messages like this one) and created a userform with a treeview control (because this is the type of control that can show hierarchical information so nicely).
The help files of tlbinf32.dll can be downloaded here: tlbinf32.zip and the dll can be found here (not sure if you can use the download at will without any licensing issues though).
After downloading the dll it needs to be registered with windows:
Click Start, run and type:
regsvr32 c:\YourPathToThedll\tlbinf32.dll
Click OK. If successful, Windows will tell you it successfully registered the dll.
In its basic state, you select any object in Excel and start the tool, which will show you the first level set of objects, properties and methods of that selection. Here is what the UI looks like after starting the tool whilst you have a chart title selected:
Tool's main window, showing members of a ChartTitle object
And here is the same window after expanding two of the object's members:
Main window after double clicking "Border" and "Font" objects.
As you can see, it is really easy to get a list of all objects and properties of an object.
What's even better is that you can get them into Excel easily. Clicking the "Report" button yields a list of all objects in the current display. Below a portion of those is shown:
Result of clicking "Report".
You can imagine that now it is easy to copy this and paste it into the VBE to quickly create code that sets a lot of properties.
Download
I have made this tool available for download:
Comments
Showing last 8 comments of 75 in total (Show All Comments):Comment by: David Miley (1-6-2019 17:46:00) deeplink to this comment
Will this work on custom objects (i.e. classes that I have created)?
Comment by: Jan Karel Pieterse (3-6-2019 09:38:00) deeplink to this comment
Hi David,
I don't know, perhaps you should just try?
Comment by: Romulus Milea (26-3-2024 12:49:00) deeplink to this comment
Hello Jan,
Can this tool be used to generate a huge list in Excel, for all libraries, for all classes, and also for all members of each class, with no connection to any project ?
With other words, can this tool dump all objects from Excel Object Browser, in a form of a list with 4 columns: Library, Class, Member and Type (Function, Sub, Enum, Property, Constant, etc.) ?
I need this list for a personal project. I have created a thread on www.MrExcel.com forum, and RoryA suggested this WebPage, of yours :-))).
Thank you !
Comment by: Jan Karel Pieterse (26-3-2024 16:03:00) deeplink to this comment
Hi Romulus,
Theoretically, yes. I built the tool to get a list of all properties and methods of a given object. If you let it do the application, it will take a huge amount of time to complete.
Comment by: Romulus Milea (26-3-2024 16:11:00) deeplink to this comment
Hello again,
Thank you ! Can you maybe suggest how can I step by step configure/use this tool to generate that list of all possible components of entire Excel application ? I can wait for it, to complete everything :-))).
Thank you once again !
Comment by: Jan Karel Pieterse (26-3-2024 16:27:00) deeplink to this comment
Hi Romulus,
I think you will find this workbook isn't necessarily ready to do a thing like that. I used to to list all properties and methods of a specific object, doing the entire application is not something it can do reliably I suspect.
Comment by: Romulus Milea (27-3-2024 07:51:00) deeplink to this comment
Thank you for your answer. Please help me to find out how this shall be done step by step, and I will give it a try.
Comment by: Jan Karel Pieterse (27-3-2024 16:27:00) deeplink to this comment
Hi Romulus,
Please get hold of me through email (address at the bottom of this page)
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.