23 thoughts on “Listing an Object’s Properties and Methods”

1. Jan Karel,

   Very sweet. That’s on my instant “convert to add-in” list. A lot less scrolling up and down the intellisense lists knowing it’s there somewhere.

   A couple of quickies. The VBA help file is coming back from the typelib with the wrong path and an lf or something at the end. On my machine XPPro64 (don’t ask why), it has an English language subfolder: C:Program Files (x86)Microsoft OfficeOFFICE111033VBAXL10.CHM. I just hard coded that path into the class to get it to work.

   Second, the help api doesn’t seem to work for me even after that fix, but here’s a simple solution in your form code that seems to do the trick quite nicely:

   Private Sub cmbHelp_Click()
   On Error Resume Next
   Application.Help moScannedObjects(trvObjProps.SelectedItem.Index).HelpFile, _
   moScannedObjects(trvObjProps.SelectedItem.Index).HelpContext
   ‘your original
   ‘HtmlHelpCtx moScannedObjects(trvObjProps.SelectedItem.Index).HelpFile, _
   ‘ moScannedObjects(trvObjProps.SelectedItem.Index).HelpContext
   End Sub

   Nice job. I’ll keep playing with it.

   Robin.

2. Hi Robin,

   So I could have done without the Help API. Thanks for pointing that out.

3. Thanks for making this available. Very useful!

4. This is an awful lot like “Add Watch” which you could use for Application or for ChartTitle in this case. Add Watch is also a treeview that you can browse for properties just like this but it doesn’t require any addins and I bet that this one suffers from the same slow performance problem if you select an object with too many children like Cells etc.

5. Jan,

   Yes, ‘Add Watch’ is what I’ve used, but it’s more of a workaround than a good solution. I’ve used it particularly for viewing pivottable/cache information (watch: Activeworkbook.PivotCaches), or “Selection” works too. :)

6. I know, you can use the Locals window as well. I find this tool a bit easier because you don’t have to start debugging and I needed the “report” functionality too.

7. Robin,

   I checked the Help bit again. One (big!) advantage of using the API is that is shows the help window with the navigation pane to its left, whereas the Application.Help only shows you the help text in a single screen without any navigation options (O how I detest that!!!).

8. Yup. That said, the API was not running on my box and it took one line of code to get something usable!!!! I’ll go for working simplicity most times. Hard to say why with the API as usual.

   One of the nicer things to see in this is that using the treeview from the common controls you get mouse wheel handling, presumably because it has its own handle, unlike a msforms.control. The situation where I do go for complexity is wheel handling which I find indispensible, with all the API rubbish that goes with it.

9. Jan Karel,
   Excellent piece of work, as we have come to expect.

   I can see an immediate use for this: to script a userform control’s design time property values as VBA i.e. to set the equivalent properties at run time. There are a couple of required tweaks that I can see.

   The Property Get clsFoundMember.value always returns a String, whereas I need a strongly-typed value to know how to format the value assignment in the script e.g. use TypeName(cFoundMember.value) to determine whether to wrap the value in double quotes if it’s truly a String (I thought cFoundMember.TypeName would give me this information but not so!)

   Similarly, I’d want to keep Long values strongly-typed as Long and only format them at the point of use, whereas the code currently converts Long to String (“adjust long values; convert to hex”) in the Property Let.

   These are easy tweaks, of course; mine is a heads-up for anyone interested in doing something similar.

   Jamie.

   –

10. Robin: Agree on the Mouse wheel. Although I haven’t had the courage to implement that in any of my tools yet.

    Jamie: Thanks. Valid comments too, but outside this tool’s scope of course, I’m just showing the values and hence they need to be converted to strings.

11. The TypeLib Information component doesn’t seem to recognize some properties as ‘objects’. To demonstrate the problem, change the ufObjBrowser.ObjectToList Property Get to

    Set ObjectToList = Me.trvObjProps

    (i.e. report on the userform’s own treeview control).

    The Font property has not returned a IFontDisp, rather the font name (default property of IFontDisp class) as String; this could be a show-stopper for me.

    PS you may want to trap ‘Member not found’ (and similar) errors when calling InvokeHook, to prevent the unimplemented properties of the MSForms.Control base class (e.g. Cancel, Default, OldHeight, etc) appearing as ‘blank’.

    Jamie.

    –

12. Hi Jamie, That font thing is a problem indeed. Any workarounds?

13. jkp: That font thing is a problem indeed. Any workarounds?

    Natural of the beast (interfaces, IDispatch and Variants in VBA), methinks. Which interface you get for the Font property is determined by how you call it:

    Dim s As String
    s = TextBox1.Font

    Dim f1 As stdole.IFontDisp
    Set f1 = TextBox1.Font

    Dim f2 As stdole.StdFont
    Set f2 = TextBox1.Font

    Dim f3 As MSForms.NewFont
    Set f3 = TextBox1.Font

    Dim o As Object
    Set o = TextBox1.Font
    Debug.Print TypeName(o)  ‘ IFont

    Dim v1 As Variant
    v1 = TextBox1.Font
    Debug.Print TypeName(v1)  ‘ String

    Dim v2 As Variant
    Set v2 = TextBox1.Font
    Debug.Print TypeName(v2)  ‘ IFont

    One modification for your code would be to try Set before Let e.g.

    Set vReturn = Nothing
    Err.Clear
    Set vReturn = tliApp.InvokeHook(ObjectToList, mMember.MemberId, INVOKE_PROPERTYGET)
    If vReturn Is Nothing Then
      vReturn = tliApp.InvokeHook(ObjectToList, mMember.MemberId, INVOKE_PROPERTYGET)
    End If

    …but then you’d then need to change the way an object is identified (using

    TypeName(vReturn) = “Empty”

    no longer works!), how you expand properties that are objects, etc. Of course, you could use get the type library using the registry then use IUnknown to determine the interface…

    Ironically, your demo appeals to me because it will help me move VBA6 code to .NET so that I can leave all this COM stuff behind! Your code does 80 percent of the job and that’s good for me. Thanks again.

    Jamie.

    –

14. jkp> Valid comments too, but outside this tool’s scope of course

    I have to say, as presented it seems said scope is, “reinventing the VBE Locals window” (i.e. a list of an object variable’s run time properties’ — types and values — in a treeview, expandable where the property is itself an object) with perhaps “for fun” thrown in :)

    I think there is scope for an abstract class(s) to recursively extract strongly-typed properties (objects as well as intrinsic types) from a running instance of an object to fulfill the stated aim, “get all of an objects properties into your VBA code”, hence my comment about separating formatting from logic. And no, I’m not volunteering :) I know enough about the multi-faceted nature of type libraries (hidden members, restricted members, like-named members, base class resolution, etc) to know I’m not interested in developing it much further.

15. Jamie: the trick on first using SET worked. Will update site with new version later today.

16. I have just updated the tool to build 003, see page on my website.

17. Hi Everyone,

    Thanks jkpieterse for this great little tool. However, I was under the impression that I’d be able to get to the object data where the series / range data info is held with this tool (ie. =SERIES(Sheet1!$B$3,,Sheet1!$B$4:$B$6,2) ). I’ve drilled down to 8 levels so far on the chart example you provided but haven’t been able to find it. Please could you advise?

    Many thanks

    RodP

18. The chart object is a bit odd here. I too had difficulty finding the Series collection of a chart object. I’m no Chart/VBA expert, maybe someone like Jon Peltier might know?

19. I haven’t dug into the tool to this extent yet. The thing is, though, the best way to get at the series data is through the series formula, and it’s text-only. If you access the .Name, .XValues, and .Values of a series, you don’t get range references, only the values bieng used, so it’s a string for the name and an array for X or Y values. John Walkenbach’s chart series class module (http://www.j-walk.com/ss/excel/tips/tip83.htm) parses the series formula and is smart enough to return a range if the argumant you want is a range. I use variations of John’s class in some of my work.

    The way the OM looks for a chart is:

    Chart Sheet:
    Application > Workbook > Chart > see below

    Embedded Chart:
    Application > Workbook > Sheet* > ChartObject > Chart > see below

    Sheet = Chart sheet, Worksheet, Dialog sheet (dropped in 2007)

    Below:

    Chart > SeriesCollection > Series

    Under Series:
    .Formula (text) **
    .Values (array of numerical values) **
    .XValues (array of values – numerical or text) **
    .Name (text) **
    .ChartType
    … tons of formatting stuff

    ** don’t expect any range objects

20. Hi Jon & Everyone

    Many thanks indeed for your replies and the link to j-walk website for this solution – that’s great.

    I have however another question – one which is probably going to require me sending an excel file to someone (if they are willing to help). I use an addin called ‘Nitro’ and it pulls in data from external databases depending on what you’ve selected. Nitro creates a little object in excel, which houses the information you’ve requested including any settings. Every week or month you can then simply refresh the object, which then refreshes the data range. This is normally fine and dandy except when you some of your selection criteria changes across the whole board. Eg. We have a trading company dimension where in the next few months we’ve agreed to change it from AAA to BBB. It would be great to run a program which will go through every Nitro Object and edit/replace AAA to BBB in the SQL query statement. The reason why I need to automate this is becuase I have a 1000 of these objects to edit!

    I’ve tried using the Object Browser to find the information needed to change but can’t find it (i can find one version which is a temporary statement but not the master statement).

    If anyone out there has much of an idea about OLE objects and how data can be stored in them, I’d be really grateful for some help.

    For your info I’ve also posted this request at:

    http://www.tek-tips.com/viewthread.cfm?qid=1368761&page=1

    …but haven’t been to get a solution as of yet. Please feel free to review the info in this link however as it houses some more information which might help you understand what my problem is.

    Many thanks in advance

    RodP

21. Have you tried my Flexfind tool at http://www.jkp-ads.com/download.asp ?

22. Hi jkpieterse,

    I use that most days – it’s brilliant! :) – but unfortunately doesn’t seem to look through the object (it’s an OLE object if that makes any difference).

    Would anyone out there be happy to receive an example object in an excel file to look at?

    Many thanks in advance

    RodP

23. Sure,visit my site and use the email link on the contact page.

Posting code? Use <pre> tags for VBA and <code> tags for inline.