To understand extended types more fully, let us create a new extended type, an ISO currency code.

Converter class

What we want the converter class to do is:

1. Check that the input is a valid ISO currency code.
2. Set the target value to be the upper-case ISO code.

You can find all the code described below in the ExtendedTypes sample, under the XLL+ Samples directory.

The header file

The first step is to create a new header file, CurrencyConverter.h, and add the following two lines:

#pragma once
#include <xllplus.h>

The header file should be saved in a directory available to the compiler: either the local directory for the current project, or a directory on the INCLUDE list.

Class declaration

Next, derive the class from CXlUserConverterBase and select the appropriate template arguments. The input and output are both strings, so we can use CString for both:

class CurrencyConverter : public CXlUserConverterBase<CString, CString>
{
private:
    typedef CString ElemType;
    typedef CString XlNativeType;
};

The typedef statements are not strictly necessary, but they make it easier to copy code from existing classes.

ConvertFromExcel method

The first and most important method is ConvertFromExcel. We need to do 2 things:

1. Validate the input and return false if validation fails.
2. Put the converted result into outerValue and return true, if validation succeeds.

The code looks like this:

virtual bool ConvertFromExcel(const XlNativeType& xlValue, 
    ElemType& outerValue, const ParamsType& params) const
{
    // Look up the code, case-insensitive 
    const TCHAR* code = FindCode(xlValue);
    if (code == 0)
        return false;
    // Set the output to be the upper-case code
    outerValue = code;
    return true;
}

We will also need the implementation of FindCode. Here is a simplistic model:

class CurrencyConverter : public CXlUserConverterBase<CString, CString>
{
    ...
private:
    const TCHAR* FindCode(const TCHAR* input) const
    {
        static const TCHAR* codes[] = {
            _T("CAD"),
            _T("CHF"),
            _T("EUR"),
            _T("GBP"),
            _T("JPY"),
            _T("USD"),
            0
        };
        for (int i = 0; codes[i]; i++)
            if (_tcsicmp(input, codes[i]) == 0)
                return codes[i];
        return 0;
    }
};

Generic-text mappings

You'll notice that the character types are declared as TCHAR and the _T(...) macro surrounds all string literals.

This means that the code will build and run correctly under both Unicode and standard builds. It's a bit of extra work, but even if you don't use Unicode yourself, if your extension is likely to be used by other developers it's worth getting it right the first time.

If you know that Unicode is not and will not be an issue, then you can use char instead of TCHAR, _strcmpi instead of _tcsicmp, and omit the _T(...) macro.

Error messages

The final thing we need to do in the converter class is to support error messages, such as:

#ERROR: Expected a currency code for CcyCode
#ERROR: Expected 4 currency codes for PaymentCurrencies

We need to provide the fragments "currency code" and "currency codes" to the framework so that they can be inserted into error messages.

There are three ways to do this:

1. Implement GetTypeMessageId(). This technique is the most work, but supports multiple user languages. You need to put a string into the resource file, and return the string ID in GetTypeMessageId().

2. Implement CXlUserConverterBase_GetTypeNameW(). You should return the Unicode string L"currency code".

3. Implement CXlUserConverterBase_GetTypeName(). You should return the standard strings "currency code".

Let us implement the standard string version, as follows:

class CurrencyConverter : public CXlUserConverterBase<CString, CString>
{
    ...
    virtual std::string GetTypeName(const ParamsType& params) const 
    { 
        return "currency code"; 
    }
    ...
};

The whole class

Let's see the whole header file:

#pragma once
#include <xllplus.h> 
 
class CurrencyConverter : public CXlUserConverterBase<CString, CString>
{
private:
    typedef CString ElemType;
    typedef CString XlNativeType;
public:
    virtual bool ConvertFromExcel(const XlNativeType& xlValue, 
        ElemType& outerValue, const ParamsType& params) const
    {
        // Look up the code, case-insensitive 
        const TCHAR* code = FindCode(xlValue);
        if (code == 0)
            return false;
        // Set the output to be the upper-case code
        outerValue = code;
        return true;
    }
protected:
    virtual std::string GetTypeName(const ParamsType& params) const 
    { 
        return "currency code"; 
    }
private:
    const TCHAR* FindCode(const TCHAR* input) const
    {
        static const TCHAR* codes[] = {
            _T("CAD"),
            _T("CHF"),
            _T("EUR"),
            _T("GBP"),
            _T("JPY"),
            _T("USD"),
            0
        };
        for (int i = 0; codes[i]; i++)
            if (_tcsicmp(input, codes[i]) == 0)
                return codes[i];
        return 0;
    }
};

The extension file

Now we need to plug the extension into the XLL+ development tools, using an XLL+ extension file.

Tip: The easiest way to do this is to copy an existing file and then change it to suit your needs. That way you get the right format and schema declarations, which make it much simpler to edit.

The file looks like this:

<?xml version="1.0" encoding="utf-8"?> 
<Extensions xmlns="http://schemas.planatechsolutions.com/xlp/601/xt"> 
  <ScalarTypes> 
    <ScalarType name="Currency"  
                cppType="CString"  
                excelType="String"  
                converterType="CurrencyConverter"  
                optionalAllowed="true"  
                headerFiles="CurrencyConverter.h"> 
    </ScalarType> 
  </ScalarTypes> 
</Extensions>

See CXlUserConverterBase - XlNativeType for a list of the mappings between excelType in the definition file and XlNativeType in the class definition.

Save the extension file in an appropriate directory with a name such as Currency.xpe.

Using the extension

Register the extension by adding the extension file to the list in the XLL+ Options/Extensions page.

A new Currency type now appears in the argument type drop-down in the XLL+ Function Wizard. Code is generated for currency arguments, like the following:

// Input buffers
CString Ccy1;
// Validate and translate inputs
XlReadScalarEx(Ccy1_op, Ccy1, CurrencyConverter(), L"Ccy1");
// End of generated code

For vectors of currencies, code like this is generated:

// Input buffers 
std::vector<CString> Ccys;
// Validate and translate inputs
XlReadVectorEx(*Ccys_op, Ccys, CurrencyConverter(), L"Ccys", 
    XLA_TRUNC_ONEMPTY|XLA_TRUNC_ONBLANK);
// End of generated code

If a currency is not recognized, then an error message such as the following is returned:

#ERROR: Expected currency code for Ccy1

Examples

For more examples of extended types see the files in the extensions directory (and their matching header files in include\extensions).

Next: Parameters >>