Subscribe in a readerListing 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 it's 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 51 in total (Show All Comments):Comment by: Jan Karel Pieterse (5/30/2012 7:07:26 AM)Hi Walter,
If you look at the sub called "AddObject2Tree" you can see how the class module that handles the listing of an object is put to use. You can easily copy out this sub to a normal module and adjust it so it lists them in the immediate pane.
Comment by: juwen (6/7/2012 11:02:22 AM)Hi,All
My download file in this:
http://www.jkp-ads.com/downloadscript.asp?filename=Objectlister.zip
the tlbinf32.dll be registered with windows is successfully.
open the ObjectList 003.xls and hit ctrl+alt+o have show Run-time error:'13' Type Mismatch”
office 2010 in windows 7 to run the ObjectList 003.xls file
Comment by: Jan Karel Pieterse (6/7/2012 2:13:23 PM)Hi Juwen,
Two questions:
1. Which object was selected when you pressed the shortcut key?
2. Which line of cod eis highlighted if you click Debug?
Comment by: juwen (6/8/2012 2:48:27 AM)Hi,Jan
How to select an object?
Can you tell my how to use ObjectList 003.xls?
Comment by: Jan Karel Pieterse (6/8/2012 12:00:04 PM)Hi Juwen,
You select an object by just clicking on it in Excel. Then use the short-cut key menitoned in the tool.
Of course you have to download and register the tlbinf32.dll first, like I described above.
Comment by: juwen (6/9/2012 3:52:53 AM)Hi Jan,
1. I don't know which object I have to select.
Can you give me a specific example, please?
2. When I click Debug,the line of codeis highlighted is
"Set vReturn = tliApp.InvokeHook(ObjectToList, mMember.MemberId, INVOKE_PROPERTYGET)" in sub IterateMembers
Comment by: Jan Karel Pieterse (6/9/2012 8:58:16 PM)Hi Juwen,
You select the object (in Excel) from which you want to show the properties.
Comment by: Young7 (2/6/2013 4:22:06 AM)Thank you very much for this tool!
I know the tool is set up to work on visible objects, but I modified it to work with other (invisible) objects.
For example, let’s say I set “test” to “Internetexplorer.application”… I can send that object to your tool and it shows me the properties. Very cool!
You have me wondering if I can use this in VB6 also. This tool displays needed information in a very useful format.
I just changed your “StartForm” routine to accept an object. I also changed the “Set.objectToList=selection” to set.objecttolist=obj (the object I send it).
Excellent work!
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.