如何通过Excel VBA获取子程序或函数参数列表提示框中的文本？

Great question! You’ve hit on a bit of a blind spot in Excel’s VBA API—while Application.MacroOptions lets you easily set the ArgumentDescriptions that show up in function/sub tooltips, there’s no built-in method to directly read those descriptions back out. But don’t worry, we can work around this by accessing the workbook’s underlying custom XML storage where Excel saves these details.

How It Works #

When you assign argument descriptions via MacroOptions, Excel stores this data in a custom XML part of the workbook, using the namespace http://schemas.microsoft.com/office/2006/metadata/macroArguments. We can write a small VBA routine to parse this XML and extract the descriptions for any macro.

Example VBA Code #

Here’s a reusable sub that retrieves argument descriptions for a specified macro (be sure to include the module name with the macro, e.g., Module1.MyCalculationFunction):

Sub RetrieveMacroArgumentDescriptions()
    Dim targetMacro As String
    Dim xmlPart As CustomXMLPart
    Dim macroNode As CustomXMLNode
    Dim argNode As CustomXMLNode
    Dim argCounter As Integer
    
    ' Set your target macro name (include module name!)
    targetMacro = "Module1.MyTestFunction"
    
    ' Loop through all custom XML parts in the workbook
    For Each xmlPart In ThisWorkbook.CustomXMLParts
        ' Check for the macro arguments namespace
        If xmlPart.NamespaceURI = "http://schemas.microsoft.com/office/2006/metadata/macroArguments" Then
            ' Find the node for our target macro
            Set macroNode = xmlPart.SelectSingleNode("//macro[@name='" & targetMacro & "']")
            
            If Not macroNode Is Nothing Then
                Debug.Print "Argument descriptions for: " & targetMacro
                argCounter = 1
                ' Loop through each argument's description
                For Each argNode In macroNode.ChildNodes
                    Debug.Print "  Argument " & argCounter & ": " & argNode.Text
                    argCounter = argCounter + 1
                Next argNode
                Exit Sub ' Exit once we find our macro
            End If
        End If
    Next xmlPart
    
    ' If no matching macro was found
    Debug.Print "No argument descriptions exist for: " & targetMacro
End Sub

Key Notes #

• Macro Naming: Always include the module name with the macro (e.g., Sheet1.MySub for a sub in a worksheet module, ThisWorkbook.MyFunction for one in the workbook module).
• Saved Workbooks: This only works with saved workbooks—Excel doesn’t write the XML parts to disk until you save the file.
• Add-Ins: If you’re looking for descriptions in an add-in, replace ThisWorkbook with Workbooks("YourAddIn.xlam") (adjust the filename as needed).

Why No Built-In Method? #

Microsoft hasn’t exposed a direct way to read these descriptions through the VBA API, which is a bit frustrating, but the XML workaround is reliable as long as you’re working with Excel 2007 or later (which introduced custom XML parts).

内容的提问来源于stack exchange，提问作者Giuseppe Alfio MArio Mendola