Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Object Lister

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 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:

Objectlister.zip


Comments

Showing last 8 comments of 58 in total (Show All Comments):

 


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!

 


Comment by: Joe Anderson (6/22/2013 2:07:53 AM)

I used your ObjectLister extensively with Windows Vista. I now have Windows 7 with Excel 2010 and cannot get ObjectLister to work. I get a compile error: "Method or data member not found" on this line of the Sub StartForm:

    
.trvObjProps.SetFocus


Have tried lots of different things, un-/re-register DLL, etc. but am stumped.

Yours is a great tool, which I hope to continue to use. I would be grateful for any advice.

Joe

 


Comment by: Jan Karel Pieterse (6/24/2013 1:38:26 PM)

Hi Joe,

Hard to say what's wrong, most likely the dll has not been properly registered. You may have to also change the references in the VBA project of the Excel demo file I created to make sure it points to the installed dll.
I expect you'll have to run the registration as an administrator to make it "stick".

 


Comment by: Gomel (7/10/2013 10:01:53 PM)

Hello, do you have same project for powerpoint 2010 64bit?
thanks

 


Comment by: Jan Karel Pieterse (7/11/2013 9:18:52 PM)

Hi Gomel,

Not needed. You can add a reference to the Powerpoint object model in Excel VBA and then set an object variable to the object you want to look at. But you also need to start PPT form Excel VBA and make sure that object in question is in the current presentation, so you can actually set the object variable to that item.

 


Comment by: PJ in FL (3/17/2014 4:04:25 PM)

Just a note to say I had difficulty downloading the DLL from the link provided.

However, the file is included in the COM Add-In Installer package downloadable from Chip Pearson's "Creating A COM Add-In In Visual Basic 6" page, located at this link address: http://www.cpearson.com/excel/ComAddInInVB6.htm

Personally, I cringe when I need to download a DLL or other active component from a location I don't know, so finding the file on Chip's site was a welcome relief!

 


Comment by: George Skelton (5/25/2014 4:56:12 PM)

Hi, I'm trying to get this working with 64-bit Excel on Windows 7 Excel 2010. I have tlbinf32.dll saved in C:\Windows\System32, I run regsvr32 from there and get "DllRegisterServer in TLBINF32.DLL succeeded". However when I try to add the Reference in VBA, I browse, select the dll and click open but it won't appear in the Available References list with a checkmark (but there is no error message).

If on the other hand I put tlbinf32.dl in C:\Windows\SysWOW64 and register it there, then I can add the reference but when I try to use it I get Run-time error '429': ActiveX component can't create object

 


Comment by: Jan Karel Pieterse (5/25/2014 5:00:20 PM)

Hi John,

The tlbinf32.dll is 32 bit, which means it will not work with 64 bit Office I'm afraid.

 


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.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].