The ApiTest sample demonstrates how you can call the functions of an XLL from other environments, including .NET, VBA and C++.

This sample contains several components:

ApiTest
XLL+ add-in project. It contains functions which use a wide variety of return types and argument types.
ApiTestLib
XLL wrapper project. It contains wrapper classes generated from the ApiTest add-in.
CsClient
C# console application which uses the wrapper classes, via .NET assemblies.
CppClient
C++ console application which uses the wrapper classes, via COM interfaces.
VbaClient
Excel spreadsheet which uses the wrapper classes, via COM interfaces and Visual Basic for Applications.

Open the solution file ApiTest.sln to see and use the four Visual Studio projects.

ApiTest: XLL+ Add-in

This add-in contains a variety of add-in functions which serve to test the various features of the XLL Wrapper Generator.

The functions' names (e.g. ScalarAdd, UseCallback) reflect their purpose.

ApiTestLib: XLL Wrapper Library

This wrapper library was generated from a model file exported from the ApiTest add-in, ApiTest.model.xml.

A number of changes were made to the information file ApiTest.wrapper.xml.

Namespace

The namespace used for the wrapper classes was changed to Planatech.Demos, by changing the Namespace attribute of the Client elements:

CopyXML
<Clients>
  <Client Type="COM"
          Namespace="Planatech.Demos"
          Class="ApiTest"
          ClassGuid="2d42a1e1-3659-4869-81fc-36e7ad4b0889"
          Interface="IApiTest"
          InterfaceGuid="fe36ae91-b9e2-4794-8d47-5a204e3e06ea"/>
  <Client .../>
</Clients>

Excluding a function

The version information function was excluded from the list of exported functions:

CopyXML
<Function ExportedName="ApiTest_VersionInfo" Wrap="Never"/>

Function return types

A return type was specified for functions which, within Excel, return a variable type - they may return an error value or an error string if the functions fail for some reason.

Specifying the return type has two effects:

  1. The wrapper function's signature specifies a useful type, rather than object.
  2. if the add-in function fails the wrapped function will throw an exception with an appropriate error message.
CopyXML
<Function ExportedName="ScalarAdd" ReturnType="double"/>
<Function ExportedName="StringAdd" ReturnType="string"/>
<Function ExportedName="VectorAdd" ReturnType="double[]"/>
<Function ExportedName="MatrixAdd" ReturnType="double[,]"/>

In each case, the wrapper function's code handles the appropriate conversion and validation:

CopyC#
/// <summary>
/// Adds two vectors
/// </summary>
/// <param name="A">is the first vector</param>
/// <param name="B">is the second vector</param>
/// <exception cref="XllWrapperException">Will be thrown if any errors 
/// occur in the call to Excel,
/// or if the add-in function returns an error value.</exception>
[Description("Adds two vectors")]
[DispId(1005)]
public double[] VectorAdd(double[] A, double[] B)
{
    CheckEngine();
    object res__ = Engine.CallExcelWithErrorCheck("VectorAdd", true, 
        errorPrefixes, A, B);
    return Unwrap(res__, typeof(double[])) as double[];
}

Dates

Excel add-in functions treat dates as numbers. It is useful for wrapper functions to know that they are in fact dates. The IsDate attribute of the Argument element can be used for this purpose:

CopyXML
<Function ExportedName="StartOfMonth" ReturnType="DateTime">
  <Argument Name="dt" IsDate="1" WrappedName="Date"/>
</Function>
CopyC#
/// <summary>
/// Returns the start of the month for the specified date
/// </summary>
/// <param name="Date">is a numeric date</param>
/// <exception cref="XllWrapperException">Will be thrown if any errors occur in the call to Excel,
/// or if the add-in function returns an error value.</exception>
[Description("Returns the start of the month for the specified date")]
[DispId(1010)]
public DateTime StartOfMonth(DateTime Date)
{
    CheckEngine();
    double Date_xldate = DateToExcel(Date);
    object res__ = Engine.CallExcelWithErrorCheck("StartOfMonth", true, errorPrefixes, Date_xldate);
    return (DateTime)Unwrap(res__, typeof(DateTime));
}

Names

The WrappedName attribute of the Argument element was used to change the argument names visible in the wrapper function, perhaps to follow standards. Thus X in Excel becomes dtX in C#.

CopyXML
<Function ExportedName="MinDates" ReturnType="DateTime[]">
  <Argument Name="X" IsDate="1" WrappedName="dtX"/>
  <Argument Name="Y" IsDate="1" WrappedName="dtY"/>
</Function>
CopyC#
public DateTime[] MinDates(DateTime[] dtX, DateTime[] dtY)
{
    CheckEngine();
    double[] dtX_xldate = DatesToExcel1d(dtX);
    double[] dtY_xldate = DatesToExcel1d(dtY);
    object res__ = Engine.CallExcelWithErrorCheck("MinDates", true, errorPrefixes, dtX_xldate, dtY_xldate);
    return Unwrap(res__, typeof(DateTime[])) as DateTime[];
}

Name changes using the WrappedName attribute can also be useful for avoiding reserved words, and other naming conflicts. The WrappedName attribute can also be applied to function names.

CsClient: C# console application

The code for the CsClient test application can be found in the CsClient sub-directory.

References

The application has references to two assemblies: the wrapper library ApiTestLib.dll and the XLL+ wrapper support classes XllPlus.ComWrappers.Runtime.dll.

Note:

In each case the reference is to the assembly (.dll) file; the type library file (.tlb) is not used by .NET clients.

A copy of the XLL+ wrapper support classes' assembly, XllPlus.ComWrappers.Runtime.dll, can be found in the same directory as the wrapper assembly. It is placed there by Visual Studio.

Initializing the engine

The engine initalization and termination takes place in TestContext.RunWithExcel(). The engine is initialized using Create. and terminated when Dispose() is called at the end of the using block. Alternatively, Destroy could be called to terminate the engine.

CopyC#
public void RunWithExcel(string[] args)
{
    using (ExcelWrapperEngine engine = new ExcelWrapperEngine())
    {
        engine.Create();
        engine.LoadXll(GetXllPath());
        TestRunner run = new TestRunner(args);
        run.Run(engine);
    }
}
private string GetXllPath()
{
    return Path.Combine(Path.GetDirectoryName(typeof(Program).Assembly.Location),
                "ApiTest.xll");
}

GetXllPath() uses a standard technique to find the XLL file - it looks in the same directory as the wrapper assembly.

.NET wrapper classes

Immediately after a wrapper object is created, its Engine property is set.

CopyC#
private void TestScalarAdd(IWrapperEngine engine)
{
    try
    {
        using (ApiTest api = new ApiTest())
        {
            api.Engine = engine;
            Console.Out.WriteLine("ScalarAdd(1.2, 2.3)={0}", api.ScalarAdd(1.2, 2.3));
        }
    }
    catch (XllWrapperException ex)
    {
        Console.Out.WriteLine(ex.ToString());
    }
}

Wrapper classes are lightweight, and they support IDisposable, so it is convenient to put them within a using block. The wrapper object is disposed automatically at the end of the using block.

CppClient: C++ console application using COM

The code for the CppClient test application can be found in the CppClient sub-directory.

References

The application contains two references to COM modules: one for the wrapper library, and one for the XLL+ wrapper support classes. In each case the reference is to the type library (.tlb) file, not to the assembly (.dll) file.

CopyC++
#import "..\..\..\bin\XllPlus.ComWrappers.Runtime.tlb"
#import "..\ApiTestLib\bin\Release\ApiTestLib.tlb"

Initializing the engine

The engine initialization takes place in TestRunner::Run().

The engine is terminated using Destroy.

Note:

As usual with COM objects, the pointers reference the primary interface of the object, i.e. IExcelWrapperEngine rather than the class.

Special consideration is also needed for the value used for the wrapper class's Engine property. The IExcelWrapperEngine interface is cast to a to a IWrapperEngine interface explicitly. The reference is dropped when it is no longer required.

CopyC++
void Run()
{
    try
    {
        IExcelWrapperEnginePtr engineInstance(__uuidof(ExcelWrapperEngine));
        engineInstance->Create();
        engineInstance->LoadXll(GetXllPath());
        IWrapperEnginePtr engine(engineInstance);

        // ... Use the engine ...

        engine.Detach();
        engineInstance->Destroy();
    }
    catch(_com_error e)
    {
        _tprintf(_T("%s\n"), (LPCTSTR)e.Description());
    }
}

COM wrapper classes

The wrapper class implements a default interface, IApiTest. As usual in C++ COM programming, pointers are for the default interface, not the object.

CopyC++
void TestScalarAdd(IWrapperEnginePtr& engine)
{
    try
    {
        ApiTestLib::IApiTestPtr api(__uuidof(ApiTestLib::ApiTest));
        api->Engine = engine;
        _tprintf(_T("ScalarAdd(1.2, 2.3)=%lf\n"), api->ScalarAdd(1.2, 2.3));
    }
    catch(_com_error e)
    {
        _tprintf(_T("%s\n"), (LPCTSTR)e.Description());
    }
}

Array arguments

Array arguments are represented as SAFEARRAY types in the COM code. The sample code uses a few array helper methods to ease the task of reading and writing COM arrays.

VbaClient: VBA module in Excel spreadsheet

VbaClient.xls can be found in the VbaClient sub-directory of the sample.

References

The Tools/References... menu was used to open the References dialog. Then the Browse... button was used to navigate to the directories containing each type library.

In each case the reference was made to the type library (.tlb file), not the assembly (.dll) file.

The XLL+ wrapper support classes' type library file, XllPlus.ComWrappers.Runtime.tlb, is in the bin sub-directory of the XLL+ installation (e.g. C:\Program Files\Planatech\XllPlus\6.0\VS9.0\bin\XllPlus.ComWrappers.Runtime.tlb).

The wrapper objects' type library is in the same directory as the wrapper assembly: ..\ApiTestLib\bin\release\ApiTestLib.tlb.

Initializing the engine

Because the wrapper library is being used from Excel VBA, the engine is initialized using Attach, passing the existing instance of Excel. When the engine is no longer required, Detach is called.

CopyVBA
Public Sub Run()
    Dim engine As New ExcelWrapperEngine
    engine.Attach Application
    engine.LoadXll GetXllPath

    ' ...


    engine.Detach
End Sub
Important Note:

You should not call Create from Excel VBA. This will create a new hidden instance of Excel instead of using the current instance.

The full path to the XLL file is calculated by GetXllPath:

CopyVBA
Private Function GetXllPath() As String
    GetXllPath = ThisWorkbook.Path & "\..\Debug\ApiTest.xll"
End Function

Wrapper classes

The VBA wrapper class ApiTestVba was used instead of the standard wrapper class, to deal with the special issues arising from the VBA implementation of COM.

CopyVBA
Private Sub TestScalarAdd(engine As IWrapperEngine)
    Err.Clear
    On Error Resume Next
    Dim api As New ApiTestVba
    Set api.Engine = engine
    If Err.Number <> 0 Then
        Debug.Print Err.Description
    Else
        Debug.Print "ScalarAdd(1.2, 2.3)";
        Dim result As Double
        result = api.ScalarAdd(1.2, 2.3)
        If Err.Number <> 0 Then
            Debug.Print " throws "; Err.Description
        Else
            Debug.Print "="; result
        End If
    End If
End Sub

See Also