如何获取Excel第三方加载项中运行的UserForm标题？

没法直接访问第三方加载项的内部代码？没关系，咱们可以绕开这个限制，通过Windows API + VBA的组合方案，从系统窗口层面直接捕获当前显示的UserForm标题，具体实现步骤如下：

核心思路 #

Excel的UserForm本质就是Windows系统窗口，第三方加载项的UserForm也不例外。我们可以通过API枚举所有属于当前Excel进程的窗口，筛选出UserForm对应的专属窗口类（ThunderDFrame对应32位Excel，ThunderDFrame64对应64位Excel），然后提取它的标题文本，最后写入你指定的目标Excel文件。

完整VBA代码实现 #

把这段代码放到你用来捕获标题的Excel文件的标准模块中：

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As LongPtr, ByVal lpString As String, ByVal cch As Long) As Long
    Declare PtrSafe Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As LongPtr) As Long
    Declare PtrSafe Function EnumWindows Lib "user32" (ByVal lpEnumFunc As LongPtr, ByVal lParam As LongPtr) As Boolean
    Declare PtrSafe Function GetParent Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
    Declare PtrSafe Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Declare PtrSafe Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As LongPtr, lpdwProcessId As Long) As Long
#Else
    Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" (ByVal hwnd As Long, ByVal lpString As String, ByVal cch As Long) As Long
    Declare Function GetWindowTextLength Lib "user32" Alias "GetWindowTextLengthA" (ByVal hwnd As Long) As Long
    Declare Function EnumWindows Lib "user32" (ByVal lpEnumFunc As Long, ByVal lParam As Long) As Boolean
    Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long
    Declare Function GetClassName Lib "user32" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwProcessId As Long) As Long
#End If

Private currentExcelPID As Long
Private targetWorkbook As Workbook
Private userFormTitles As Collection

Sub CaptureUserFormTitles()
    ' 设置目标工作簿（替换成你的目标文件名，确保文件已打开，或用Workbooks.Open指定路径）
    Set targetWorkbook = Workbooks("目标文件.xlsx")
    Set userFormTitles = New Collection
    
    ' 获取当前Excel进程ID
    currentExcelPID = Application.HinstancePtr
    
    ' 枚举所有系统窗口
    EnumWindows AddressOf EnumWindowsProc, 0
    
    ' 将捕获到的标题写入目标工作簿的Sheet1
    Dim i As Integer
    For i = 1 To userFormTitles.Count
        targetWorkbook.Sheets("Sheet1").Cells(i, 1).Value = userFormTitles(i)
    Next i
    
    MsgBox "已成功捕获" & userFormTitles.Count & "个UserForm标题！", vbInformation
End Sub

#If VBA7 Then
    Private Function EnumWindowsProc(ByVal hwnd As LongPtr, ByVal lParam As LongPtr) As Boolean
#Else
    Private Function EnumWindowsProc(ByVal hwnd As Long, ByVal lParam As Long) As Boolean
#End If
    Dim className As String * 256
    Dim pid As Long
    Dim titleLength As Long
    Dim title As String
    
    ' 只处理无父窗口的顶层窗口（UserForm属于顶层窗口）
    If GetParent(hwnd) = 0 Then
        ' 获取窗口类名
        GetClassName hwnd, className, 256
        className = Left(className, InStr(className, vbNullChar) - 1)
        
        ' 判断是否是Excel的UserForm专属窗口类
        If className = "ThunderDFrame" Or className = "ThunderDFrame64" Then
            ' 获取窗口所属进程ID
            GetWindowThreadProcessId hwnd, pid
            
            ' 确认窗口属于当前Excel进程
            If pid = currentExcelPID Then
                ' 获取窗口标题长度并提取标题
                titleLength = GetWindowTextLength(hwnd)
                If titleLength > 0 Then
                    title = String(titleLength + 1, vbNullChar)
                    GetWindowText hwnd, title, titleLength + 1
                    title = Left(title, titleLength)
                    
                    ' 加入集合并自动去重
                    On Error Resume Next
                    userFormTitles.Add title, Key:=title
                    On Error GoTo 0
                End If
            End If
        End If
    End If
    
    EnumWindowsProc = True ' 继续枚举下一个窗口
End Function

使用说明 #

1. 打开用来捕获标题的Excel文件，按Alt+F11打开VBA编辑器；
2. 插入一个标准模块，把上面的代码粘贴进去；
3. 修改代码中的"目标文件.xlsx"为你要写入标题的Excel文件名（如果文件未打开，可替换为Workbooks.Open("C:\你的文件路径\目标文件.xlsx")）；
4. 运行CaptureUserFormTitles宏，此时如果第三方加载项的UserForm正在显示，它的标题就会被写入目标文件的Sheet1中。

注意事项 #

• 代码兼容32位和64位Excel，通过条件编译处理了API声明差异；
• 若多个UserForm同时显示，所有标题都会被捕获并自动去重；
• 确保Excel的宏安全设置允许运行VBA代码（可在Excel选项中启用宏）。

内容的提问来源于stack exchange，提问作者vishnuraj1175