+ Reply to Thread
Results 1 to 5 of 5

Breakpoints Ignored

  1. #1
    Registered User
    Join Date
    09-02-2007
    Posts
    3

    Breakpoints Ignored

    I'm using Conditional Formatting for a cell with Formula Is set to my user-defined function. Everything works as it should except that:
    • Excel ignores any breakpoints I set in the user-defined function
    • Function doesn't run if there's a syntax error but Excel doesn't gripe like it should
    • Errors aren't trapped.
      • E.g., this line i = 1/0 does not throw error
      • But the function exits if there is a runtime error
    • Stop is ignored
    • Debut.Assert false is ignored

    Does anybody have an explanation or workaround for this 'un? I've got Excel 2003 SP2. Behavior is completely reproducible with a brand new bare workbook so it's probably not a side-effect of anything else..

  2. #2
    Forum Contributor
    Join Date
    07-05-2007
    Location
    Lexington, MA
    Posts
    302
    Do you know that the function is being called?
    Put a MSGBOX("Hi") line into your function to prove it.
    FrankBoston is the pen name for Andrew Garland, Lexington MA

  3. #3
    Registered User
    Join Date
    09-02-2007
    Posts
    3
    That was my initial thought, too. So I proved it's being called using both a MSGBOX and a Debug.Print statement. And the conditional formatting works like it's supposed to so Excel is properly using the return value.

    I did notice that Excel calls the function twice each time it redraws the cell with the conditional format but I doubt if that hurts anything. Red herring?

  4. #4
    Forum Contributor
    Join Date
    07-05-2007
    Location
    Lexington, MA
    Posts
    302

    Can't debug functions called from conditional formats

    Functions can be called multiple times during a recalculation. Excel is just not concerned about that, and it seems to happen.
    The statement i = 1/0 is only evaluated at run-time, and does not show an error at compile time.

    I tested the following code. It doesn't stop on breakpoints when called from a conditional format formula.
    Breakpoints do work in this funciton when it is called from a cell formula in the worksheet.
    Please Login or Register  to view this content.
    I conclude that Excel is intentionally turning off debugging during evaluation of conditional formats.

  5. #5
    Registered User
    Join Date
    09-02-2007
    Posts
    3
    Yeah, the Excel developers were probably forced to do it for some reason. I don't see that behavior as a good thing because untrapped errors are bad news.

    But I did find a way to work around the limitation. Say I want to conditionally format A1 with a user-defined function. I originally tried setting A1's conditional format formula to: =CellIsValid(A1)
    CellIsValid() returns a Boolean and the conditional formatting worked great. BUT breakpoints and runtime errors were ignored.

    So instead I set the conditional format formula to =A2 and then set A2's formula (the actual formula not the conditional format formula) to =CellIsValid(A1)

    So A2 will be TRUE or FALSE based on A1's validity. And A1's conditional format is based on that. Works great. And now breakpoints in CellIsValid() work and errors are trappable. And by the way, the function only gets called once, not twice like before. Go figure.

    Final step: Make A2 hidden so as not to be reminded of the ugly kludge and then chortle, "Calloo! Callay! Oh frabjous day!"
    Last edited by Lord Fitzy; 09-02-2007 at 09:55 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1