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

Objectlister.zip

Comments

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

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.

Comment by: Mark Aldous (7/22/2009 4:12:06 AM)

Hi,
your sample app is almost perfect for my needs, but there is one extra bit I am trying to figure out. As part of the development process of my app I want to report to a log file the names and values of properties of numerous classes. This is all working well until the class contains a collection of another class. Can you give some advice on how to get the objects from the collection and add them to log.

Below is my code for now
many thanks
Mark

Public Function GetObjectProperties_v2(ByVal Obj As Object) As String
Dim cObjAnalyser As clsAnalyseObject
Dim cFoundMember As clsFoundMember
Dim sReturn As String

sReturn = vbNullString

Set cObjAnalyser = New clsAnalyseObject
With cObjAnalyser
    Set .ObjectToList = Obj
    .Root = True
    .IgnoreParentAndApplication = True
    .IterateMembers
End With

If Not cObjAnalyser.PropsAndObjects Is Nothing Then
     For Each cFoundMember In cObjAnalyser.PropsAndObjects

         If cFoundMember.IsObjectOrCollection Then
            sReturn = sReturn & " <" & cFoundMember.Name & "(" & CStr(cFoundMember.TypeName) & ")" & ">"
            Debug.Print cFoundMember.Name & "(" & CStr(cFoundMember.TypeName) & ")" & ">"
            ' now I need to get child objects from the collection and report the properties for each object

         Else
            sReturn = sReturn & " <" & cFoundMember.Name & "=" & CStr(cFoundMember.value) & ">"
            Debug.Print vbTab & cFoundMember.Name & "=" & CStr(cFoundMember.value) & ">"
         End If
     Next
End If

End Function

Comment by: Jan Karel Pieterse (7/22/2009 5:17:38 AM)

Hi Mark,

Not sure if this will work, but you might try recursing the function and adding an optional argument which adds some text if the function has been used recursively:

Public Function GetObjectProperties_v2(ByVal Obj As Object, Optional sText2Add As String = "") As String
    Dim cObjAnalyser As clsAnalyseObject
    Dim cFoundMember As clsFoundMember
    Dim sReturn As String

    sReturn = vbNullString

    Set cObjAnalyser = New clsAnalyseObject
    With cObjAnalyser
        Set .ObjectToList = Obj
        .Root = True
        .IgnoreParentAndApplication = True
        .IterateMembers
    End With

    If Not cObjAnalyser.PropsAndObjects Is Nothing Then
        For Each cFoundMember In cObjAnalyser.PropsAndObjects

            If cFoundMember.IsObjectOrCollection Then
                sReturn = sReturn & " <" & cFoundMember.Name & "(" & CStr(cFoundMember.TypeName) & ")" & ">"
                Debug.Print cFoundMember.Name & "(" & CStr(cFoundMember.TypeName) & ")" & ">"
                If cFoundMember Is Collection Then    '<-----please check, this may be wrong!
                    GetObjectProperties_v2 cFoundMember, sText2Add & " " & cFoundMember.Name & ":"
                End If
                ' now I need to get child objects from the collection
                ' and report the properties for each object

            Else
                sReturn = sReturn & " <" & cFoundMember.Name & "=" & CStr(cFoundMember.Value) & ">"
                Debug.Print vbTab & cFoundMember.Name & "=" & CStr(cFoundMember.Value) & ">"
            End If
        Next
    End If

End Function

Comment by: Mark Aldous (7/22/2009 6:15:39 AM)

Hi Jan,
thanks for the reply.

I changed your class (clsAnalyseObject) so that it returned "_Collection" for the type name (cFoundMember.TypeName = mMember.ReturnType.TypeInfo.Name).

Unfortunately when I then tried to call it recursively it was sending through the cFoundMember object not the child object of the collection.

A simple example of what I am trying to read is as follows.

objClass1
propName
colChildren
objClass2
propId
objClass2
PropId

For now I am going to call the objects in the child collections through type specific subs. I will try to investigate further at a later date and let you know of any progress.

many thanks
Mark

Comment by: Stephen (8/4/2009 7:53:16 AM)

What are the prerequsities for this to run? I had it running on Windows XP SP3 but after I reimaged my machine it no longer works. I receive the a compile error, "Can't find project or library" at the subroutine, "Private Sub AddObjectToTree(oObj As Object, nNodeToAdd2 As MSComctlLib.Node, bRoot As Boolean, IgnoreParentAndApp As Boolean)".

Comment by: Jan Karel Pieterse (9/7/2009 8:59:55 AM)

Hi Stephen,

See the "What does it do" section for instructions on what you need to make it 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.