Forum Discussion

papa_austin's avatar
papa_austin
Copper Contributor
Jun 16, 2022

CSE formula does not calculate correctly using application.calculateFull (or other variations)

Hello, 

Excel version -  Office 365. 

In the attached workbook, I have an array formula that is entered as CSE (Ctrl + Shift + Enter). 

It is a relatively simple formula that is a combination of filter, and index functions. 

 

When I press F9, I get the correct result (I have auto calculate turned off).

However, when I use a UDF that executes the formula using application.calculatefull (or any other variations of it), the result is not correct (it spills the first value for all of the years).

 

This appears to be some kind of Excel calculation engine error.

 

I am not asking how to work around the issue in the example.

I am asking if this is a known issue with CSE formulas and application.calculate incompatibility.

Have there been publications (by MS or blogs) on this? Has anybody seen this issue before? 

 

Thank you

excel
Formulas and Functions
office 365
  • amit_bhola's avatar
    amit_bhola
    Iron Contributor
    Jun 17, 2022

    papa_austin , it seems that the VBA way of refreshing the CSE formula values on the grid is to set the formula again.

    Issue might not be with the calculation engine but with the refreshing of the grid for CSE formulas.

    Setting the formula again forces excel to refresh the grid, perhaps?

     

    This works :-

    Public Sub AppCalc()

    Application.CalculateFull
    
    'Set CSE formula again
    With Range("$B$26:$L$26")
        .Formula = .Formula
    End With

End Sub

     

     

    • papa_austin's avatar
      papa_austin
      Copper Contributor
      Jun 17, 2022

      Thank you!

      This appears to be a bug with Excel (application.calculate causes an unexpected error).

      I'm interested in finding out if this is a known bug. Like has MS reported this before? Or any blogs?
      It matters because my workplace has many examples like this. I would like to know if I need to alert my co-workers of this bug. It would be helpful in my communication if this was a known bug that is documented.

      If not, what would be a way to get hold of MS Excel team's attention on this?

      • amit_bhola's avatar
        amit_bhola
        Iron Contributor
        Jun 17, 2022

        papa_austin , not sure if this behavior should be labelled as a bug . Sure it is not as per user expectation because this behavior of CSE formulas is different from non CSE formulas. But then, manual (calculation) mode and CSE formulas too are quite unique scenarios in themselves and have  some / may have some lesser known unique behaviors associated with them, like this one, which are not so well documented.

        As for your query, sorry I'm not aware of any official documentation or blog which lists the issue at hand.

        Below portal may be of some help to share feedback to MS

        https://feedbackportal.microsoft.com/feedback/forum/c23f3b77-f01b-ec11-b6e7-0022481f8472

         

Resources