Breaking the rules for UDF's (User Defined Functions)

[Rick Rothstein]

You are right, it does work... in XL2007 and above! I do all my testing in XL2003 figuring if it works there, it will work anywhere. Well, that appears to not be true... the colors do not work in XL2003, but it does work in XL2007 and later. Thanks for trying it out, although I am not sure why it did not occur to me to so myself.

EDIT NOTE: I just tried making the font bold and italicized... didn't work.

*** ALERT ***

Hold off making any use of the color "features" Marcelo posted about... there are early indications that using them may make Excel unstable (especially when there are array formulas on the sheet along witht he UDF); however, those same early indications seem to point to just changing cell values as being stable. Marcelo and I are corresponding on the private MrExcel MVP forum where we hopefully will lock things down better. When we figure out what's what, I'll post back here, but I wanted to give a "heads up" now before people started to make wide-spread use of the color "feature".

[Rick Rothstein]

*** ALERT ***

Hold off making any use of the color "features" Marcelo posted about... there are early indications that using them may make Excel unstable (especially when there are array formulas on the sheet along witht he UDF); however, those same early indications seem to point to just changing cell values as being stable. Marcelo and I are corresponding on the private MrExcel MVP forum where we hopefully will lock things down better. When we figure out what's what, I'll post back here, but I wanted to give a "heads up" now before people started to make wide-spread use of the color "feature".

Okay, here is the scoop...

Excel 2003: The method is fully stable, but you cannot change the colors of the foreign cells.

Excel 2007: The version of Excel requires you to first set everything up, save the workbook, close Excel, and then reopen Excel... after doing that, everything appears to then be fully stable.

Excel 2010: You can use colors or not (your choice) and the method is fully stable.

Excel 2013: I don't have this version available to me, so I can't test it, but I expect that the method will work without problems. Excel 2007 was a transition version between the old menu style of Excel and the Ribbon style of Excel and I suspect the problems have to do with the recoding Microsoft did to implement the change. I suspect they tightened things up in moving from XL2007 to XL2010, so there is no reason to believe the XL2007 problems would be reintroduced.

NOTE: While the method from this thread can be used to have a UDF control both its own cell and a foreign range of cells (one or more), there appears to be a better way to do this which I am working on. The concept is one that a fellow MrExcel MVP (yeah, I am one of those as well as a Microsoft MVP) name Mike Erickson introduced me to and it is far superior to the method from this thread. I am acquainting myself with the method and hope to post a new article featuring it in the near future, so keep checking back to this forum looking for it.