Curly brackets being added to UNIQUE function as soon as the file is opened by Flexcel

The following is a test scenario for demonstration purposes.

As you can see in the file Unique Test 1 which was created on Office 365 Microsoft Excel.

Unique Test 1.xlsx (10.6 KB)

I am using the following function: =UNIQUE(FILTER(A:A,A:A<> "")) on Cell C1. The result is that every time you add or modify a value on Column A, then Column C parallel Cell will be updated, this works perfectly on Excel.


For some reason when the same file is opened using FlexCel, automatically curly brackets are added at the beginning and the end of the function as follows {=UNIQUE(FILTER(A:A,A:A<> ""))}, as you can see on the file Unique Test 2 which was saved just after been opened by FlexCel.

Unique Test 2.xlsx (7.3 KB)

This breaks the behavior because adding a new value on Column A, for example "Test4" on cell A4 won't update cell C4.


Can you please help me with this?

I am using .NET FlexCel version 7.15.0

Thanks in advance.

In short, the problem is that FlexCel doesn't support yet dynamic arrays. (see Dynamic array formulas in Excel | Exceljet ). So FlexCel works as if it was office 2019, or libreoffice. (you'll get the same curly brackets if you open a file created by Excel in any older Excel version).

Going more in depth, Dynamic arrays are a "feature that breaks everything", which to me is a first in all Excel history. The feature in itself isn't that hard to implement, in fact, stuff like the lambda functions we already implemented is harder. But lambda functions are just an addition to existing stuff, they don't break it. Dynamic arrays change basic assumptions to the spreadsheet, stuff like "A cell either has a value or it hasn't" now is not valid anymore.

Some cells now have " Schrödinger values" and they might or not might have a value depending on what you want them for. In your example, cell C2 is grey in Excel, and if you are printing it, it has a value (test1). But if you are copying it to another sheet, it doesn't, because the formula is only in cell C1. So what should GetCellValue return for cell C2? The answer is "it depends", and we might need 2 different GetCellValue (one that returns test2, the other returns empty), and you need to know which one to call depending in what you want the value for. Or have GetCellValue return a "special" value for C2, so you can know it is not a real value.

There are other things that break, like for example array intersection, which was automatic before, but now it is not anymore. This means that just opening an older file in Excel 2021 could break it. So what does Excel do? It silently converts the file when you open it, so, in most cases, you will not notice (unless you want to open the file back in the older Excel version, or other spreadsheet like libreoffice).

So now in our side we have 2 problems: On one side the stuff that breaks (and that could cause old FlexCel code to break, even if you are not using and don't care about dynamic arrays at all). And on the other side the silent conversion Excel does to the file.

About the brackets particularly, it is not FlexCel that adds them, they are always there. Excel 365 adds them so programs not aware of dynamic arrays can still display the formulas, but array formulas don't expand like dynamic arrays. When Excel 365 reopens the file, it knows it was saved by a version of Excel that supports dynamic arrays, so it will convert those array formulas back to dynamic arrays.

(just a note: array formulas are formulas that take more than one cell, and they have those curly brackets. They have existed forever in Excel and FlexCel fully supports them. Dynamic arrays are the new stuff with formulas that automatically spill to their neighbors)

FlexCel will not save a flag in the file "the app that wrote this file supports dynamic arrays" (because we don't), so when you open it again in Excel, it won't convert the array formulas to dynamic arrays.

We've been working a good part of this year in supporting dynamic arrays, but as said, our problem is not so much in the code to support them, but in the fact that we might need to break basic stuff in FlexCel to support them. For example, GetCellValue won't return an object anymore, but another class that can support Schrödinger values. But really, releasing a 8.0 version that will break your old code in return for dynamic arrays is not something we want to do. Specially because dynamic arrays are nice and flashy, but not that useful in real life, and in my humble opinion, not worth breaking existing stuff.

So we are still trying to see how we minimize the breaking changes (there is no way to have no breaking changes at all), in order to release support for dynamic arrays. Given the breaking nature of the release it won't even be a 7.x release anymore, it will be 8. So you know code might break. And that is something we really don't want to do, because we are religious in keeping FlexCel backwards compatible, to us if you update from 7 to 8 and now your code doesn't run anymore, that is a bug. It must run the same as before, we can't just break your existing apps. So that's the reason this is taking long, and it will still take some more time.

While this work is being done in a flexcel 8 branch, some stuff like support for the new functions like "unique" or "sort" was backported to 7.x, because while those functions are mostly useful when you have dynamic arrays, they don't require them, and can be used in normal array formulas too (the ones with the curly brackets) or even in simple formulas.

1 Like

Hello Adrian, thanks for answering and taking the time to explain the whole situation. I understand the reasons why the dynamic arrays feature hasn't been added to FlexCel. I'll check other ways of doing what I am trying to achieve.

Anyways, I will look forward to the next mayor version of the library.

Thanks again.