Monthly Archives: July 2014

It’s not your default Will

The xll library uses C++, which has a mechanism for default arguments, but the default value for an add-in is not a C++ default. No, really. It’s not your (C++) default Will.

The fourth argument to Args::Arg allows default arguments to be specified, but only if you use some funny macros.
Excel has built-in functionality to paste in argument names when you type Ctrl-Shift-A after typing the name of a function. If you type the name of a function in a cell (with no arguments or parentheses) and hit Return you will see a number show up. It is the register id Excel assigns to the function. If the cursor is in that cell and you type Ctrl-Shift-B a macro I wrote will fix up the cell to call the function with the default arguments you specify. It pastes the default arguments in the cells below the function. More obscure than Ctrl-Shift-A but more useful if you are a power user that wants to quickly cook up a spreadsheet.

But wait, that’s not all! If you type Ctrl-Shift-C it will create named ranges based on the variable names and use those in the fixed up function. Don’t do this in column A since the names get pasted into the column to the left of the selected cell.
If you pull this trick twice in a sheet there is a problem: the named ranges get clobbered. The solution is to put a prefix to the left of the cell containing the register id. The named ranges that get created will use that prefix. Just be sure to use unique prefixes.

Advertisements