Find specific text in VBA watch list

Question

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.

Answer 1

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.