Generated code

The Visual Basic 6 project that is generated contains two class files:

• ExcelWrapper, a utility class.
• The add-in class, which exposes all the add-in's functions as COM methods.

ExcelWrapper class

The ExcelWrapper does the work of controlling an instance of Excel or Xll Host and invoking the XLL add-in functions.

A different version of the ExcelWrapper class will be generated, depending on the implementation model selected.

Model	Code	Description
Excel Application	EA	Creates an instance of Excel and uses the Excel.Application.Run method to invoke add-in functions..
XLL Host	XD	Use the XLL Host COM library to host XLL add-in functions. Does not use Excel and does not require that Excel be present on the computer.

The class contains initialization and termination code, which manages the Excel application instance or XLL Host instance. It also contains two important methods.

Method	Description
CallExcel(FnName, [Arguments...])	Calls Excel.Application.Run (or XllDriver.CallAddinFunction), passing any arguments provided.
CallExcelWithError(FnName, ComErrorOnXlError, ComErrorStringPrefix, [Arguments...])	Calls Excel.Application.Run (or XllDriver.CallAddinFunction), passing any arguments provided. If the add-in function returns an error type, and ComErrorOnXlError is set to True, then an error is raised. If the add-in function returns a string that begins with ComErrorStringPrefix, then an error is raised.

Note: the generated code for the ExcelWrapper class is identical in all generated COM projects that use a particular implementation model .

Add-in class

The name of the add-in class will be the one you selected in the Generator dialog. It's full name will be <ProjectName>.<ClassName>, e.g. MyAddinLib.MyAddin. The add-in class contains methods for each of the add-in functions in the XLL being wrapped.

The object contains an ExcelWrapper object, to which most of the work is delegated. When an object is created, it initializes the ExcelWrapper object (which in turn creates a new instance of Excel). When the object is destroyed, so is the ExcelWrapper object (which in turn closes the Excel instance).

Methods

Each COM method is implemented with a single line of code, which passes all its arguments to ExcelWrapper.CallExcel() or ExcelWrapper.CallExcelWithError(). For example, the wrapper for NORMSDIST2 function from the Tutorial1 XLL is generated as follows:

Public Function NORMSDIST2(ByVal Z As Double) As Double
    NORMSDIST2 = impl.CallExcel("NORMSDIST2", Z)
End Function

If necessary, some function names or argument names may be changed, because they are Visual Basic keywords. For example, the Array argument of MAXCOORD (from the Tutorial1 XLL) is changed as follows:

Public Function MAXCOORD(ByVal Array_ As Variant, ByVal XCoord As Boolean) As Variant
    MAXCOORD = impl.CallExcel("MAXCOORD", Array_, XCoord)
End Function

Data types

The table below shows how the Excel data type of each argument and the return value is transformed to a COM argument type in the COM method.

Excel type	COM type
boolean	Boolean
double	Double
long	Long
USHORT	Long
short	Integer
string	String
COper	Variant
CXlOper	Variant
CXlArray	Variant
Arrays of any type	Variant
Group	Variant
Column	Variant

Error handling

When an add-in function fails, it often returns either an error type or an error string. The COM methods can detect these cases and throw an error.

Error handling for all add-in functions can be added by setting the appropriate fields in the generator user interface.

Field	Setting	Behavior
Excel error type handler	Return a variant containing an error type	If the add-in function returns an error type, the COM method simply returns it as a value.
	Raise an error	If the add-in function returns an error type, the COM method will throw an error, with a description such as "#N/A", "#DIV/0!", "NAME?", etc.
String error prefix	(Blank)	If the add-in function returns a string, the COM method simply returns it as a value.
	Non-blank, e.g. "#Error:"	If the add-in function returns a string starting with the supplied prefix, the COM method will throw an error, with a description containing the remainder of the string. Thus if "#Error: Expected an array for argument 1" is returned, the COM method will throw an error with description "Expected an array for argument 1".

If neither field is set, then ExcelWrapper.CallExcel() will be used. If either field is set, then ExcelWrapper.CallExcelWithError() will be used.

Note: the error handling behavior can be also set for each function, by setting the function's extended attributes "comerroronxlerror" and "comerrorstringprefix" in the XLL+ Function Wizard.

GetAddins event handler

The add-in class should return a list of all the XLL files which are required by the COM library. These should be returned as a single-dimensional array of strings, each containing the full path and name name of an XLL add-in file.

Deploying the library

The default code generated for the GetAddins event handler expects the XLL add-in to be in the same directory as the COM wrapper library. You may decide to use an alternative method.

1. If you place the XLL elsewhere, then you should amend the string returned by the GetAddins event handler to point to the location of the XLL.
2. If you are using the EA implementation model and you register with Excel using the add-in using Tools - Addins, then you can return any path, as long as the file name is correct. The ExcelWrapper.XllIsLoaded() method will ignore the path, but will check that an XLL with the correct name has been correctly loaded by Excel.

Performance considerations using the 'Excel Application' implementation model

The add-in class opens a new invisible instance of Excel when it is initialized (via the ExcelWrapper object), and closes it when it is destroyed. It is therefore a good idea to keep the add-in object alive for as long as you will be using it.

Because each COM wrapper object contains its own instance of Excel, it may advisable to do the following:

1. Combine multiple add-ins into a single COM library.
2. Use a single global instance of the object in a calling application.

Performance considerations using the 'XLL Host' implementation model

The add-in class loads the wrapped XLL add-in(s) when it is initialized (via the ExcelWrapper object). In addition, if it is the last ExcelWrapper object alive when it is destroyed, the XLL Host instance will be terminated when it is destroyed. It is therefore a good idea to keep the add-in object alive for as long as you will be using it.

Because all COM wrapper objects share a common instance of the XLL Host, there is no requirement to combine multiple add-ins into a single COM library. However, because there is a cost to loading the wrapped XLL add-in during initialization, it is sensible to use a single global instance of the wrapper object in a calling application.

See Also

XLL+ COM wrapper generator | Using the wrapper generator | Calling the COM methods