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

File crashes Excel!!

Save my workbook!
The best tool for salvaging problematic workbooks.

Trainings

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

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
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):

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