Advanced Flow Control in Macro Sheets

Ordinarily, when Microsoft Excel runs a macro, it does so by evaluating successive cells, one at a time, from top to bottom. However, simple top-to-bottom execution does not allow for any of the flow-control constructs that are essential in a high-level language. Microsoft Excel solves this problem by using the flow-control XLOPER, named xltypeFlow. If a function on the sheet returns a flow-control XLOPER, then instead of going on to the next cell, Microsoft Excel executes the flow-control command contained in that XLOPER.

This is how Microsoft Excel controls all flow on macro sheets. It means that functions such as GOTO and RETURN can be implemented as first-class functions. Therefore, you can write your own versions of GOTO and HALT. This capability also explains how statements such as the following work:

=IF(Boolean,GOTO(A3),GOTO(A4))

This capability allows you to write and understand flow-control functions. For example, suppose you want a function that checks the sign of a number and then performs one action if the sign is negative, another action if it is zero, and yet another action if it is positive. The macro could look like the following example.

You can use flow-control XLOPERs to write this function, as shown in the following code example.

#include <windows.h>
#include <xlcall.h>

LPXLOPER WINAPI SwitchSign(double d, LPXLOPER pxNeg,
    LPXLOPER pxZero, LPXLOPER pxPos)
{
    // pxNeg, pxZero, and pxPos must be local references.

    static XLOPER xGoto, xError, xSheet;
    LPXLOPER pxChosen;

    if (d < 0)
        pxChosen = pxNeg;
    else if (d == 0)
        pxChosen = pxZero;
    else
        pxChosen = pxPos;

    if (pxChosen->xltype != xltypeSRef)     
    {
        xError.xltype = xltypeErr;
        xError.val.err = xlerrValue;
        return &xError;
    }

    // Figure out the Sheet ID of the current sheet, which we need
    // to make xGoto

    Excel4(xlSheetId, &xSheet, 0);

    xGoto.xltype = xltypeFlow;
    xGoto.val.flow.xlflow = xlflowGoto;
    xGoto.val.flow.valflow.idSheet = xSheet.val.mref.idSheet;
    xGoto.val.flow.rw = pxChosen->val.sref.ref.rwFirst;
    xGoto.val.flow.col = pxChosen->val.sref.ref.colFirst;

    return &xGoto;
}

This sample code is in the SWITCHSN directory or SwitchSn folder.

Note

You can return flow control XLOPERs from your C functions, and you can pass them to Excel4. The only thing you can't do with flow-control XLOPERs is pass them into C functions from Microsoft Excel. In other words, you cannot pass the return value from the GOTO function to one of your own functions. This means you can't write IF in C.