none
Using UDF in Conditional Format formula ... stopped being executed RRS feed

  • Question

  • Employing UDF as Boolean test function in CF formula. Then, it stopped being executed.

    Trobleshooting:

    Setup test worksheet within same workbook using very simple, new UDF that simply returns Boolean True to the Function. Certainly that would yield a CF response, but No. Re-cal'd WB & WS with WS.enableCalculation and WS.enableformatconditionscalculation all set True. NaDa.

    Have Debug.print statements in the UDF but nothing appears in the Immediate window.

    Have shut down and restarted Excel to no avail.

    What other troubleshooting is available?

    
    Tuesday, March 28, 2017 11:00 PM

Answers

  • Hi tocguy001,

    please try to post your code.

    with your description, we are not able to find the issue in your code.

    it is possible that you are missing something in your code.

    if you post the code then we can try to test the code on our side and try to find the issue and provide you a suggestion to solve that issue.

    below is one example , you can try to refer.

    Function Discount(quantity, price)
     If quantity >= 100 Then
     Discount = quantity * price * 0.1
     Else
     Discount = 0
     End If
     Discount = Application.Round(Discount, 2)
    
    End Function
    Sub demo2()
    Debug.Print Discount(105, 5)
    End Sub
    

    Output:

    Reference:

    Create Custom Functions in Excel

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by tocguy001 Wednesday, March 29, 2017 1:25 PM
    Wednesday, March 29, 2017 1:44 AM
    Moderator

All replies

  • Hi tocguy001,

    please try to post your code.

    with your description, we are not able to find the issue in your code.

    it is possible that you are missing something in your code.

    if you post the code then we can try to test the code on our side and try to find the issue and provide you a suggestion to solve that issue.

    below is one example , you can try to refer.

    Function Discount(quantity, price)
     If quantity >= 100 Then
     Discount = quantity * price * 0.1
     Else
     Discount = 0
     End If
     Discount = Application.Round(Discount, 2)
    
    End Function
    Sub demo2()
    Debug.Print Discount(105, 5)
    End Sub
    

    Output:

    Reference:

    Create Custom Functions in Excel

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by tocguy001 Wednesday, March 29, 2017 1:25 PM
    Wednesday, March 29, 2017 1:44 AM
    Moderator
  • TY for replying, Deepak.

    This turns out to be another of those instances where spending more time scrutinizing what may be causing the issue was not yielding any progress for me. Walking away and coming back to it later revealed that indeed this was yet another case of the CF UDF code not being completely explicit, i.e., lack of ".Value" when attempting to reference the Target range's value. Prior to making the code completely explicit, what is noteworthy is that despite 'adding' error handling the function still would not run unless called from the immediate window. It's as if something within Excel was 'set' to prevent further attempts.

    Also noteworthy is that in the simple test UDF I'd mentioned I'd used the shorthand IIF...Then method which as you illustrate in your example is not explicit enough.

    Sigh... Getting Smarter Every Day.

    Wednesday, March 29, 2017 1:24 PM