Home > English site > Articles > Register UDFs > VBA code

Registering a User Defined Function with Excel

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