Using Excel functions affects "other" cells

Question

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

When you try to convert this Sub to a function and try to evaluate it from a table (for example, set its formula to "= MyFunction ()") Excel will bark that you are trying to affect the value of an inactive cell and simply force the function to return #VALUE, without touching the neighboring cell.

Can this protective behavior be disabled? If not, what's a good way around this? I am looking for something that a competent developer could accomplish in 1-2 weeks, if possible.

Regards, Alan.

Note. I am using 2002, so I would prefer a solution that will work for this version. Having said that, if future versions make it much easier, I would also like to know about it.

Answer 1

This is impossible to do, which makes sense because:

• When a worksheet function is called, the cell containing this function is not necessarily the active cell. Therefore, you cannot reliably find a neighboring cell.

• When Excel recounts a worksheet, it must support dependencies between cells. Therefore, it cannot allow sheet functions to arbitrarily change other cells.

The best you can do is one of:

• Handle the SheetChange event. If the cell containing your function changes, change the adjacent cell.

• Place the worksheet function in an adjacent cell to return the desired value.

Update

Regarding the comment: "I would like this function to work on an" empty "table, so I cannot rely on the SelectionChange event for spreadsheets, which does not exist yet, but I 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 open in this Excel instance?

Regarding the comment: "However, if you save Excel in CalculationMode = xlManual and fill in only the values, you should be fine"

• Even when CalculationMode is xlManual, Excel must maintain a dependency tree between cells so that it can calculate in the correct order. And if one of the functions can update an arbitrary cell, this will ruin the order. This is probably why Excel imposes this limitation.

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

Update 2 Some notes on AlanR's post describing how to “curiously” get it to work with a timer:

• It is not clear how the timer function ("Woohoo") will know which cells to update. You have no information about which cell contains the formula that caused the timer.

• If the formula exists in more than one cell (in the same or different books), then UDF will be called several times during recalculation, rewriting timerId. As a result, you cannot reliably destroy the timer and test Windows resources.

Answer 2

I am using Excel 2007 and it does not work. Excel mentions that it creates a circular link. I don’t think you can change other cells from a function, just return the value.

This is a kind of functional programming, no side effects. If you can simply change other cells inside the function (used on the worksheet), then there is no way for Excel to know the order and what to recount if the cell changes.

This article also contains a lot of information about how Excel performs recounting. But he never states that other cells are frozen.

I don’t know what you are trying to do, but why don’t you just put another function in a neighboring cell that takes the first cell as a parameter?

Example:

Public Function Bar(r As Range) As Integer If r.Value = 2 Then Bar = 0 Else Bar = 128 End If End Function 

Answer 3

According to How to Create Custom Excel Custom Functions :

UDF Limitations

• It is not possible to put a value in a cell other than the cell (or range) containing the formula. In other words, UDFs are intended to be used as “formulas”, and not necessarily “macros”.

So it seems like this is impossible.

Answer 4

Thanks to everyone for the answer. It can be done! Like. I say “curious” because technically saying “function” does not affect the cells around it. In practice, however, no user could tell the difference.

The trick is to use the Win32 API to start the timer, and as soon as it goes out, you do what you want in any cell and turn off the timer.

Now I'm not an expert on how COM streams work (although I know that VBA is a single threaded apartment), but be careful when your timer runs off with the Excel process and resets it. This is really not what I would suggest as a solution for all other spreadsheets.

Just create a module with this content:

 Option Explicit Declare Function SetTimer Lib "user32" (ByVal HWnd As Long, _ ByVal IDEvent As Long, ByVal mSec As Long, _ ByVal CallFunc As Long) As Long Declare Function KillTimer Lib "user32" (ByVal HWnd As Long, _ ByVal timerId As Long) As Long Private timerId As Long Private wb As Workbook Private rangeName As String Private blnFinished As Boolean Public Sub RunTimer() timerId = SetTimer(0, 0, 10, AddressOf Woohoo) End Sub Public Sub Woohoo() Dim i As Integer ' For i = 0 To ThisWorkbook.Names.Count - 1 ' ThisWorkbook.Names(i).Delete ' Next ThisWorkbook.Worksheets("Sheet1").Range("D8").Value = "Woohoo" KillTimer 0, timerId End Sub 

Answer 5

While you cannot do this in Excel, this is possible in Resolver One (although this is still a rather strange thing).

This is a spreadsheet that allows you to define custom functions in Python, which you can then call from a cell formula in a grid.

As an example of what you are asking, you can define the safeDivide function, which (instead of raising ZeroDivisionError ) told you about the problem by coloring the denominator cell and placing the error message next to This. You can define it as follows:

 def safeDivide(numerator, cellRange): if not isinstance(cellRange, CellRange): raise ValueError('denominator must be a cell range') denominator = cellRange.Value if denominator == 0: cell = cellRange.TopLeft cell.BackColor = Color.Red cell.Offset(1, 0).Value = 'Tried to divide by zero' return 0 return numerator / denominator 

There is an additional wrinkle: the functions that the transferred cells transmit simply transmit the cell value, so in order to get around this, we insist on transmitting a single-network cellular range for the denominator.

If you are trying to do unusual things with spreadsheets that don’t quite fit into Excel, or you are interested in using Python to work with the data in your spreadsheets, you should take a look at Resolver One.

Answer 6

Here is an easy way to work around VBA, which works. In this example, open a new Excel workbook and copy the following code into the code area for Sheet1 (not ThisWorkbook or VBA Module ). Then go to Sheet1 and put something in one of the upper left cells of the worksheet. If you dial a number and press Enter, the cell on the right will be updated with a 4-fold number, and the background of the cell will turn light blue. Any other value causes the next cell to clear. Here is the code:

 Dim busy As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If busy Then Exit Sub busy = True If Target.Row <= 10 And Target.Column <= 10 Then With Target.Offset(0, 1) If IsNumeric(Target) Then .Value = Target * 4 .Interior.Color = RGB(212, 212, 255) Else .Value = Empty .Interior.ColorIndex = xlColorIndexNone End If End With End If busy = False End Sub 

A routine captures all cell change events in a worksheet. If the row and column are <= 10, then the cell on the right is set 4 times in the changed cell, if the value is numeric; otherwise, the cell on the right is cleared.