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`

:

| ```
#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`

:

| ```
#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`

:

| ```
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`

:

| ```
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`

:

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

In `mylib.c`

:

| ```
#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:

| ```
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.