在VBA监视列表中查找特定文本 [英] Find specific text in VBA watch list

问题描述

通过VBA监视列表，您可以逐行浏览宏，以监控变量.有没有一种方法可以扩展监视列表中包含的对象的所有属性，并在其中搜索特定的文本?监视列表中的一行可以复制到记事本或单词等中，但是似乎无法导出监视列表的全部内容或一次搜索所有行.

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.

我正在尝试确定Excel电子表格上对象的特定属性是什么.能够在监视列表中找到其中包含的文本，将大大加快我的搜索速度.

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.

推荐答案

在VBA(基本上是VB6)中最接近的是 TypeLib信息对象库.在此处和在其中有一些示例代码 对VB6 UDT进行自检.

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.

您可以创建一个函数，该函数扫描对象的成员以查找某些名称或值.调试时可以从即时窗口中调用此函数，并通过Debug.Print输出其结果.由于语言和环境的限制，您只能搜索公共对象，类型和接口的公共成员.

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.

出于我自己的好奇心，我创建了一个简单的示例.它仅执行浅层单级搜索，并且仅查看成员名称，而不查看值.您可能希望通读文档，以便可以查询成员的值(当它们是属性时)，并可以查看返回对象，集合或接口的成员的某种递归.

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.

要运行此代码，您需要在VBA编辑器中添加对TypeLib Information的引用.

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

然后我运行测试"子程序.当它停在Debug.Assert False行上时，我在即时"窗口中运行了FindMember子程序，得到以下结果. (您不必调试活动代码即可使用此功能，可以在普通代码中或不运行任何内容时从即时窗口中使用它.我只是这样做以确保在实时代码暂停时可以使用它).

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

从理论上讲，TypeLib信息对象库应该能够查看对象浏览器可以执行的任何操作.有趣的是，尝试在您自己的项目中定义一个私有子对象.您会看到对象浏览器无法查看它，但是可以查看您在模块和(公共)类中定义的公共成员.

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.

这篇关于在VBA监视列表中查找特定文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！