«Excel formula to change the value of another cell?» ... «It - could - work!»

Update: You can find another interesting evaulation method added here.

We know that User Defined Functions (UDF) are not allowed to directly change values/properties in any cell, worksheet, or workbook - they must only return a value, as any other Excel built-in formula does. Your UDF will simply go to error and stop immediately if you code anything forbidden into it.
This is the normal way of working according to Microsoft’s design goal.
However there are some workarounds to bypass this limitation.
The formula is always entered to a cell or cells on a worksheet, so this way the caller (which calls the UDF, so makes it run) is the cell itself. Maybe Excel watches only if the caller is a cell or not - it seems the code stops running only if we try to alter a sheet from an UDF called from a cell. So we should try to find other methods to call an UDF.

by The FrankensTeam
Send your comments

Method 1 - Mouse rollover

The first method we have seen is the mouse rollover technique by Jordan Goldmeier.
If the Windows mouse rollover event is the caller, the UDF will run and do whatever you coded into it. You can read the details of this ingenious technique here:
How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell and The Excel Rollover Mini FAQ

Method 2 - Windows timer

The second we found after googling the topic cited here, uses a funny trick with Windows timer:
Answer to "Cannot VBA write data to cells in Excel 2007/2010 within a funtion" question

Method 3 - Evaluate named formulas

It's kind of fun to do the impossible [Walt Disney]

We started to check how Excel evaluates these formulas and realized some strange things.
We used this test function:

   Function d(rng)
       Debug.Print Application.Caller.Address
       rng.Value = ""
   End Function

(Debug.Print is just for better understanding the ongoing events.)
It should (or at least try to) delete the content of the cell referenced with the formula. If we write this formula to a cell, it will result #VALUE error - this is the normal way of working, as Excel realized we want to do a forbidden change from a formula.

Evaluate Formula dialogue box

The first thing we realized in connection with this simple formula is the different method how Evaluate Formula dialogue box works in Excel 2007 and Excel 2010:
  • In Excel 2010 (and 2003) when starting to Evaluate, the UDF is called by the active cell, but the code interrupts at rng.Value = "", so no deletion happens.
  • In Excel 2007 the Evaluate dialogue works differently. The UDF is called by the active cell, but it runs, so the deletion happens!

Evaluate named formula from VBA

In the next step we used the UDF in named formulas to make easier to put value into the cell, instead of the #VALUE error. Using this name (with relative reference to the active cell):

del = d(Sheet1!C1)

We find that if we reference this named formula in VBA, the UDF code runs and the deletion happens in 2007 and 2010 version too!
So we can easily step over the limitations: simply create a named formula with the original UDF and write a second UDF to evaluate this named formula. We only need to use this second formula on the worksheet, which will call the original UDF via the evaluation of the named formula. This trick works within Excel, without Windows or Excel events. Our Example 1 is based on this technique.

Example 1 - The Suicide Formula v1

Here is a small example about a self-deleting formula which will clear the cell if the value of another cell is 0 - it could be useful for line charts where we need empty cell to separate lines within a series.
This is what we have in the code module:

Option Explicit

Function d(rng)


   rng.Value = ""

End Function

Function empty_f()

   Dim v


   v = [del]

End Function

Function d(rng) will do the deletion, this is what we use in the named formula <del>, with relative reference to the active cell - this is the cell will be given to the d function as rng and this cell will be deleted. If C1 is selected, the name will look like:

   del = d(Sheet1!C1)

In Function empty_f() the only thing happens is the evaluation of [del] using a variant (v). Only this empty_f() should be used on the sheet, for example this way:

   = IF(D2 = 0, empty_f(), D2)

This formula will copy the value of D2 if it is not zero. If D2 is zero then the formula deletes itself.
(see the file: Example_1.xlsm)

Evaluate using the old EVALUATE() formula

In all versions of Excel it is possible to use Excel 4.0 macro formulas in named formulas (defined names). This way we could simplify the above mentioned VBA-based evaluation and only the code of the UDF will be needed.

Example 2 - The Suicide Formula v2

In the above example we used empty_f() only to trigger the evaluation of the named formula where we used the “illegal” UDF. With the help of the old Excel 4.0 EVALUATE formula it is possible to replace this code and make the model easier to understand. The key element is the defined name formula. In this case, C1 must be selected first, then create the named formula this way:

  p = EVALUATE ("d("& CELL( "address" , Sheet1!C1) & ")")

The cell referenced in this name should always be the active cell because CELL formula will read the address of this cell and give it to the UDF d() as string parameter. This is the cell will be deleted (as a kind of hara-kiri) when the EVALUATE formula triggers the UDF to run.

The formula used on the sheet now simplified, using the name “p”:


 =IF(D2=0, p, D2)

This model works the same way as the above, but we found one interesting difference regarding the calls of the UDF You can read the details here.
(see the file: Example_2.xlsm)

Evaluate named formula as Validation list

There is another interesting way to trigger the evaluation. Using a validation with list set by a name, Excel evaluates the name when you click on the small arrow to open the drop-down list. Accordingly we could use a little trick with UDF in validation as it is described in Example 1 and 2.

Example 3 - Validation with memory

It is possible to build up a validation (using drop-down list) which will copy the old value to another cell.
We only need this UDF:

Function PrintOldValue()

    Set PrintOldValue = [=Sheet1!$D$1:$D$4]

    Application.Caller.Offset(0, -1).Value = "old value:" & Application.Caller.Value

End Function

Within the function we set PrintOldValue to reflect a range where the elements of the list are stored - then we use this function in the named formula:
     list = PrintOldValue()

This name will be the list we use in the validation. And when you click on the small arrow, the magic happens: before choosing the new value, the UDF writes the old value to the cell left to the active cell - as it is set by the Application.Caller.Offset(0, -1).Value statement. Remember that Application.Caller is the cell from which we started the validation.
You can say that this is possible without tricks, using Worksheet_change event. Yes, that is correct. We do not want to encourage anyone to use this “illegal” UDF. (But we must highlight one obvious advantage: that you can simply copy the cell, the UDF technique will work anywhere in your file - no need to change/set up new Worksheet_Change events.)
(see the file: Example_3.xlsm)

Evaluate named formula of a chart

You can read in this post that column width change and most of the cell-format changes triggers charts to call the named formulas to be calculated (evaluated). We realized that the UDF wrapped to a named formula will be evaluated together with the named formula.

Example 4 - Trigger calculation: format change, column width change

We used this feature to build an UDF which will trigger sheet calculation. After some experiments the final UDF looks like:

Function MyCalc()
    MyCalc = 0
    ActiveSheet.Range("a1").Formula = ActiveSheet.Range("a1").Formula
End Function

The second row (seemingly) changes the sheet and as a result calculation happens. You can easily test it using a volatile RAND() function on the sheet.

We created this name:

rnd = IFERROR(EVALUATE(mycalc()),0)

According to our observations, it is not a must to use EVALUATE within the name, but we realized some crashed in some versions of Excel without it, so the solution is more robust this way.

The third element needed is a very simple chart where the name “rnd” is used for series value. The chart must be visible on the sheet, if it is hidden, it does not call the named formula to be re-calculated.

(see the file: Example_4.xlsm)
You can read more about the chart-UDF-trick: SUM by color: Calculate when color changed

Further findings

Seems there are ways to step over the limitations - but remember that the limitations are not designed to make our life harder but to prevent users to lose control over their work.
It is important to remember this, because we also realized some strange behaviour of this technique.

Debugging the code

First of all, not an easy task to test UDFs because breakpoints could not work in these special cases, and the code does not stop on the Debug.Assert False statement. Many times the only way we could have some information about what is happening inside the code is the good old way of logging: the Debug.Print method. Using this we could capture who is the caller (with Debug.Print Application.Caller.Address which works fine for most of the cases).

Excel calculates twice the named formula called from VBA?

Using this technique we find that the above mentioned d() function was called twice in Example 1 - first from $A$1 (at least, this is what the Application.Caller.Address tells) and second from the cell where we referenced the empty_f() function.
Knowing this it is not a surprise that our Suicide formula v1 will always delete the content of $A$1...
If you want to avoid this, add an If statement to the code of d():

Function d(rng)


   If rng.Address <> "$A$1" Then

       rng.Value = ""

   End If

End Function

By using the Excel 4.0 macro Evaluation formula (Example 2) this problem does not appear, the formula called only once and does not touch A1 - so our conclusion is that the this is the best way to make “illegal” UDFs work.

Excel crashes

In Example 1 and 2 we realized in Excel 2010 that if you use mouse click to confirm the value of the cell (so you do not push Enter or Tab or arrows) Excel will crash immediately. It did not happen so clearly in Excel 2007, but in some cases this version crashed too.

Some exceptions for sheet change from formula

On the below links you can read that there are some properties exceptionally could be changed by UDF, especially in connection with comments and with shapes:
The Spreadsheet Page - A User-Define Function Can’t Change The Worksheet. Oh Yeah?
Daily Dose of Excel - Modifying Shapes (and Charts) With UDFs

Krisztina Szabó,
Sep 19, 2012, 8:41 AM
Krisztina Szabó,
Sep 19, 2012, 8:41 AM
Krisztina Szabó,
Sep 19, 2012, 8:45 AM
Krisztina Szabó,
Nov 3, 2013, 11:54 AM