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

**Rick Rothstein** · 10-16-2013, 02:12 AM

I belong to a private mailing list (Mailing list - Wikipedia, the free encyclopedia) and one of the participants (Jordan Goldmeier) posted a rather interesting "feature" about the worksheet's HYPERLINK function. Here is what he posted..

Specifically, if you write something like...
=HYPERLINK(myUDF())
(1) the UDF is fired like a macro when your mouse hovers over the cell that houses the formula; and,
(2) the UDF allows you to write to and the change properties of other cells on the worksheet.

"What is the big deal?", you ask. Well, the main thing is it contradicts the normally touted restriction concerning worksheet functions... "A worksheet function cannot change anything other than the value it displays". Now this "feature" did not seem all that useful to me as posted, but it got me to thinking... since UDF's follow the same rules as worksheet formulas, I wondered if there might be a way to use this "feature" within a UDF (user defined function) without having to place a HYPERLINK formula directly on the worksheet and so the user did not have to manually pass his/her mouse over the cell containing the HYPERLINK function? The answer? Yes, you can.

I have somewhat recently become fascinated with VBA's Evaluate method, which among other things, can process the text string equivalent of a worksheet formula. Well, as it turns out, this "feature" of the HYPERLINK function works when processed by the Evaluate function! There are innumerable ways to construct such code, but here is an example that should give you an idea of what is possible. Put this code in a module…

Code:

Sub TooCool(InCell As Range, PushTo As Range)
  PushTo.Value = "The square of " & InCell.Value & " (in " & _
                 InCell.Address(0, 0) & ") is " & InCell.Value ^ 2 & "."
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

and then put this formula in, say, C3…

=DoCool(B3)

Now put a negative value in cell B3 and look at both C3 and J3. Next put a positive number (different numeric part) in B3 and, again, look at both C3 and J3. Note that this one formula is pushing different values into two separate cells... pretty neat, eh? Also note the argument to the HYPERLINK formula does not have to be a pure macro... in my example above, I used a subroutine that took its own arguments (pure macros do not allow for arguments)... this make things much more flexible than if the HYPERLINK argument had been restricted to pure macros.

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. [Edit Note: Colors do work for some versions of Excel... see Messages #4 and #5]

Okay, let me put on my "responsibility" hat for a moment. There is a very real possibility that the above HYPERLINK "feature" is actually just a "bug" instead (this possibility was raised in the above mentioned mailing list exchanges that took place). If it is, then there is a possibility that Microsoft could fix it one day. Personally, I do not think that will happen as the "feature" has existed since Excel 2003 (the earliest version of Excel that I have installed)... I think microsoft would risk breaking the workbooks of others who might have discovered it on their own, and implemented it, across the last 13 years or more years. Anyway, I thought I would mention that so that you had all the facts before you proceeded to make use of any of the above.

***** MAKE SURE TO READ MESSAGE #7 FOR IMPORTANT INFORMATION *****

**Excel Fox** · 10-16-2013, 06:11 AM

Wow! This is fantastic. Thanks Rick.

**Marcelo Branco** · 10-17-2013, 09:21 PM

DoCool is really TooCool !

Thanks for sharing.

M.

**Marcelo Branco** · 10-17-2013, 09:35 PM

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

Code:

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** · 10-17-2013, 10:11 PM

Originally Posted by Marcelo Branco

Rick,

This worked for me

Code:

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** · 10-18-2013, 01:07 AM

Originally Posted by 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** · 10-19-2013, 10:40 PM

Originally Posted by 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.