microsoft / advanced-formula-environment

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

Report errors for using Excel 4.0 macro function names as defined names #10

Open SergeiStPete opened 2 years ago

SergeiStPete commented 2 years ago

There are some names which we can't use in AFE (Table, Group, Consolidate, etc.) but documentation says nothing about that, at least I didn't find. It's worth to mention, if not list of such names when the rule.

jack-williams commented 2 years ago

Do you mean in the case where we already have a table defined (say Table1) and we try to define a new name in AFE called Table1?

SergeiStPete commented 2 years ago

Nope, I mean

Table=LAMBDA(x, x+1); Group=LAMBDA(x, x+1); Consolidate=LAMBDA(x, x+1);

in empty workbook

SergeiStPete commented 2 years ago

It looks like all Excel 4.0 macro function names

jack-williams commented 2 years ago

Ah I see. It appears those names are fine to have when they refer to values such as 10, but it does not work when they refer to LAMBDA.

Does the built-in name manager give warnings here?

SergeiStPete commented 2 years ago

They are synced with Name Manager, but if we enter in cell something like =Group(2) alert appears "That function isn't valid"

SergeiStPete commented 2 years ago

And yes, they don't work with lambdas only, e.g.

Group = SUM(Sheet1!a1:a10);

works

SergeiStPete commented 2 years ago

@jack-williams , now it doesn't sync if we use reserved name function, but the message is not very informative. For

beep = lambda(x,y,x+y); itWorks = LAMBDA(x,y, (beep)(x,y) ); doesntWork = LAMBDA(x,y, beep(x,y));

we have "Sync names with Excel name manager. failed: The argument is invalid or missing or has an incorrect format." If we have long enough code it's not clear where exactly is an error (doesntWork function in this case) and what is wrong, especially if you don't know about reserved function names.