none
Excel 2016 formula (from macro) not being calculated RRS feed

  • Question

  • I am using Office 360 (or Office 2016 by another name I believe).

    I have created a macro with the following code:

        Function CheckColor1(range)
          If range.Interior.Color = RGB(217, 217, 217) Then
            CheckColor1 = Grey
          ElseIf range.Interior.Color = RGB(0, 0, 0) Then
            CheckColor1 = Black
          End If
        End Function

    I have a cell with grey colored text in cell Z2, of color RGB(217, 217, 217) in fact.

    In cell AB2, I have the following formula:

        =CheckColor1(Z2)

    I press enter, and the formula stays visible, uncalculated it seems.

    If I highlight the cell, and press Formulas > Calculate Now, nothing changes.

    I have checked to make sure the Macro is specific to the file I am working on.

    I have saved the file as an .XLSM, which is a macro-enabled workbook.

    Any ideas on how I can get the formula to be calculated?

    Thanks.
    Monday, March 7, 2016 11:40 AM

Answers

  • Hi SRD75,

    After checking the file, I find you checked Show Formulas option. To show the calculated value, you could navigate to FORMULAS->Formula Auditing-> unclick Show Formulas.

    For a VBA code, you could try the code below:

    ActiveWindow.DisplayFormulas = False

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 14, 2016 9:26 AM

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,

    Emi Zhang
    TechNet Community Support


    Please mark the reply as an answer if you find it is helpful.

    If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Wednesday, March 9, 2016 2:15 AM
  • Hi SRD75,

    What value you want in Cell AB2, a string or set the Color? Could you share us a screen shot about your issue and share us a excel file with this macro? I made a test with your code, and I would get 0. If you want to get Grey or Black string, I suggest you try the code below:

    Function CheckColor1(range)
           If range.Interior.Color = RGB(217, 217, 217) Then
             CheckColor1 = "Grey"
           ElseIf range.Interior.Color = RGB(0, 0, 0) Then
             CheckColor1 = "Black"
           Else
             CheckColor1 = "Test"
           End If
    End Function

    Where did you insert this code? I suggest you create module and insert this code.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, March 9, 2016 3:14 AM
  • Hi Edward,

    Thank you very much for assisting me.

    I've tried your Macro code, but interestingly, both cell text colors produce the outcome "test".

    See attached.

    Thursday, March 10, 2016 2:47 AM
  • Hi SRD75,

    Based on your document, it seems you want to get string based on the font color of range. If so, I suggest you try the code below:

    Function TT(r As range) As String
         If r.Font.Color = RGB(217, 217, 217) Then
             TT = "Grey"
         ElseIf r.Font.Color = RGB(0, 0, 0) Then
             TT = "Black"
         Else
             TT = "Test"
         End If
    End Function

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 10, 2016 8:26 AM
  • Hi Edward,

    Is is only a single cell I want to measure, not a range.

    Even so, using your updated code produces "Test" again.

    I've uploaded my file (link) so you can see this for yourself.

    Regards,

    Steve.


    • Edited by SRD75 Thursday, March 10, 2016 8:42 AM
    Thursday, March 10, 2016 8:41 AM
  • Hi SRD75,

    I have some questions below:

    1. What do you want to deal with a cell, with cell font color?

    2. In your file, there is only function TT, why you used "=CheckColor1(A1)"?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 10, 2016 9:14 AM
  • Hi Edward,

    Thank you. Your code works on my test file, but for some reason, not on the file I want it to work on.

    I have enabled Macros. The Macro code is identical to the test file. The formula code is identical. But the formula remains uncalculated.

    Is there any chance I could email you the file to look at please? Alternatively, I could upload it to a location that I won't post on this forum. I just don't know how I can provide you the upload location.

    Bit desperate for help,

    Steve.

    Sunday, March 13, 2016 9:21 AM
  • HI

    >>> I just don't know how I can provide you the upload location.

    Follow these instruction.

    To upload to onedrive - 

    1. Zip your file.
    2. Login to https://onedrive.live.com/ utilizing the same Login ID and                               -   Password which you have used on this forum..
    3 Click Upload in the top OR drag and drop the file here.
    4. After uploading, right click the file and choose share.
    5. Click Get a Link.
    6. Create Link.
    7. Copy and paste the link here.


    Cimjet

    Sunday, March 13, 2016 1:52 PM
  • Quote: SRD75: "Alternatively, I could upload it to a location that I won't post on this forum."

    (it is sensitive information)

    Sunday, March 13, 2016 2:09 PM
  • Even so, using your updated code produces "Test" again.

    It means the formula does get executed.

    You can set a breakpoint at your VBA code for find if the range.Interior.Color is indeed RGB(217, 217, 217).

    Monday, March 14, 2016 2:54 AM
  • Hi SRD75,

    I agree with ghjackie, if you got “Test”, the formula does get executed.

    >> Your code works on my test file, but for some reason, not on the file I want it to work on.
    For this, I suggest you check which formual you used in your file, and check whether the function has been defied in your file. For example, if you use CheckColor1(A1) in your file, I suggest you check whether this function has been defined in your file, and check the code.

    >> it is sensitive information
    For this, I think you could remove all sensitive information and just left the code related with CheckColor1().

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 14, 2016 8:47 AM
  • Please see my last post:

    =======
    Thank you. Your code works on my test file, but for some reason, not on the file I want it to work on.

    I have enabled Macros. The Macro code is identical to the test file. The formula code is identical. But the formula remains un-calculated.
    =======

    Blank file with issue: http://www.wascc.com.au/2016-blank.xlsm


    • Edited by SRD75 Monday, March 14, 2016 9:02 AM
    Monday, March 14, 2016 9:00 AM
  • Hi SRD75,

    After checking the file, I find you checked Show Formulas option. To show the calculated value, you could navigate to FORMULAS->Formula Auditing-> unclick Show Formulas.

    For a VBA code, you could try the code below:

    ActiveWindow.DisplayFormulas = False

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, March 14, 2016 9:26 AM
  • Ahhhhhh.

    That's great Edward. Sincerely appreciated.

    Monday, March 14, 2016 12:53 PM
  • Hi SRD75,

    If your issue has been resolved, I suggest you mark the helpful reply as answer to close this thread, and then others who run into the same issue would find the solution easily.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, March 15, 2016 6:19 AM