XLL+ Class Library

Excel Emulators

Overview

Some XLLs make calls to Excel. XLL Host attempts to respond to these calls by simulating the behavior of Excel. Occasionally a call will be made that XLL Host is not programmed to understand; in these cases the developer can install an object that implements the IExcelEmulator interface and which returns the result that the XLL expects.

Excel API

Calls from XLLs to the Excel API are made via Excel4() and Excel4v(); the declarations of these functions are shown below.

int far _cdecl Excel4(int xlfn, LPXLOPER operRes, int count, ...);
int far pascal Excel4v(int xlfn, LPXLOPER operRes, int count, LPXLOPER far opers[]);

When an XLL add-in function is run under Excel, Calls to the API are routed via the Excel extension file XLCALL32.DLL, which in turn passes them back to Excel:

Under XLL Host, a different routing occurs. A special build of XLCALL32.DLL catches calls from the XLL to the Excel API. Each call is passed to the global instance of XllDriver, which attempts to handle it by using a set of Excel emulator objects. Each of these emulators is tried in turn, until one of them succeeds. The result is then returned to the XLL that invoked the API.

The XllDriver object contains a number of built-in emulators, which handle many of the more common built-in Excel functions and macros. However, an XLL may make use of Excel functions that are not handled by the standard set of emulators; in this case the developer can create and register their own emulator, which will handle the special case as required.

Example

The remainder of this topic discusses a problematic XLL, and the procedure used for resolving the problem. All the source code discussed can be found in the Samples\XllHost\XdTest1 subdirectory of the XLL+ installation directory.

The problem

The code generated by the XLL+ COM Wrapper Generator can be found in its original form in the sub-directory XdTest1Wrap1.

The XLL (XdTest1.xll) handles the OnXllOpenEx() event by showing a message box. If the user does not respond by pressing "OK", then the event fails, and the XLL is not opened.

The relevant code is shown below:

BOOL CXdTest1App::OnXllOpenEx()
{
    if (XlMessageBox("Are you sure wish to load this add-in?", XlMessageBoxTypeQuestion) != TRUE)
        return FALSE;
    return TRUE;
}

When an attempt is made to load the XLL, using XllDriver.LoadXll, an error occurs:

xlAutoOpen for 'C:\Source\Program Files\Planatech\XllPlus\Samples\
XllDriver\Samples\XllHost\XdTest1\Debug\XdTest1.xll' returned FALSE

Installing a trace emulator

Note: You can find all the source code for this step in the directory XdTest1Wrap2. You can open a test project along with the wrapper project by opening XdTest1Wrap2\TestApp\Group1.vbg

The first step to resolving the problem is to isolate the API call that is not being properly handled. Create a new class in VB6, named TraceEmulator, and add code to implement IExcelEmulator:

Option Explicit

Implements IExcelEmulator

Private Function IExcelEmulator_HandleExcel4(ByVal xlfn As Long, arguments() As Variant, resultCode As Long, resultValue As Variant) As Boolean
    Dim i As Long
    On Error Resume Next
    ' Print function number and name
    Debug.Print "[" & xlfn & "] "; XllDriver.GetFunctionName(xlfn) & "(";
    ' Print function arguments
    For i = LBound(arguments) To UBound(arguments)
        If i > LBound(arguments) Then Debug.Print ",";
        Debug.Print arguments(i);
    Next
    Debug.Print ")"
    ' Call is NOT handled - return False
    IExcelEmulator_HandleExcel4 = False
End Function

The function IExcelEmulator_HandleExcel4() prints out all the details of the call to the Immediate window. It returns False, to indicate that the call has not been handled. It makes use of XllDriver.GetFunctionName in order to convert the uninformative function number to a more useful name.

The next step is to install the emulator. The easiest place to do this is within the ExcelWrapper class that was generated by the COM Wrapper generator.

Public Sub InitXlls(ByVal xllList)
    If has_initialized Then Exit Sub   
    
    ' Start Host (if required)
    XllDriver.Initialize
    
    ' Load addins
    Dim xllPath
    If IsArray(xllList) Then
        For Each xllPath In xllList
            XllDriver.LoadXll xllPath, True
        Next
    End If
    has_initialized = True
End Sub

Add code to install the new emulator. This should be placed after XllDriver.Initialize, but before XllDriver.LoadXll, since it is during LoadXll that the problem is occuring.

Public Sub InitXlls(ByVal xllList)
    If has_initialized Then Exit Sub   
    
    ' Start Host (if required)
    XllDriver.Initialize
    
    ' Install emulator
    Dim cookie As Long, functionIDs() As Long
    cookie = XllDriver.InstallExcelEmulator(New TraceEmulator, True, functionIDs)
    
    ' Load addins
    Dim xllPath
    If IsArray(xllList) Then
        For Each xllPath In xllList
            XllDriver.LoadXll xllPath, True
        Next
    End If
    has_initialized = True
End Sub

Note that the second argument (topOfList) to InstallExcelEmulator is True: we want our emulator at the top of the list of emulators, so that we can observe every call to the API. If we place it at the end of the list, it will only catch calls that have not been handled by any of the built-in emulators.

The third argument (functionIDs) contains an empty array. This indicates that we want to trap every API call, regardless of which built-in Excel function is being called. If the argument is not empty, then only the functions whose IDs are passed will be trapped by the emulator - all others will be ignored.

Finally, notice that we retain the result of the call to InstallExcelEmulator in the variable cookie. We can use this later to uninstall the emulator if it is no longer wanted.

Running with a trace

The next time we attempt to load the XLL, we get a trace output in the VB6 Immediate window:

[186] xlfGetWorkspace( 37 )
[16393] xlGetName()
[16384] xlFree(C:\Source\Program Files\Planatech\XllPlus\Samples\XllHost\XdTest1Wrap2\..\XdTest1\Debug\XdTest1.xll)
[32909] xlcEcho(False)
[32886] xlcAlert(Are you sure wish to load this add-in?, 1 )

We can see that the problem is being caused by a call to xlcAlert during the XllOpen event. The next step is to write an emulator that handles this call and returns the result that the XLL is expecting.

Fixing the problem

Note: You can find all the source code for this step in the directory XdTest1Wrap3. You can open a test project along with the wrapper project by opening XdTest1Wrap3\TestApp\Group1.vbg.

Create a new VB6 class XdTest1Emulator and add the following code:

Option Explicit

Implements IExcelEmulator
Const xlcAlert = 32886

Private Function IExcelEmulator_HandleExcel4(ByVal xlfn As Long, arguments() As Variant, resultCode As Long, resultValue As Variant) As Boolean
    ' Check function ID and argument count
    If xlfn = xlcAlert And ((UBound(arguments) + 1 - LBound(arguments)) = 2) Then
        ' Only intercept if the arguments match
        If arguments(LBound(arguments) + 0) = "Are you sure wish to load this add-in?" _
        And arguments(LBound(arguments) + 1) = 1 Then
            ' Simulate the user clicking "OK"
            resultCode = 0                      ' xlretOK
            resultValue = 1                     ' TRUE
            IExcelEmulator_HandleExcel4 = True  ' Handled
            Exit Function
        End If
    End If
    ' Otherwise the SDK call is not handled
    IExcelEmulator_HandleExcel4 = False
End Function

This emulator handles only calls to xlcAlert, and in addition it ignores any calls except those with the exact arguments of the call that is causing the problem. In this one case, it returns True to indicate that the call has been handled, and simulates the results that the XLL is expecting - a return value of 1, indcating that the user pressed OK.

Now change the code in ExcelWrapper, to use the new emulator:

Const xlcAlert = 32886
			
Public Sub InitXlls(ByVal xllList)
    If has_initialized Then Exit Sub   
    
    ' Start Host (if required)
    XllDriver.Initialize
    
    ' Install emulator
    Dim cookie As Long, functionIDs() As Long
    ReDim functionIDs(0 To 0) As Long
    functionIDs(0) = xlcAlert
    cookie = XllDriver.InstallExcelEmulator(New XdTest1Emulator, False, functionIDs)
    
    ' Load addins
    Dim xllPath
    If IsArray(xllList) Then
        For Each xllPath In xllList
            XllDriver.LoadXll xllPath, True
        Next
    End If

    ' Uninstall emulator
    XllDriver.UninstallExcelEmulator cookie
    
    has_initialized = True
End Sub

There are three points of interest here:

  1. The functionIDs array now contains a single numeric ID, so that only calls to xlcAlert are sent to the emulator. This reduces the performance cost of having an additional emulator.
  2. The topOfList argument is now False; the emulator is only used if all the built-in emulators have failed to handle the call. This also reduces the performance cost of the emulator.
  3. The emulator is uninstalled after the XLL has been loaded. It's not needed, and therefore it is sensible to remove it. In other cases, the problem may occur during an add-in function, rather than during initialization, in which case the emulator should be left installed.

See Also

XLL Host | XLL Host Reference | XLL+ COM wrapper generated code

© 1997-2006 Planatech Solutions Ltd. All rights reserved.