Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Register UDFs > Class Module

Registering a User Defined Function with Excel

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:

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
            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
        'errors will be raised by the property procedures
    End If

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

    '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

    If IsError(vRes) Then
        vRes = "Failed to Register:" & DllName & " " & DllProc & " " & FunText
        If VERBOSE Then
            MsgBox vRes
            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
End Sub