search

microsoft / advanced-formula-environment

Create, edit, and reuse formulas in Excel
https://aka.ms/get-afe
MIT License
113 stars 11 forks source link

Constantly getting "The formula uses spilled (dynamic) arrays, which are not yet supported by the debugger." even in simple single-cell references #83

Closed santiago-afonso closed 6 months ago

santiago-afonso commented 6 months ago

Hello. A recent AFE update apparently broke the debugger, which now constantly complains about spilled arrays even in simple single-cell references like you can see in the screenshot. Despite the error, the debugger can in this case show the following steps, but not being able to step into calculations is an issue in other formulas.

image

jack-williams commented 6 months ago

Thanks for the report - cc @NWilson

NWilson commented 6 months ago

Hi @santiago-afonso, we're sorry to hear it's not working for you. We'd like to investigate.

In your example, it's the formula in C22 which is causing the problem, not the LET formula that you've shown. Could you share what is stored in C22? I've tried just now with a variety of simple formulas, but they don't trigger the spilled-array error.

santiago-afonso commented 6 months ago

Cell C22 displays the same spills error in the debugger. Cell C22's formula returns a single value, not an array, and thus doesn't spill. That formula is a LET with several single-cell references and a named range referring to a single cell. The LET itself and every calculation involve just single-value ¿variables? in every case. The only noteworthy feature is that the formula uses an IFS to test several conditions, which might be what causes the issue. Unlike the capture in my first message, the AFE debugger only displays the error message "The formula uses spilled (dynamic) arrays, which are not yet supported by the debugger.". Still, this doesn't spill at all. Using the old Evaluate Formula dialog, at no step do I get an array; all are single values.

Give me an email and I'll share the file if you'd like.

Thanks for the prompt reply!

NWilson commented 6 months ago

Thank you @santiago-afonso that would be really helpful.

If you're able, in the top-right of Excel there should be a feedback button ("Help to improve Office") where you can send & attach files. I'm subscribed to all feedback that mentions the phrase "Advanced Formula Environment". That would let me receive your spreadsheet and use it in compliance with our internal policies.

santiago-afonso commented 6 months ago

I've sent the file, look for the only cell with a comment. Thanks!

NWilson commented 6 months ago

Thank you again Santiago, I am closing this GitHub ticket now.

After investigation, we believe the feature is working as intended, although the error message should be clearer.