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

I'm using Excel 2007, and it does not work. Excel mentions it creates a circular reference. I don't think you can alter other cells from a function, just return a value.

It's kind of functional programming, no side effects. If you could just alter other cells inside a function (used from a worksheet), then there's no way for Excel to know the order and what to recalculate if a cell changes.

This article also contains a lot of information about how Excel does recalculation. But it never states that the other cells are frozen.

I don't know what you are trying to do, but, why don't you just place another function in the adjacent 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 2

According to How to Create Custom User Defined Excel Functions:

Limitations of UDF's

• Cannot place a value in a cell other than the cell (or range) containing the formula. In other words, UDF's are meant to be used as "formulas", not necessarily "macros".

So, it looks like it cannot be done.

Answer 3

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.

Answer 4

Thank you all for responding. It is possible to do this! Kinda. I say 'kinda' because technically speaking the 'function' isn't affecting the cells around it. Practically speaking, however, no user could tell the difference.

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

Now I'm not an expert on how COM threading works (although I know VBA is Single Apartment Threaded), but be careful about your Timer running away with your Excel process and crashing it. This is really not something I would suggest as a solution to every other spreadsheet.

Just Make a Module with these contents:

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 can't do this in Excel, it's possible in Resolver One (although it's still a pretty odd thing to do).

It's a spreadsheet that allows you to define custom functions in Python that you can then call from a cell formula in the grid.

As an example of what you're asking, you might want to define a safeDivide function that (instead of raising a ZeroDivisionError) told you about the problem by colouring the denominator cell, and putting an error message beside it. You can define it like this:

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's an extra wrinkle: functions that get passed cells just get passed the cell value, so to work around that we insist on being passed a one-cell cellrange for the denominator.

If you're trying to do unusual things with spreadsheets which don't quite fit into Excel, or you're interested in using the power of Python to work with your spreadsheet data, it's worth having a look at Resolver One.

Answer 6

Here's an easy VBA workaround that works. For this example, open a new Excel workbook and copy the following code into the code area for Sheet1 (not ThisWorkbook or a VBA Module). Then go into Sheet1 and put something into one of the upper-left cells of the worksheet. If you type a number and hit Enter, then the cell to the right will be updated with 4 times the number, and the cell background will become light-blue. Any other value causes the next cell to be cleared. Here's 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

The subroutine captures all cell change events in the sheet. If the row and column are both <= 10, then the cell to the right is set to 4 times the changed cell if the value is numeric; otherwise the cell to the right is cleared.