8

The VBA watch list allows you to monitor variables as you step through your macro line by line. Is there a way to expand out all the properties of objects contained within the watch list and search through them for specific text? A single line from the watch list can be copied into notepad or word etc., but there does not appear to be a way to export the entire contents of the watch list or search through all the lines at one time.

I am trying to determine what the specific property of an object on an Excel Spreadsheet is. Being able to find the text contained within it on the watch list would expedite my search greatly.

Shrout1
  • 2,497
  • 4
  • 42
  • 65
  • 2
    have you seen Object Browser? F2 –  Sep 17 '13 at 15:14
  • Use the object browser per @mehow's suggestion. Not sure if the watch list allows that sort of programmatic access, but even if it does, if you don't know how to use the Object Browser, trying to automate the VBE would be a very tall order. – David Zemens Sep 17 '13 at 15:18
  • 1
    It's possible that I am using Object Browser incorrectly, but I am really looking for properties in context. I'm sure that I could *eventually* identify the property I am seeking by hunting and pecking for it, but similarly to CSS (to locate items visually), you manipulate an object to have a very unique characteristic and then identify it's location. In the same way, I would like to put unique text into a field (for example) and then use that text as a target identifier to find the associated property. – Shrout1 Sep 17 '13 at 16:19
  • I suggest simply asking about the property as a question on SO -- it might even be faster! :) As far as I know, it's not possible to search through a live object's properties in a meaningful way because VBA doesn't support [Type Introspection](http://en.wikipedia.org/wiki/Type_introspection). The only alternative is to search through the object definitions through the Object Browser as others have suggested. – Blackhawk Oct 29 '13 at 17:55
  • @Blackhawk Not strictly true, as in my below answer you can see that limited type introspection can be done in VBA for public objects and members the same way the Object Browser does it. Use the TypeLib Information Object Library. You can access the information the Object Browser uses programmatically with that library. – AndASM Nov 12 '13 at 17:37
  • I stand pleasantly corrected - TypeLib looks like some powerful mojo! I have to add, I was also incorrect because of the [TypeName function](http://msdn.microsoft.com/en-us/library/5422sfdf(v=vs.90).aspx) and the [TypeOf operator](http://msdn.microsoft.com/en-us/library/vstudio/0ec5kw18.aspx), both of which are technically forms of [Type Introspection](http://en.wikipedia.org/wiki/Type_introspection), even though they wouldn't be an answer to the OP's question. – Blackhawk Nov 12 '13 at 18:34

1 Answers1

5

The closest you'll get in VBA (which is basically VB6) is the TypeLib Information Object Library. There is some example code here and in Self Inspection of VB6 UDTs.

You could create a function that scans through the members of an object for certain names or values. This function could be called from the Immediate window while debugging and output it's results via Debug.Print. Due to the limitations of the language and environment you can only search the public members of public objects, types, and interfaces.

Example

For my own curiousity I created a simple example. It only does a shallow single level search, and only looks at member names, not values. You'd probably want to read through the documentation so that you can query the values of members (when they are properties) and possibly look at some sort of recursion for members that return objects, collections, or interfaces.

To run this code you need to add a reference to TypeLib Information in the VBA editor.

Option Explicit

Sub FindMember(target As Object, searchString As String)
  Dim interface As TLI.InterfaceInfo
  Dim member As MemberInfo
  
  Set interface = TLI.InterfaceInfoFromObject(target)
  
  For Each member In interface.Members
    If InStr(1, member.Name, searchString, VbCompareMethod.vbTextCompare) > 0 Then
      Debug.Print "Found in " & interface.Name & " member " & member.Name
    End If
  Next
End Sub

Public Sub Test()
  Debug.Assert False
End Sub

I then ran the Test sub. When it paused on the Debug.Assert False line I ran my FindMember sub in the Immediate window, getting the results below. (You don't have to be debugging active code to use this function, it can be used from normal code or from the immediate window when nothing is running. I just did that to ensure it could be used while live code is paused).

FindMember Application, "ang"
Found in _Application member Range
Found in _Application member MaxChange
Found in _Application member MaxChange
Found in _Application member UILanguage
Found in _Application member UILanguage
Found in _Application member LanguageSettings

In theory the TypeLib Information Object Library should be able to look at anything the Object Browser can. A point of interest, try defining a private sub in your own project. You'll see that the Object Browser cannot view it, but can view the public members you've defined in your modules and (public) classes.

Community
  • 1
  • 1
AndASM
  • 9,458
  • 1
  • 21
  • 33