Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find specific text in VBA watch list

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.

like image 784
Shrout1 Avatar asked Sep 17 '13 15:09

Shrout1


People also ask

How do I find a specific text in Excel VBA?

INSTR Function Using VBA to Find String in a Cell You can find any string and its position from any cell. The VBA InStr Function indicates if a text string is detected in another text string. It returns 0 if the text is not found. Otherwise, it returns the character position where the text is located.

How do I find a specific word in VBA?

This functionality is equivalent to using the 'Find All' option you can choose when you use the normal find functionality available by pressing CTRL + F . Be sure to set strToFind to your desired string, and change Sheet1 to match the name of whichever sheet you want to search over.

How do you find a cell with a specific value in Excel VBA?

To find a cell with a numeric value in a cell range, set the SearchDirection parameter to either of the following, as applicable: xlNext (SearchDirection:=xlNext): To search for the next match. xlPrevious (SearchDirection:=xlPrevious): To search for the previous match.


1 Answers

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.

like image 104
AndASM Avatar answered Sep 29 '22 09:09

AndASM