From VBA to C Part 8 – Using Containers from the Standard Template Library

In the VBA world we make a lot of use of arrays and Collections. And we can also use the Dictionary object from the VBScript library by adding a reference to the VBScripting runtime. All of these are types of containers.

The C++ world has a much richer set of containers available from the Standard Template Library (STL) and the Boost Template Library. This post wil show how to use one of these containers – the STL set container, by creating a simple function for comparing 2 lists. You can compare this with the VBA version in the previous post.


The STL library makes use of the c++ Templates concept.

In VBA we tend to use Variants as arguments for functions when we don’t know what data type will be used at run-time. An example is writing a single QuickSort routine that accepts Variants rather than a set of QuickSort routines that handle the individual data types (string, double …).

The Templates approach is to write a function or object that takes a data type that is determined at compile time. So the C++ code calls up the Template code and tells it what type to use by enclosing the Type in angle brackets:
std::widget<long> Fred; would define a widget object from the standard C++ library with a data type of long.

STL Containers

Some of the STL Containers I use are:

  • pair – a tuple of 2 elements which can be of different types
  • vector – a dynamic 1-dimensional array with automatic resizing capablility for insert and delete. Has methods such as SORT
  • set – an indexed vector of unique elements. Has methods such as union, difference, intersection.
  • map – an associative array of key-value pairs. Similar to Collections and Dictionaries


Because Containers are templates and can contain a wide variety of data types its useful to define Iterators (generalised pointers/indexes) that allow you to move through them. And Containers all have some standard iterators like begin() which points to the first element in the container and end() that tells you you are one past the end rather than giving you the last item.


This is an array function that compares 2 lists and returns an array of True/False for each element in the first list (LookFor) showing if the element exists anywhere in the second list (LookIn).

I have defined 2 arguments, both of type value to simplify the code.
LookFor gives the list of items to look for in the LookIn list.

The chunk of code below does some generic error checking and gets the dimensions

RW12 nRLookFor=0,nRLookIn=0,nROut=0;    // number of rows
COL12 nCLookFor=0,nCLookIn=0,nCOut=0;    // number of columns

// default error result
// check that both parameters are present
if (LookFor->IsMissing() || LookIn->IsMissing()) return xloResult.Ret();

// get caller dims
if (!CXllApp::GetCallerDims(nROut,nCOut))
return CXlOper::RetError(xlerrNA);

// get input array dims
// more than 1 column -> error
if (nCLookFor!=1 || nCLookIn!=1) return xloResult.Ret();

Then I create the output array as the smaller of the number of calling rows and the the number of LookFor rows, and initialise to False:

// allocate output array & initialise to false
if (nRLookFor<nROut) nROut=nRLookFor;

Now I create a set container called InList and an iterator of the same datatype caled it.
The type of data that the set will contain is CXlConstCell.
That is a read-only xlOper data structure (think of it as a variant variable, but read-only).

// create set for LookIn
std::set<CXlConstCell> InList;
std::set<CXlConstCell>::iterator it;

Next loop through the LookIn list and insert each element into the set.
Remember that the set is a vector of unique keys and will ignore duplicates.

// populate the set
for (RW12 j=0;j<nRLookIn;j++) {

Now loop through the LookFor list checking if each item exists in LookFor.
If it does NOT exist then the Find method will return end(), which means one past the last item.
Remember that != means not equal

// check lookfor against inlist
for (RW12 j=0;j<nROut;j++) {
if (it!=InList.end()) xloResult.Cell(j,0)=true;

You don’t really need the iterator it in this case, you could do it all in one line of code:

if (InList.find(LookFor->Cell(j,0))!=InList.end()) xloResult.Cell(j,0)=true;

But I think its simpler to read with the iterator.

Finally return the output:

return xloResult.Ret();

Performance compared to VBA Dictionary UDF

In the previous post using the VBScript Dictionary was generally the fastest, but started running out of puff after about 500K rows.
For 1 Million rows the VBA Collection was fastest taking 6720 milliseconds for 50000 LookFor rows and 1 million LookIn rows.
And the VBA array function is limited to returning 64K rows.

The COMPARE.LISTS XLL UDF is considerably faster: 1923 milliseconds versus 6720 milliseconds (and of course its multi-threaded so there would be an even bigger speed advantage for multiple formulas).
The XLL array function is NOT limited to 64K rows. Comparing 1 million LookFor rows against 1 million LookIn rows takes just 2.5 seconds


Using C++ XLL’s you are not limited to just arrays, Collections and Dictionaries.
The STL and BOOST libraries can add significant power, performance and pre-built code to your projects.

This entry was posted in XLL. Bookmark the permalink.

5 Responses to From VBA to C Part 8 – Using Containers from the Standard Template Library

  1. kalx says:

    Why not return the index of the element in the set, or 0 if it is not in the set. Like this:

    // member.cpp – index of elements in a set
    #include “range.h”

    using namespace xll;

    static AddInX xai_range_member(
    FunctionX(XLL_LPOPERX, _T(“?xll_range_member”), _T(“RANGE.MEMBER”))
    .Arg(XLL_LPOPERX, _T(“Element”), _T(“is a range of elements. “))
    .Arg(XLL_LPOPERX, _T(“Set”), _T(“is a range specifying a set. “))
    .FunctionHelp(_T(“Returns an Element shaped range of the index of Element in Set. “))
    xll_range_member(LPOPERX pe, LPOPERX ps)
    #pragma XLLEXPORT
    static OPERX m(pe->rows(), pe->columns());

    for (xword i = 0; i begin(), ps->end(), (*pe)[i]) – ps->begin() + 1)%(ps->size() + 1);
    // Excel macro style
    // m[i] = ExcelX(xlfIferror, ExcelX(xlfMatch, (*pe)[i], *ps, OPERX(0)), OPERX(0));

    return &m;

  2. fastexcel says:

    Keith, looks like your code got mangled beyond recognition- but 2 issues –
    1: how long will it take to compare 1 million rows with 1 million rows? The target to beat is 2.5 seconds
    2: Returning an array of indices is a different function that I have not posted about yet (AMATCHES in FXL V3 Speedtools)

  3. WordPress is not very code friendly. See for the source. You can download the code, compile it on your machine, and compare results on a level playing field.
    It would be interesting to see how the STL version compares to the Excel4 macro version.

  4. wordpress1080 says:

    This is very interesting. I went to the site and it has only the C++ code.
    I have no previous experience on STL for Excel. Any website that you can suggest that may have an example or explanation?
    Many thanks!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s