Registering a User Defined Function with Excel

Pages in this article

Class module

The main code has been written in a class module called CUdfHelper.

This class contains almost all code you need to set things up. Internally, there is a number of tricks involved in getting things working reliably:

Write all argument data for the REGISTER function into Excel's hidden name space
Execute the REGISTER function using ExecuteExcel4Macro
This creates a new hidden name, with the name of the function and a pointer to the dll that has been used. To ensure nothing goes wrong, this pointer is set to zero
By declaring the UDF functions as type private, you avoid them from showing up twice in the function wizard.

Relevant code:

Function RegisterFunction() As Boolean
' Purpose   : This procedure registers the user defined function
' Arguments : none
' Results   : True is the function was registered successfully
'
' Notes     : You must FIRST assign the properties
' ---------------------------------------------------------------------
' Date        Developer                   Action
' 2006-02-23 Jurgen Volkerink            Created

    Dim i%, sCmd$, vRes

    If VERBOSE Then
        'Check we've got enough data
        Select Case True
        Case Len(m_uArgs.sDllName) = 0
            MsgBox "DLLname not specified", vbExclamation
            Exit Function
        Case Len(m_uArgs.sDllProc) = 0
            MsgBox "DLLproc not specified", vbExclamation
        Case ProcInUse
            Select Case MsgBox(DllName & " " & DllProc & " already registered." & _
                    vbLf & "Delete existing registration?", vbOKCancel)
            Case vbOK
                UnregisterFunction
            Case vbCancel
                Exit Function
            End Select
        Case Len(m_uArgs.sFunText) = 0
            MsgBox "FunText not specified", vbExclamation
            Exit Function
        Case Len(m_uArgs.vCatName) = 0
            MsgBox "CatName not specified", vbExclamation
            Exit Function
        End Select
    Else
        'errors will be raised by the property procedures
    End If

    'Clear existing names
    DelArgumentNames
    'Define names for each argument needed by the Register function
    SetArgumentNames

    'Create the command string
    For i = 1 To 10 + NumArgs: sCmd = sCmd & "," & NAMEID & i: Next
    sCmd = "REGISTER(" & Mid(sCmd, 2) & ")"

    'Execute the command
    vRes = Application.ExecuteExcel4Macro(sCmd)

    'Block access to the DLL memory address
    'Assign 0 to the Function namespace!!!!
    SetGlobalName Me.FunText, 0

    'Unload arguments from namespace
    DelArgumentNames

    If IsError(vRes) Then
        vRes = "Failed to Register:" & DllName & " " & DllProc & " " & FunText
        If VERBOSE Then
            MsgBox vRes
        Else
            Err.Raise 5, , vRes
        End If
    End If

End Function

And the routines that handle creating and deleting of the hidden names:

Private Function SetGlobalName(sName As String, Optional ByVal vValue As Variant) As Variant
'defines a global name to refer to a value.
'if vValue is omitted the name 'sName' is deleted.

    Dim sCmd$
    Select Case True
    Case IsArray(vValue)
        Err.Raise 16, , "SetGlobalName: Arrays s/b assigned as string like '{1,2,3}'"
    Case IsMissing(vValue)
        sCmd = "SET.NAME(" & Q & sName & Q & ")"
    Case IsEmpty(vValue)
        sCmd = "SET.NAME(" & Q & sName & QCQ & Q & ")"
    Case TypeName(vValue) = "String"
        sCmd = "SET.NAME(" & Q & sName & QCQ & vValue & Q & ")"
    Case Else
        'Int'l: The value must use a . as decimal separator
        vValue = Application.Substitute(CStr(vValue), Application.International(xlDecimalSeparator), ".")
        sCmd = "SET.NAME(" & Q & sName & QC & vValue & ")"
    End Select
    SetGlobalName = Application.ExecuteExcel4Macro(sCmd)
End Function

Private Sub DelArgumentNames()
'Deletes the argument "names" from Excel's (hidden) global namespace
    Dim i%
    For i = 1 To 30
        SetGlobalName NAMEID & i
    Next
End Sub