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

Sync names failure #16

Closed timnudecision closed 2 years ago

timnudecision commented 2 years ago

This formula environment is really helpful. Thanks!

I'll see the following error message frequently when I sync code changes from the editor (using the metadata sheets). If I clear the message and sync again, the change is accepted successfully. I see this behavior frequently.

Sync names with Excel name manager. failed: The argument is invalid or missing or has an incorrect format.

using Mac Version 16.61

jack-williams commented 2 years ago

If I clear the message and sync again, the change is accepted successfully.

This is likely because we cache the content to be saved so that sync'ing is fast for many definitions. After the first (failed) sync, when you try again it is probably not sending anything to the name manager and silently succeeding. This is not great behaviour, but the issue is that Excel API does not give us errors for which specific name fails, so we do not know what to cache or not.

Focusing on debugging the initial failure. It could be a number of things. The most common one is that we do not support locale's using ; as the argument separator. Another one is using formulas with the incorrect number of arguments to a function call. For instance: aName = SUM(); will fail because SUM requires at least one argument.

If you are willing and able, feel free to put examples on this thread that were causing you problems, and I can try and take a look.

timnudecision commented 2 years ago

Thanks. Caching explains some other related behavior that I've seen.

About how often are the formulas in the cache saved?

From: Jack Williams @.> Date: Thursday, March 31, 2022 at 10:04 AM To: microsoft/advanced-formula-environment @.> Cc: Tim Johnston @.>, Author @.> Subject: Re: [microsoft/advanced-formula-environment] Sync names failure (Issue #16)

If I clear the message and sync again, the change is accepted successfully.

This is likely because we cache the content to be saved so that sync'ing is fast for many definitions. After the first (failed) sync, when you try again it is probably not sending anything to the name manager and silently succeeding. This is not great behaviour, but the issue is that Excel API does not give us errors for which specific name fails, so we do not know what to cache or not.

Focusing on debugging the initial failure. It could be a number of things. The most common one is that we do not support locale's using ; as the argument separator. Another one is using formulas with the incorrect number of arguments to a function call. For instance: aName = SUM(); will fail because SUM requires at least one argument.

If you are willing and able, feel free to put examples on this thread that were causing you problems, and I can try and take a look.

— Reply to this email directly, view it on GitHubhttps://github.com/microsoft/advanced-formula-environment/issues/16#issuecomment-1084719367, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AOA77UZJQPD2HLVYB7BW6FDVCW5IBANCNFSM5SFS65OA. You are receiving this because you authored the thread.Message ID: @.***>

jack-williams commented 2 years ago

The caching is not time based, but on whether the saved content changes. For example:

Given the editor content

aName = SUM();

We will try and sync that and it will fail because SUM does not have enough arguments. If you hit sync again, we will identify that the text content you are trying to save since you last hit sync is the same, and instantly return "ok". Really, we should only do this caching when the last sync was successful. We cache per name, but the API does not give us sync failures per name, which causes a little trouble. We might want to clear the whole cache if any name fails, but that might be a performance hit.

As to you why you are hitting sync errors is the first place, I see that you are on Mac. I know that on some versions of Mac the length of defined names is very small, meaning formulas get truncated and are likely incorrect. Does this happen when all the names you are saving are very short?

timnudecision commented 2 years ago

20 characters is the longest I have. I haven't added namespaces yet.

jack-williams commented 2 years ago

@timnudecision

I have an upcoming change that will no longer cache when there is an error in saving the names. This will remove the misleading user experience where you:

  1. Sync and get an error message
  2. Clear the error message
  3. Sync again and get no error message.

I suspect this is not a complete fix for the issues you are experiencing, but it is an improvement.

Ideally, you should see red squiggles in a formula that will trigger a sync error. Anytime there are no squiggles, this is something we should look to track and potentially fix.

I'm going to close this issue. If you find specific formulas that fail the red squiggle check as described above, feel free to create a new issue.