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: http://support.microsoft.com/kb/224331 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 33 in total (Show All Comments):Comment by: Bruno (5/16/2008 12:56:08 PM)Hallo Jan,
a really wonderful tool - it works perfect on the Excel-sheet and helps to save a lot
of time. Many thanks for your work!
Does there exist a similar tool for the VBE-objects of a userform?
Comment by: Jan Karel Pieterse (5/18/2008 3:12:49 AM)Hi bruno,
No, but you do not need a separate tool. You can insert a userform in the
objectlister VBAProject with just one control (the one you want listed) and change
the routine in modMain like this:
Change this line:
Set .ObjectToList = Selection
To:
Set .ObjectToList = UserForm1.Controls(1)
Comment by: Bruno (5/18/2008 3:00:20 PM)Hello Jan,
many thanks for your help.
I have tried your instructions with the sample userform1 within your object lister
Project and changed the above mentioned line. I started "startform", but it produces
the following error:
Error -2147024809; Ungültiges Argument in Startform.Module.modMain
and the procedure stopped in the changed line.
Do you have any idea?
At the moment it would be very helpful for me, because Excel produced an error in my
program (the exit-event will not be fired anymore in my existing userforms), so i
have to build them again).
Greetings from the Allgäu
Bruno
Comment by: Jan Karel Pieterse (5/18/2008 11:58:35 PM)Hi Bruno,
Sorry, I did not test the code. This does work with a commandbutton called
CommandButton1 on the form called Userform1:
Set .ObjectToList = UserForm1.CommandButton1
Comment by: Go (3/5/2009 7:17:20 PM)Hi..
i'm rookie in this vb things, it's very confusing. From your creation here, does it use only for charting? what about other objects and their properties? can i get the explanation of them (so it would not just a list)?
very appreciate what you've done
Comment by: Jan Karel Pieterse (3/6/2009 2:40:42 AM)Hi Go,
To be honest I don't think this utility is the best place to start learning VBA. Excel VBA Help is a better place to start and the object browser (F2 from the editor) is also a good resource.
Also, I'd avise you to go to the library and get yourself a good book on VBA. For beginners, the dummies series are a good start.
Comment by: Narendra (6/26/2009 1:48:19 AM)I see that Articles on your page are split in Steps by Step Procedures, each located on a different page.
It will be better to use a "one page per article" concept.
For browsing on a long page one can always use, tags/bookmarks & "Back to Top" at each topic end.
Just a suggestion.
Regards,
Narendra.
Comment by: Jan Karel Pieterse (6/26/2009 4:01:27 AM)Hi Narendra,
Thanks for your comment.
There is something to say for either method. Personally I prefer shorter pages (in general) over long pages with bookmarks. And short pages load faster.
Have a question, comment or suggestion? Then please use this form.