By Tim Williams


2014-05-02 16:44:00 8 Comments

Not really a question, but posting this for comments because I don't recall seeing this approach before. I was responding to a comment on a previous answer, and tried something I'd not attempted before: the result was interesting so I though I'd post it as a stand-alone question, along with my own answer.

There have been many questions here on SO (and many other forums) along the lines of "what's wrong with my user-defined function" where the answer has been "you can't update a worksheet from a UDF" - this restriction outlined here:

Description of limitations of custom functions in Excel

There are a few methods which have been described to overcome this e.g. see here (https://sites.google.com/site/e90e50/excel-formula-to-change-the-value-of-another-cell) but I don't think my exact approach is among them.

See also: changing cell comments from a UDF

2 comments

@Tim Williams 2014-05-02 21:32:05

Posting a response so I can mark my own "question" as having an answer.

I've seen other workarounds, but this seems simpler and I'm surprised it works at all.

Sub ChangeIt(c1 As Range, c2 As Range)
    c1.Value = c2.Value
    c1.Interior.Color = IIf(c1.Value > 10, vbRed, vbYellow)
End Sub


'########  run as a UDF, this actually changes the sheet ##############
' changing value in c2 updates c1...
Function SetIt(src, dest)

    dest.Parent.Evaluate "Changeit(" & dest.Address(False, False) & "," _
                        & src.Address(False, False) & ")"

    SetIt = "Changed sheet!" 'or whatever return value is useful...

End Function

Please post additional answers if you have interesting applications for this which you'd like to share.

Note: Untested in any kind of real "production" application.

@RubberDuck 2014-05-29 15:40:50

Does anyone know why this works? I mean seriously, this is sorcery.

@omegastripes 2014-06-14 16:24:05

I've tested it on Win 7 HB, Excel 2003, values changed ok, but color formatting doesn't work.. Also there is one more work-around - see part 2 of the answer http://stackoverflow.com/a/23232311/2165759

@AFischbein 2014-08-12 13:15:20

Excel 2010 32-bit on Win7 64-bit - worked!.. once, then crashed when recalculated.

@user4039065 2015-03-06 18:51:04

One caveat that I can envision is the possibility of creating an infinite calculation loop where this method is used to modify or create a value or formula in a cell. This could conceivably re-initiate a calculation cycle and so on and so on. This might not be recognized (and halted) by the system as a circular reference; hence the crashes noted. Similar to event macros (e.g. Worksheet_Change) running on top of themselves. Moral of the story: if you are trying to override 'behavior by design', accept any limitations that come with it.

@Tim Williams 2015-03-06 19:23:54

@Jeeped - agreed: this is definitely a use-at-your-own-risk type of thing.

@AER 2016-08-10 04:57:10

You can't add breaks in the code due to Evaluate bypassing the called function. May create issues in debugging.

@AER 2016-08-10 05:47:29

@Jeeped the circular reference is solved in my (self) answered question here: stackoverflow.com/questions/38863510/…

@GSerg 2019-04-08 12:20:22

Note that using this may result in a crash.

@Siddharth Rout 2015-07-30 09:43:59

The MSDN KB is incorrect.

It says

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

  1. Insert, delete, or format cells on the spreadsheet.
  2. Change another cell's value.
  3. Move, rename, delete, or add sheets to a workbook.
  4. Change any of the environment options, such as calculation mode or screen views.
  5. Add names to a workbook.
  6. Set properties or execute most methods.

In the below code you can see points 1, 2,4 and 5 can be easily achieved.

Function SetIt(RefCell)
    RefCell.Parent.Evaluate "SetColor(" & RefCell.Address(False, False) & ")"
    RefCell.Parent.Evaluate "SetValue(" & RefCell.Address(False, False) & ")"
    RefCell.Parent.Evaluate "AddName(" & RefCell.Address(False, False) & ")"

    MsgBox Application.EnableEvents
    RefCell.Parent.Evaluate "ChangeEvents(" & RefCell.Address(False, False) & ")"
    MsgBox Application.EnableEvents

    SetIt = ""
End Function

'~~> Format cells on the spreadsheet.
Sub SetColor(RefCell As Range)
    RefCell.Interior.ColorIndex = 3 '<~~ Change color to red
End Sub

'~~> Change another cell's value.
Sub SetValue(RefCell As Range)
   RefCell.Offset(, 1).Value = "Sid"
End Sub

'~~> Add names to a workbook.
Sub AddName(RefCell As Range)
   RefCell.Name = "Sid"
End Sub

'~~> Change events
Sub ChangeEvents(RefCell As Range)
    Application.EnableEvents = False
End Sub

![enter image description here

@Sam 2015-07-30 09:52:10

Just for the sake of debate - could it be argued that the KB is correct, as the UDF is Evaluating/Calling a Sub, which is actually doing the changing...?

@Siddharth Rout 2015-07-30 10:08:55

For the sake of debate then :P The KB should say that clearly However the above/below can be achieved using Evalute/Calling a Sub rather than issuing a blanket statement that that such a function cannot do any of the following.... @MacroMan

@Sam 2015-07-30 10:15:01

Nice return ;) Obviously the people at MS weren't prepared for this. I'm also proposing a new tag of vba-voodoo for such things

@Siddharth Rout 2015-07-30 10:23:37

@MacroMan: Obviously the people at MS weren't prepared for this. While I can understand and live with that. My real beef with those people is that they do not take MS Office feedback seriously. I don't know how many feedback I have left on the MSDN KBs in the last couple of years but none of them have been actioned! It's as if they bloody don't care!

@Sam 2015-07-30 10:43:03

They probably don't - As far as MS is concerned Office is their 'cash cow' and (in my opinion) there isn't any real competitor in the market especially where enterprise is concerned so they can probably afford to be a bit nonchalant in that respect. I imagine they focus their efforts on creating a newer version of everything to try and keep up with the market *ahem - Apple* rather than making what they've got even better...

@user535673 2017-07-21 13:55:05

Really useful! I'm creating a function that takes various bits of info, does a bit of logic and calculations, and returns a string containing the info in a more readable way. As part of this I'd like to have some of the text bigger than other bits. The code above looks like it will work for this. (Wish I could use real databases and display stuff on web pages, but that's another matter!)

Related Questions

Sponsored Content

7 Answered Questions

[SOLVED] How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

  • 2014-03-20 19:09:13
  • Portland Runner
  • 732467 View
  • 496 Score
  • 7 Answer
  • Tags:   regex excel vba

0 Answered Questions

UDFs Vs Built In Functions in Excel

42 Answered Questions

[SOLVED] How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 998103 View
  • 1732 Score
  • 42 Answer
  • Tags:   c# .net excel file-io

13 Answered Questions

[SOLVED] How to avoid using Select in Excel VBA

  • 2012-05-23 05:57:58
  • BiGXERO
  • 235034 View
  • 475 Score
  • 13 Answer
  • Tags:   excel vba

1 Answered Questions

1 Answered Questions

1 Answered Questions

0 Answered Questions

Excel User Defined Function within another UDF not updating

  • 2016-05-13 11:20:29
  • ChiPhi85
  • 289 View
  • 2 Score
  • 0 Answer
  • Tags:   vba excel-vba excel

2 Answered Questions

[SOLVED] Excel VBA > Formula not updating until workbook is saved

  • 2015-02-17 00:54:31
  • BeardedCoder
  • 1204 View
  • 1 Score
  • 2 Answer
  • Tags:   excel vba

1 Answered Questions

[SOLVED] UDF causing Lag

Sponsored Content