Step 4 - XLM

Now we “just” need to get rid of the hard-coded list of sheets.

For this, Excel’s worksheet functions are no longer enough, and we need more powerful tools – namely XLM macros. XLM is Excel’s old macro language that predates VBA. For backwards compatibility, XLM functions can still be used in new versions of Excel.

XLM functions are not covered by the Excel help files, but you can download the help files for XLM functions from http://www.microsoft.com/downloads/ – search for macrofun.exe, or for xlmacro.exe. The latter was last spotted at http://www.microsoft.com/downl…03748ca5cd&displaylang=en

We are going to use two functions here: GET.WORKBOOK and GET.DOCUMENT.

- GET.WORKBOOK returns information about the current workbook. GET.WORKBOOK(1) will return a list of all worksheets in the workbook.
- GET.DOCUMENT returns information about the current document, which in the case of worksheet formulas means the current sheet. GET.DOCUMENT(1) returns the sheet name.

The main limitation of XLM functions is that they can only be used in named formulas, not directly in a worksheet. So we can break down our formula into two parts – a named formula to replace SheetList, and an array formula like what we had before.

So let us try named formula, named Addr:

=GET.WORKBOOK(1)&"!"&ADDRESS(ROW(),COLUMN())

Try entering it in a worksheet range (I entered it in cells A8 to D8) and you should get results similar to this (where // separates cells):

[3d sum.xls]Totals!$A$8 // [3d sum.xls]Sheet1!$B$8 // [3d sum.xls]Sheet2!$C$8 // [3d sum.xls]Sheet3!$D$8

The problem with this is that our Totals sheet is included in the results. If we used this in a formula, the calculation would be circular. We need to exclude the current sheet from the array.

This is where the GET.DOCUMENT function comes in. We modify our formula so that the current sheet is excluded, and replaced with a blank:

=IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),"",GET.WORKBOOK(1)&"!"&ADDRESS(ROW(),COLUMN()))

Used in a worksheet formula, the named formula should now yield something like this: (the first cell is empty)

// [3d sum.xls]Sheet1!$B$8 // [3d sum.xls]Sheet2!$C$8 // [3d sum.xls]Sheet3!$D$8

Now, if we tried to use any of these range references in an INDIRECT function, we would still get an error, because of the space in the workbook name. To handle that, we need to put single quotes before and after the workbook / worksheet names:

=IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),"","'"&GET.WORKBOOK(1)&"'!"&ADDRESS(ROW(),COLUMN()))

This should return:

// '[3d sum.xls]Sheet1'!$B$8 // '[3d sum.xls]Sheet2'!$C$8 // '[3d sum.xls]Sheet3'!$D$8