Can I inspect an Excel function signature programmatically in VBA?

Question

Rather than access all the arguments explicitly by name, is there a way to get a list of function arguments programmatically from within the function?

So for this function signature:

Function doSomething(Arg1 as String, Arg2 as Range, Optional Arg3 as String):

is there, ideally, an object that contains argument names and their metadata (type, optional, default value, etc.)? E.g., the code Me.Arguments inside this function would produce a dictionary something like this:

{
  "Arg1": {
    "Type": String,
    "Optional": False,
    "Default": Nothing
  },
  "Arg2": {
    "Type": Range,
    "Optional": False,
    "Default": Nothing
  },
  "Arg1": {
    "Type": String,
    "Optional": True,
    "Default": Nothing
  }
}

Thanks.

Answer 1

Could it be possible to use this?

ThisWorkbook.VBProject.VBComponents("[Your_Code_Module_Name]").CodeModule

... and get the method signature and parameters out of there? Something like in the following example (just an example).

For you probably only the vbext_ProcKind.vbext_pk_Proc = 0 will be usefull, but in the example are all the proc-kinds available.

Standard Module 'Module1':

' Add referemce to Microsoft Scripting Runtime (Scripting.Dictionary)

Sub main()
    Call doSomething("hello", Nothing)
End Sub

' the code Me.Arguments inside this function would produce a dictionary
Function doSomething(Arg1 As String, _
    Arg2 As Range, Optional Arg3 As Long = 123456789)

    Dim thisCodeArguments As Scripting.Dictionary
    Dim thisCodeModule As Variant
    Set thisCodeModule = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

    With New ThisCode
        Set thisCodeArguments = .Arguments(thisCodeModule, "doSomething", 0)  ' 0 = VBIDE.vbext_ProcKind.vbext_pk_Proc
        Set thisCodeArguments = .Arguments(thisCodeModule, "someProperty", 3) ' 3 = VBIDE.vbext_ProcKind.vbext_pk_Get
        Set thisCodeArguments = .Arguments(thisCodeModule, "someProperty", 1) ' 1 = VBIDE.vbext_ProcKind.vbext_pk_Let
        Set thisCodeArguments = .Arguments(thisCodeModule, "someProperty", 2) ' 2 = VBIDE.vbext_ProcKind.vbext_pk_Set
    End With

End Function

Public Property Get someProperty() As Variant

End Property

Public Property Let someProperty(ByVal vNewValue As Variant)

End Property

Public Property Set someProperty(ByVal vNewValue As Variant)

End Property

Class Module 'ThisCode':

Public Function Arguments( _
    targetCodeModule As Variant, _
    procedureName As String, _
    vbextProcKind As Integer) _
    As Scripting.Dictionary

    Dim startLine As Long
    Dim countLines As Long
    Dim code As String
    Dim leftParentheses As Long
    Dim rightParentheses As Long
    Dim argumentsText As String
    Dim argumentsArray() As String
    Dim argumentParts() As String
    Dim argumentName As String

    Set Arguments = New Scripting.Dictionary

    With targetCodeModule
        startLine = .ProcStartLine(procedureName, vbextProcKind)
        countLines = .ProcCountLines(procedureName, vbextProcKind)
        code = .Lines(startLine, countLines)
    End With

    leftParentheses = InStr(code, "(")
    If leftParentheses > 0 Then
        rightParentheses = InStr(leftParentheses + 1, code, ")")
    Else
        Err.Raise 123, , "No left parentheses found" ' TODO: error number
    End If

    If rightParentheses > 0 Then
        argumentsText = Trim(Mid(code, leftParentheses + 1, _
                                rightParentheses - leftParentheses - 1))
    Else
        Err.Raise 456, , "No right parentheses found" ' TODO: error number
    End If

    If Len(argumentsText) = 0 Then Exit Function
    argumentsText = Replace(argumentsText, "_", "")
    argumentsText = Replace(argumentsText, vbCrLf, "")

    argumentsArray = Split(argumentsText, ",")

    Dim i As Long
    Dim j As Long
    Dim argumentInfo As Argument
    Dim argumentArray() As String

    For i = LBound(argumentsArray) To UBound(argumentsArray)

        Set argumentInfo = New Argument
        Set argumentInfo.DefaultValue = Nothing
        argumentInfo.IsOptional = False
        argumentInfo.TypeName = ""

        argumentParts = Split(argumentsArray(i))

        For j = LBound(argumentParts) To UBound(argumentParts)
            If Len(Trim(argumentParts(j))) = 0 Then GoTo continue
            If Trim(argumentParts(j)) = "Optional" Then
                argumentInfo.IsOptional = True
                argumentName = Trim(argumentParts(j + 1))
            ElseIf Trim(argumentParts(j)) = "As" Then
                argumentName = Trim(argumentParts(j - 1))
                argumentInfo.TypeName = Trim(argumentParts(j + 1))
            ElseIf Trim(argumentParts(j)) = "=" Then
                argumentInfo.DefaultValue = CVar(argumentParts(j + 1))
            End If
continue:
        Next j

        Arguments.Add argumentName, argumentInfo
    Next i

End Function

Class Module 'Argument':

Public TypeName As String
Public IsOptional As Boolean
Public DefaultValue As Variant 

Dictionary:

Answer 2

You might consider

• using Application.Caller to get a reference to the cell containing the formula, then use that cell's .Formula property to get the formula as text.
• You can then parse the formula to get your parameters as text.

I did this when I wanted to pass a 3D range as a parameter to a UDF (so I could make a suite of functions like COUNTIF and SUMIF that would work on a 3D range like Sheet1:Sheet99!$A$1:$A$1000). I found that the UDF would be triggered when a value in the 3D range was changed--but the UDF would have a runtime error as soon as I needed to do anything with the Variant that received the 3D range in the UDF. My workaround was as discussed in the first paragraph--get the formula and parse it to get the 3D range as text.

I tried inserting the code as a block, but was stymied by the formatting requirements. Here is the workbook that uses it on my SkyDrive

Answer 3

Great question!

I don't think so .... the closest I have come is using this workaround to pre-populate the FunctionWizard with a formula in the ActiveCell (the code below uses the first available blank cell looking up) to call up the Functions Dialog with NPV.

I have tried passing arguments to the same dialog without success.

Sub Kludge()
Dim rng1 As Range
Set rng1 = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
With rng1
Application.Goto rng1
.Value = "=NPV(10%,-10,5,5,5)"
c = Application.Dialogs(xlDialogFunctionWizard).Show 
.ClearContents
End With

Answer 4

You can get the Typestring for registered functions using Application.RegisteredFunctions. The typestring gives you the datatype for each argument and whether the function is multi-threaded and/or volatile.

But it only works for XLL-registered functions, not VBA or Automation functions, and you also have to do some trickery to match up the name of the function with the typestring.

See my blog post