Excel: impossible to set named item referring to multiple cells in a portable way

[wh1t3cAt1k]

Our add-in sometimes needs to create (and then work with) named items that refer to multiple cells or areas, however it's not possible to do it in a clean and portable way!

The list separator character differs on different platforms, most frequently ";" or ",". However, Office.js is opaque regarding which separator is actually used.

Expected behavior

A possibility to retrieve the list separator! Excel's C API exposes that possibility.

Current behavior

We have to use this workaround to detect the separator character. It's ugly and non-performant.

try {
        const refersToFormula = `=A1,A2`;

        namedItem = worksheet.names.add(separatorTestName, refersToFormula);

        await context.sync();

        separator = ',';
        return separator;
    } catch {
        separator = ';';
        return separator;
    } finally {
        namedItem?.delete();
    }

Steps to reproduce

Use the script above for an arbitrary worksheet - on systems with ";" list separator the block will catch, on other systems there will be no exception.

Context

We want to write code without kludges!

[wh1t3cAt1k]

Maybe it's a platform specific bug? Our list separator is set to ";" on a Canadian Mac, where Excel for Mac is installed.

And we cannot use the "," separator there when setting refersToFormula.

However apparently we should be, according to this answer: https://stackoverflow.com/a/37398895/499206

It would be ideal if we could simply use , everywhere and it was interpreted correctly as the list separator. Alternatively, we're fine with Office.js team exposing the list separator API for us.

[jipinshi]

@wh1t3cAt1k, thanks reporting the issue, can you share the complete sample script gist which is used to repro the issue? I tried above script and try to run in scriptlab in win32 and failed.

[wh1t3cAt1k]

@jipinshi @JinghuiMS

Apologies, I thought it would be enough to share the relevant bits - we just need to add the Excel.run invocation and then get the first worksheet of the workbook into the worksheet variable.

Anyway, I am happy to amend the script to a full example for you.

[wh1t3cAt1k]

@jipinshi @JinghuiMS just battle-tested the full snippet, here it is:

$("#run").click(() => tryCatch(run));

async function run() {
  const separatorTestName = "VelixoSeparatorTest";

  let separator;

  await Excel.run(async (context) => {
    context.workbook.worksheets.load('items');

    await context.sync();

    const worksheet = context.workbook.worksheets.items[0];

    let namedItem = worksheet.names.getItemOrNullObject(separatorTestName);

    await context.sync();

    if (!namedItem.isNullObject) {
      namedItem.delete();
    }

    namedItem = undefined;

    try {
      const refersToFormula = `=${worksheet.name}!A1,${worksheet.name}!A2`;

      namedItem = worksheet.names.add(separatorTestName, refersToFormula);

      await context.sync();

      separator = ",";
    } catch {
      separator = ";";
    } finally {
      if (namedItem !== undefined) {
        namedItem.delete();
      }
    }

    console.log(`The separator is ${separator}`);
  });
}

/** Default helper for invoking an action and handling errors. */
async function tryCatch(callback) {
  try {
    await callback();
  } catch (error) {
    // Note: In a production add-in, you'd want to notify the user through your add-in's UI.
    console.error(error);
  }
}

[wh1t3cAt1k]

To reiterate, the above is what we currently have to use to understand which separator character we should utilize in names.add with a formula referring to multiple ranges.

This is a hack at best, ideally, we should either be able to retrieve the list separator character through the API or be able to use a universal character like , consistently on all cultures.

[jipinshi]

@wh1t3cAt1k ， thanks for the info. names.add only accept English version like "," while in other language like German will use ";". For the requirement, not sure whether it is one continus area like A1:A5, or B1:C5, If it is , you can use : to do it. Tried English language on both MAC and win32, both respect "," as separator. Let me know whether this is useful for you.

[wh1t3cAt1k]

@RuizhiSunMS @jipinshi I'm afraid that does not help at all in our scenario.

I understand that different cultures use different separator, but this is precisely the problem, that we don't know through Excel API what this separator is. And we need to apply hacks to understand it.

I suspect it is a culture-specific issue on Excel for Mac.

We have users in Canada (French), the list separator in the system culture is set up as ";" for them.

In this case, using "," as list separator in the formula does not work, and in our hack, the execution flow goes to the catch block:

However, this is just a hack - in reality the user can set up anything as their list separator in the system's culture settings. Furthermore this workaround is non-performant.

Additional info:

We cannot avoid using the separator because in our particular scenario, we need to save several disjointed areas into a single named item, therefore the need to use a formula separator (formula like =Sheet1!A1:A9,Sheet1!C1:C9).

Furthermore, we need to use a single named item for reasons of compatibility with our legacy XLL-based product.

To reiterate, one of the two expected results for us would be:

a) Allow retrieving the list separator character through the Office.js API, to avoid using hacks. b) and/or always allow using "," in formula separators, even on systems where ";" is set up as a list separator in the culture settings.

[jipinshi]

@wh1t3cAt1k , can you try below script to get the seperator? select like A3 range, range.formulasLocal[0][0].toString().substring(7,8) should be the Seperator you need here.

async function run() { await Excel.run(async (context) => { const range = context.workbook.getSelectedRange(); range.load(); await context.sync(); range.formulas = [["=SUM(A1, A2)"]]

console.log( range.formulasLocal); range.load(); await context.sync(); console.log(range.formulasLocal[0][0].toString().substring(7,8)); }); }

[wh1t3cAt1k]

@jipinshi if I am not mistaken, this is not in principle different from the hack I posted above.

I would expect it will still fail on the cultures with the ";" list separator. Also it messes with the user's selected range which is not acceptable...

Could we ask for a proper fix to the problem instead, or at least a predictable way to determine the list separator?

Looping in @sshukurov @JinghuiMS @RuizhiSunMS .

[jipinshi]

@wh1t3cAt1k , will open bug to track it. After getting the list separator, could remove the range value. Which will not impact customer data. Even with cultures with the ";", it won't fail. I tested with windows changing the number Seperator from "," to ";" and it works. The other way to do it is getting two ranges and set two values, usually range means one joint area.

[wh1t3cAt1k]

@jipinshi for backward compatibility with our legacy XLL offering it needs to be all in one range 😢 Thanks for opening the bug, I appreciate it! 🙏 Will this ticket be updated with regards to this?

[sshukurov]

I believe NamedItemCollection.add() as well as any other API method expecting a range address as parameter (such as Worksheet.getRange()) should consistently accept the culture-agnostic (invariant) notation of the address, where the expected formula language would be English with , being separator, regardless of the content language used.

I think this answer on SO is also in line with the thought above.

Having to mess with identifying the correct separator is a pain for developers, and when there is no formal API for detecting the separator then that pain doubles, to say the least.

I'd ask Office JS team to carefully review this and assign it a relevant priority.

[JinghuiMS]

@wh1t3cAt1k
I'd like to know which platform you hit this issue?

@wh1t3cAt1k @sshukurov Sorry for the convenience caused to you. We can totally understand your concerns! We are also in favor of adding new APIs(like:worksheet.names.addLocal) to address such problems, because it will bring potential risks to customers if we easily change the behavior of these released APIs. If you have no further comments/concerns, I will create one new feature ask for such similar issues. At the same time, for new feature request, we would like to suggest you go to https://techcommunity.microsoft.com/t5/microsoft-365-developer-platform/idb-p/Microsoft365DeveloperPlatform and see if your question has already been asked or ask a new question. The number of upvotes is also a basis for scheduling our work. Thanks again for reporting your issue!

[jkpieterse]

The root cause of this issue is that the formula property returns and accepts formulas in localized syntax, rather than in US syntax.

This is contrary to how the range object works, which has a formulas property that reads and writes us syntax formulas and a formulasLocal which reads and writes localized formulas.

There are more objects which have similar issues as the namedItem object. IIRC Validation also has this issue and FormatConditions too.

I would strongly prefer the way localization works with the formula(s) property OF ALL OBJECTS to be the same as the Range object.