formula - Making Excel functions affect 'other' cells

Question

Let's say that I create a Sub (not a function) whose mission in life is to take the active cell (i.e. Selection) and set an adjacent cell to some value. This works fine.

When you try to convert that Sub to a Function and try to evaluate it from from spreadsheet (i.e. setting it's formula to "=MyFunction()") Excel will bark at the fact that you are trying to affect the value of the non-active cell, and simply force the function to return #VALUE without touching the adjacent cell.

Is it possible to turn off this protective behavior? If not, what's a good way to get around it? I am looking for something a competent developer could accomplish over a 1-2 week period, if possible.

Regards, Alan.

Note: I am using 2002, so I would favor a solution that would work for that version. Having that said, if future versions make this significantly easier, I'd like to know about it too.

Answer 1

It can't be done, which makes sense because:

• When a worksheet function is called, the cell containing the function is not necessarily the active cell. So you can't find the adjacent cell reliably.

• When Excel is recalculating a worksheet, it needs to maintain dependencies between cells. So it can't allow worksheet functions to arbitrarily modify other cells.

The best you can do is one of:

• Handle the SheetChange event. If a cell containing your function is changing, modify the adjacent cell.

• Put a worksheet function in the adjacent cell to return the value you want.

Update

Regarding the comment: "I'd like this function to work on a 'blank' spreadsheet, so I can't really rely on the SelectionChange event of spreadsheets that may not yet exist, but will need to call this function":

• Can you put your function in an XLA add-in? Then your XLA add-in can handle the Application SheetChange (*) event for all workbooks that are opened in that instance of Excel?

Regarding the comment: "Still, if you keep Excel at CalculationMode = xlManual and fill in just values, you should be just fine"

• Even when CalculationMode is xlManual, Excel needs to maintain a dependency tree of references between cells so that it can calculate in the right order. And if one of the functions can update an arbitrary cell, this will mess up the order. Which is presumably why Excel imposes this restriction.

(*) I originally wrote SelectionChange above, corrected now - of course the correct event is SheetChange for the Workbook or Application objects, or Change for the Worksheet object.

Update 2 Some remarks on AlanR's post describing how to 'kinda' make it work using a timer:

• It's not clear how the timer function ("Woohoo") will know which cells to update. You have no information indicating which cell contains the formula that triggered the timer.

• If the formula exists in more than one cell (in the same or different workbooks), then the UDF will be called multiple times during a recalculation, overwriting the timerId. As a result, you will fail to destroy the timer reliably, and will leak Windows resources.