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:

Screen-shot of the ObjectLister's main dialog showing chart object properties
Tool's main window, showing members of a ChartTitle object

And here is the same window after expanding two of the object's members:

Screen-shot of the ObjectLister's main dialog showing chart object properties
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 the Report button
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 69 in total (Show All Comments):

 


Comment by: Jan Karel Pieterse (3-4-2015 19:25:15) deeplink to this comment

Hi Lisa,

No just the control properties. As far as I recall, the object lister ignores events.


Comment by: Lisa Green (3-4-2015 22:28:28) deeplink to this comment

I'm wondering if it's a problem with "windows"... AFAIK an object like a command button is actually a window while a userform is contained within a window... and no... I'm not sure about what I'm saying!!! :-)

Shame about not listing events... Googling and following links for a loooooooong time is't helping.. :-( I've managed to get lists of events from various sites but some of the lists miss some events that I know exist and some include events I know... I think I know.. are spurious.

It seems the best way so far is to go to the VBE and womanually write down the events from a drop down box!!!

It's an old story I think Jan Karel... They're somewhere ... otherwise the listbox would be up the spout... ut where!!???

Lisa


Comment by: Jan Karel Pieterse (5-4-2015 15:04:46) deeplink to this comment

Hi Lisa,

I'm afraid that is the only way I see right now.


Comment by: shg (11-11-2015 20:47:39) deeplink to this comment

Jan Karel,

After copying the dll, when I run regsvr32 C:\Windows\System32\TLBINF32.DLL, I get

The module "C:\Windows\System32\TLBINF32.DLL" failed to load. Make sure the binary is stored at the specified path [it is] ... The specified module could not be found."

W7 64 bit, Excel 2010 32 bit.

Is it obvious what I'm fat-fingering?


Comment by: Jan Karel Pieterse (12-11-2015 11:03:50) deeplink to this comment

Hi shg,

To be honest, I haven't touched this in a long time and I forgot how I set this up on my system! The file itself is both in my Documents folder somewhere and in the C:\Windows\SysWOW64 folder and it works as expected so I guess I somehow managed to register the dll successfully.
Perhaps registering it from the C:\Windows\SysWOW64 folder should work (remember to run cmd as an admin!)


Comment by: shg (12-11-2015 16:58:37) deeplink to this comment

I will try that, thank you.


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?


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.




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