Registering a User Defined Function with Excel

Pages in this article

1. Introduction
2. How does it work?
3. Setting things up
4. Class Module
5. VBA code
6. Put to Use
7. Wrap Up

Unregistering

This again needs a couple of tricks not written in any book (but figured out by KeepItCool somehow):

• unregister, reregister as COMMAND in category "CUSTOMIZING"
• unregister, reregister as NONE
• unregister and block the pointer to the dll (as written on the previous page).

Code:

Function UnregisterFunction() As Boolean
' Purpose   : This procedure unregisters the user defined function
' Arguments : The Properties DLLName and DLLproc must be set
' Results   : True is the function was unregistered successfully
'
' Caveat    : Due to a bug in the UNREGISTER() xlm command
'             the functions will remain visible in FunctionWizard.
'
' ---------------------------------------------------------------------
' Date        Developer                   Action
' 2006-02-23  Jurgen Volkerink            Created

    Dim i%, sCmd$, vRes

    Select Case True
    Case Len(Me.DllName) = 0
        MsgBox "DllName not specified"
    Case Len(Me.DllProc) = 0
        MsgBox "DllProc not specified"
    Case ProcInUse
        'unblock
        SetGlobalName FunText
        SetArgumentNames

        'unregister
        sCmd = "UNREGISTER(REGISTER.ID(UDFH1,UDFH2))"
        vRes = Application.ExecuteExcel4Macro(sCmd)
        'reregister as a Command in category "Customizing"
        sCmd = "REGISTER(UDFH1,UDFH2,UDFH3,UDFH4,,2,""Customizing"")"
        vRes = Application.ExecuteExcel4Macro(sCmd)
        'unregister
        sCmd = "UNREGISTER(REGISTER.ID(UDFH1,UDFH2))"
        vRes = Application.ExecuteExcel4Macro(sCmd)
        'reregister as a NOTXLM
        sCmd = "REGISTER(UDFH1,UDFH2,UDFH3,UDFH4,,0)"
        vRes = Application.ExecuteExcel4Macro(sCmd)
        'unregister
        sCmd = "UNREGISTER(REGISTER.ID(UDFH1,UDFH2))"
        vRes = Application.ExecuteExcel4Macro(sCmd)

        'block any CALLS to the pointer
        SetGlobalName FunText, 0
        DelArgumentNames
    Case Else
        UnregisterFunction = True
    End Select
End Function