(see the file: Example_4.xlsm)
It always seems impossible until its done [Nelson Mandela]
Please note: This article is for experimental use only. We do not encourage anybody to use these findings in real world applications. (Except the developers of Excel...)
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
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
Answer to "Cannot VBA write data to cells in Excel 2007/2010 within a funtion" question
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:
(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.
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:
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:
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:
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)
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:
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”:
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)
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:
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:
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)
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:
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:
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.
You can read more about the chart-UDF-trick: SUM by color: Calculate when color changed
It is important to remember this, because we also realized some strange behaviour of this technique.
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():
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.
The Spreadsheet Page - A User-Define Function Can’t Change The Worksheet. Oh Yeah?
Daily Dose of Excel - Modifying Shapes (and Charts) With UDFs