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

[Marcelo Branco]

I must admit, though, that I was a little disappointed with how the above works... I had hoped besides pushing values into "foreign" cells, that it would be possible to affect other environmental properties via the subroutine called by the HYPERLINK function when evaluated by the Evaluate function, but unfortunately, it does not appear so... all attempts to do things like change the "foreign" cell's interior fill color or font color failed (no errors were raised, the code lines attempt to do this were simply ignored). Oh well, what it can do is still pretty neat, I just wished it could have been neater.

Rick,

This worked for me

Sub TooCool(InCell As Range, PushTo As Range)
  PushTo.Value = "The square of " & InCell.Value & " (in " & _
                 InCell.Address(0, 0) & ") is " & InCell.Value ^ 2 & "."
    PushTo.Interior.Color = vbYellow
    PushTo.Font.Color = vbRed
End Sub

Function DoCool(Rng As Range) As String
  If Rng.Value < 0 Then
    DoCool = "Number in " & Rng.Address(0, 0) & " is less than zero."
  Else
    DoCool = "Number in " & Rng.Address(0, 0) & " is greater than, or equal to, zero."
  End If
  Evaluate "HYPERLINK(TooCool(" & Rng.Address & ",J3))"
End Function

M.

[Rick Rothstein]

Rick,

This worked for me

Sub TooCool(InCell As Range, PushTo As Range)
  PushTo.Value = "The square of " & InCell.Value & " (in " & _
                 InCell.Address(0, 0) & ") is " & InCell.Value ^ 2 & "."
    PushTo.Interior.Color = vbYellow
    PushTo.Font.Color = vbRed
End Sub

Function DoCool(Rng As Range) As String
  If Rng.Value < 0 Then
    DoCool = "Number in " & Rng.Address(0, 0) & " is less than zero."
  Else
    DoCool = "Number in " & Rng.Address(0, 0) & " is greater than, or equal to, zero."
  End If
  Evaluate "HYPERLINK(TooCool(" & Rng.Address & ",J3))"
End Function

M.

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.

[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.