Forum Discussion

Beastian's avatar
Beastian
Copper Contributor
Dec 16, 2022

Dynamic array formula call with spill range input parameter crashes Excel; legacy array call works

Hi,

 

I have a workbook with a dynamic range formula call referencing a spill range that worked last week but is suddenly causing excel to crash this week when using a full worksheet re-calc on a specific machine. This workbook works on multiple other machines but something on that machine is causing this formula to break.

 

1) Excel version is 2211 (Build 15831.20208 Click-to-run). I have tried an excel repair already on that specific machine.

 

2) Shift+F9 on the offending worksheet hangs excel with the status bar showing:

 

"calculating (spill resize pass 1)"

 

stuck at 66%. Pressing escape allows me to regain control, but a subsequent Shift+F9 crashes the excel process entirely.

 

3) The legacy Ctrl+Shift+Enter method of using the formula with a defined output range allows Shift+F9 to work on that worksheet without any issue.

 

4) Calculating the rest of the page separately without the offending formula, then inputting the offending dynamic range formula also works.

 

5) Other machines on the identical version of Excel recalculate the sheet instantaneously.

 

5) I've tried to disable things such as endpoint security services to no avail.

 

Is there anyone who has encountered this behavior before and/or can tell me how to diagnose better the issue?

excel
Formulas and Functions

4 Replies

Sort By
  • Lars11ave's avatar
    Lars11ave
    Copper Contributor
    Feb 24, 2023
    I am having the same issue with Dynamic Spill ranges, which seems to happen for me when toggling between automatic and manual calculations and the shift+F9 with dynamic array functions, it freezes you can get out of that then it crashes with a next action, it happens on multiple workbooks but inconsistently as it seems to allow the toggling and shift+F9 for a certain amount of time. I have done repairs on the excel installation, rebuilt workbooks but it still occurs. It started to occur around 2 weeks ago.
  • Johan_Ras's avatar
    Johan_Ras
    Copper Contributor
    Jan 17, 2023

    Beastian I have exactly the same issue and I cannot seem to find anyone that could solve this. Did you get an answer yet?

    • ChrisVW's avatar
      ChrisVW
      Copper Contributor
      Jan 28, 2023

      Johan_RasI am having the same problem and I also don't see any response from Microsoft. This started happening in late December 2022 in all my spreadsheet applications that have dynamic arrays. The ActiveSheet.Calculate command will no longer execute and Excel crashes.

      • MarcoFilocamo's avatar
        MarcoFilocamo
        Copper Contributor
        Feb 02, 2023

        I have the exact same problem you are describing - as of now, nothing has worked and it's driving me crazy.

        It does not seem there is much on this topic either (https://www.mrexcel.com/board/threads/calculations-hang-up-freeze-issue-spill-resize.1139035/) and it does not seem to correlate with the number of calculations or arrays in a single sheet, sometimes I see this with quite basic formulas with just 100s of rows.

         

        EDIT: one thing I noticed is that CHOOSECOLS is particularly prone to generating this kind of problem (maybe it's only me). Removing this formula with some workarounds has removed this issue from one of the sheets. I also noticed that the issue arose when there were some names that were either referring to another file or to a sheet that was not present on the file.

Resources