Results 1 to 7 of 7

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

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

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

    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 *****
    Last edited by Rick Rothstein; 10-19-2013 at 10:43 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Wow! This is fantastic. Thanks Rick.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    DoCool is really TooCool !

    Thanks for sharing.

    M.

  4. #4
    Junior Member
    Join Date
    Aug 2013
    Posts
    4
    Rep Power
    0
    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.

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Marcelo Branco View Post
    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.
    Last edited by Rick Rothstein; 10-17-2013 at 10:13 PM.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    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".

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Rick Rothstein View Post
    *** 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.

Similar Threads

  1. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  2. Color Functions In Excel
    By Admin in forum Download Center
    Replies: 2
    Last Post: 10-24-2013, 11:44 AM
  3. Creating Powerpoint Slides: Rules
    By Transformer in forum Powerpoint Help
    Replies: 0
    Last Post: 05-17-2013, 08:41 PM
  4. Replies: 2
    Last Post: 05-14-2013, 01:02 AM
  5. Excel Macro to Sort Data if a value changes in defined range
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 4
    Last Post: 09-05-2012, 10:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •