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 *****