In the last post I described how to write functions that can input/output arrays, matrices and ranges. For the introduction to XLW series take a look at the first post. Today I am going to describe several useful utilities that are available in XLW. There are many more utilities that are available but they do not work from UDF and only work from macros. This is Excel limitation. I will address creating macros in one of the future posts.

The starting point of this post is the solution we set up previously. If you don’t have set it up you can download it.

1. Size of calling range

The calling range is a single cell when you input a scalar formula in Excel. However, when you use functions that output arrays or matrices, you need to input formula as an array formula. To do this type a formula as a regular scalar formula. Then select a range where you want array output to be (the formula should be in left-top corner or your selection). Then select the formula in the formula bar and press Ctrl+Shift+Enter. You will see curly braces appearing around your formula, this signifies that it is array formula. If you perform this operation with a simple scalar formula, then the same result will be copied to all output cells.

Sometimes you want to adjust your computations based on the size of output range that a user selected. The following snippet shows how you can accomplish this:

Add the following to mylib.cpp: 

1
2
3
4
5

#include <xlw/XlfServices.h>


// ... inside a function:
  const size_t nrows = XlfServices.Information.GetCallingCell().rows();
  const size_t ncols = XlfServices.Information.GetCallingCell().columns();

2. Abort function when a user presses Esc

Sometimes calculations in your function might take long time. For example suppose that your function runs Monte Carlo simulations with number of paths set by a user. If a user sets number of paths to be very large it might take too long to compute. It would be nice if there was a way to interrupt these calculations without killing Excel. Excel users are used to press Esc key in these cases. It is up to you to ensure that your function stops computation when Esc keypress is registered. To do that XLW provides IsEscPressed function:

In mylib.cpp: 

1
2
3
4
5
6

#include <xlw/XlfExcel>


if( XlfExcel::Instance().IsEscPressed() )
{
  throw("#Aborted");
}

3. Parallel Calculations

Excel can compute cells in parallel if they are marked as thread safe. To mark a function as thread safe put the following directive before the function name in function declaration block:

In mylib.h: 

1
2
3
4
5

double //Sum of square roots of two numbers
//<xlw:threadsafe
SumSqrt(double x, //argument 1
  double y //argument 2
  );

4. Timing Functions

It is trivial to add timing to a function, by adding //<xlw:time directive:

In mylib.h: 

1
2
3
4

double // Returns number of iterations performed
//<xlw:time
EscInterrupt(int n //max number of iterations
);

The comment “//<xlw:time” instructs InterfaceGenerator to produce timing code. If the function is called with a scalar output, it produces just the original result. However, if the function is called with 2x2 array as a requested output (see array formulas in Excel), the bottom right cell of the array will contain number of seconds it took to compute the function.

When timing functions returning MyArray, MyMatrix, CellMatrix or XlOper the timing information is appended to the output from below.

5. Volatile Functions

A volatile function is a function whose value can change even if the arguments have not. Usually you apply volatile directive to a function which depends on random numbers or time. To specify that a function is volatile add //<xlw:volatile before the function’s name:

In mylib.h: 

1
2
3

double // Number of minutes since Jan 1, 2000
//<xlw:volatile
SysTime();

In mylib.c: 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

#include <time.h>


double SysTime()
{
  time_t timer = time(NULL);

  struct tm y2k;
  y2k.tm_hour = 0;   y2k.tm_min = 0; y2k.tm_sec = 0;
  y2k.tm_year = 100; y2k.tm_mon = 0; y2k.tm_mday = 1;

  double seconds = difftime(timer, mktime(&y2k));

  return seconds/60;
}

It is also possible to time the volatile function by adding the timing directive. The order of //<xlw:time and //<xlw:volatile directives is not important.

6. Calling add-in functions from VBA

A function defined in an add-in can be called from VBA using Application.Run function: <Output Value> = Application.Run("<function name>", <input 1>, <input 2>, ... <input N>)

Example:

Add a module to an Excel file with the following code:

1
2
3

Function vbaOuter1(v As Variant)
  vbaOuter1 = Application.Run("Outer1", v)
End Function

This will create a VBA function that will behave in a similar way as add-in defined Outer1 function (aside from the fact that it will lack descriptions in the function wizard).

7. Happy Coding!

If you want to see the code described in this post in action you can download updated mylib library with example xls file.