Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Public function (97 SR2 on XP)

    No doubt someone will post back with "Aahhhh, that old nuggett. They cured that in Excel XP!"
    But I'm afraid some of us here the the sleepy backwaters are still on planet 97.

    I get a couple of large (20,000 plus rows) worksheets where the client has used colours to identify the location or current position of rows of data.
    I could use a macro to check the interior colours and return back a value from the current list, but I'd have to run it each time any of the data changed.
    I would like to use a public function, but how do I make reference to a cells address rather than a cells value?
    i.e.
    my public function I have called Colour

    Public Function Colour(Arg1) as long
    Colour = Range(Arg1).Interior.ColorIndex
    End Function

    So when I use say =Colour(A3)
    What I get in return is #VALUE error and I should have known better but what the heck, y' gotta try.

    Any help would be much appreciated.

    TIA
    Alan, Cheshire

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public function (97 SR2 on XP)

    Change your function to:

    Public Function Colour(Arg1 As Range) As Long
    Application.Volatile
    Colour = Arg1.Interior.ColorIndex
    End Function
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Public function (97 SR2 on XP)

    Since you already pass a range to the function, Range(Arg1) is superfluous. Try this:

    Public Function Colour(Arg1 As Range) As Long
    Colour = Arg1.Interior.ColorIndex
    End Function

    Chip Pearson has a whole series of Functions For Working With Cell Colors.

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Public function (97 SR2 on XP)

    Thanks guys.
    Works a treat.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Public function (97 SR2 on XP)

    One comment. Even with the application.volatile that Jan put in, the function is not "live". It will only be updated when a calculation is made.

    If you just change the color of a cell, this will not trigger a calculation event, so the function will not be updated. You must physically force a recalc (ctl-alt-f9) or also change a cell that affects another cell. F9, by itself, will not update it since excel "knows" that it nothing has changed so it "knows" not to do a recalc.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Public function (97 SR2 on XP)

    Thanks Steve.
    I came across exactly that problem but figured I could "circumvent" the problem possibly by including =Now() at one location in the inherited worksheet, but even that doesn't seem to work.
    I tried also to add a simple sum formatted to mm:ss such that the value was forcibly updated each time F9 or recalc.
    ctrl+alt+f9 had no effect.
    It appears that the easiest way to update is search and replace the = sign?
    Surely there's an easier way.

  7. #7
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Public function (97 SR2 on XP)

    Whoops, I am a muppet will be written 100 times without the aid of a computer.
    I could have just edited the last post to try and cover my tracks but I own up unreservedly.
    Forgot to put volatile in.
    HOWEVER...saving grace......
    by adding the =Now() and formatting cell as mm:ss, means that F9 works fine for recalcing the colour cell. Phew!

Posting Permissions

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