HOW TO: How can I open and close an XLL from Visual Basic for Applications?

HOW TO: How can I open and close an XLL from Visual Basic for Applications?

Question

I have an XLA and an XLL that work together. How can I open and close the XLL from the XLA?

Answer

1. To open an XLL from VBA code, use Application.RegisterXLL.
2. To close the XLL from VBA, use Application.ExecuteExcel4Macro to invoke the XLM4 macro UNREGISTER.
3. To ensure that all your add-in functions are removed from the Excel Formula Wizard, add a call to CXllApp::UnregisterFunctions() in your CXllApp::OnXllClose() event handler.

Example VBA

The Visual Basic for Applications code below shows how to open and close an XLL. Note that you should pass the XLL's entire path and name when opening it, but only the file name when you close it.

Option Explicit
Const xllName = "RegTest.xll"
Const filePath = "C:\Files\RegTest\Debug\" & xllName

Public Sub DoIt()
    On Error GoTo ErrorHandler
    Application.RegisterXLL filePath
    Exit Sub
ErrorHandler:
    Debug.Print Err.Number, Err.Description
End Sub

Public Sub UndoIt()
    On Error GoTo ErrorHandler
    Application.ExecuteExcel4Macro "UNREGISTER(""" & xllName & """)"
    Exit Sub
ErrorHandler:
    Debug.Print Err.Number, Err.Description
End Sub

Calling UnregisterFunctions()

The code below shows how to ensure that all the add-in functions registered by the XLL are removed from Excel's Formula Wizard. Add a call to CXllApp::UnregisterFunctions() after all other termination code has run.

void CRegTestApp::OnXllClose()
{
    // Delete menu
    m_menu.Destroy();

    // Ensure that all functions are removed from Excel's Formula Wizard
    UnregisterFunctions();
}

See also

FAQ #0029: How can I open an XLL from another XLL