Muokkaa

Jaa

Custom functions naming and localization

  • Artikkeli

This article describes guidelines and best practices for naming custom functions. It also shows how to localize custom function names to languages other than English.

Custom functions naming guidelines

A custom function is identified by an id and name property in the JSON metadata file.

  • The function id is used to uniquely identify custom functions in your JavaScript code.
  • The function name is used as the display name that appears to a user in Excel.

Important

Note that Excel custom functions are available on the following platforms.

  • Office on the web
  • Office on Windows
    • Microsoft 365 subscription
    • retail perpetual Office 2016 and later
    • volume-licensed perpetual Office 2021 and later
  • Office on Mac

Excel custom functions aren't currently supported in the following:

  • Office on iPad
  • volume-licensed perpetual versions of Office 2019 or earlier on Windows

A function name can differ from the function id, such as for localization purposes. In general, a function's name should stay the same as the id if there is no reason for them to differ.

A function's name and id share some common requirements.

  • A function's id may only use characters A through Z, numbers zero through nine, underscores, and periods.

  • A function's name may use any Unicode alphabetic characters, underscores, and periods.

  • Both function name and id must start with a letter and have a minimum limit of three characters.

Excel uses uppercase letters for built-in function names (such as SUM). Use uppercase letters for your custom function's name and id as a best practice.

A function's name shouldn't be the same as:

Naming conflicts

If your function name is the same as a function name in an add-in that already exists, the #REF! error will appear in your workbook.

To fix a naming conflict, change the name in your add-in and try the function again. You can also uninstall the add-in with the conflicting name. Or, if you're testing your add-in in different environments, try using a different namespace to differentiate your function (such as NAMESPACE_NAMEOFFUNCTION).

Best practices

  • Consider adding multiple arguments to a function rather than creating multiple functions with the same or similar names.
  • Avoid ambiguous abbreviations in function names. Clarity is more important than brevity. Choose a name like =INCREASETIME rather than =INC.
  • Function names should indicate the action of the function, such as =GETZIPCODE instead of ZIPCODE.
  • Consistently use the same verbs for functions which perform similar actions. For example, use =DELETEZIPCODE and =DELETEADDRESS, rather than =DELETEZIPCODE and =REMOVEADDRESS.
  • When naming a streaming function, consider adding a note to that effect in the description of the function or adding STREAM to the end of the function's name.

Tip

If you'll be testing your add-in across multiple environments (for example, in development, staging, demo, etc.), we recommend that you maintain a different manifest file for each environment. In each manifest file, you can:

  • Specify the URLs that correspond to the environment.
  • Customize metadata values like DisplayName and labels within Resources to indicate the environment, so that end users will be able to identify a sideloaded add-in's corresponding environment.
  • Customize the custom functions namespace to indicate the environment, if your add-in defines custom functions.

By following this guidance, you'll streamline the testing process and avoid issues that would otherwise occur when an add-in is simultaneously sideloaded for multiple environments.

Localize custom functions

You can localize both your add-in and your custom function names. To do so, provide localized function names in the functions' JSON file and locale information in the add-in only manifest file.

Important

Autogenerated metadata doesn't work for localization so you need to update the JSON file manually. To learn how to do this, see Manually create JSON metadata for custom functions.

Localize function names

To localize your custom functions, create a new JSON metadata file for each language. In each language JSON file, add name and description properties in the target language. The default file for English is named functions.json. Use the locale in the filename for each additional JSON file, such as functions-de.json to help identify them.

The name and description appear in Excel and are localized. However, the id of each function isn't localized. The id property is how Excel identifies your function as unique and shouldn't be changed once it is set.

Important

Avoid giving your functions an id or name that is a built-in Excel function in another language as this conflicts with localized functions.

The following JSON shows how to define a function with the id property "MULTIPLY". The name and description property of the function is localized for German. Each parameter name and description is also localized for German.

{
    "id": "MULTIPLY",
    "name": "SUMME",
    "description": "Summe zwei Zahlen",
    "helpUrl": "http://www.contoso.com",
    "result": {
        "type": "number",
        "dimensionality": "scalar"
    },
    "parameters": [
        {
            "name": "eins",
            "description": "Erste Nummer",
            "dimensionality": "scalar"
        },
        {
            "name": "zwei",
            "description": "Zweite Nummer",
            "dimensionality": "scalar"
        },
    ],
}

Compare the previous JSON with the following JSON for English.

{
    "id": "MULTIPLY",
    "name": "Multiply",
    "description": "Multiplies two numbers",
    "helpUrl": "http://www.contoso.com",
    "result": {
        "type": "number",
        "dimensionality": "scalar"
    },
    "parameters": [
        {
            "name": "one",
            "description": "first number",
            "dimensionality": "scalar"
        },
        {
            "name": "two",
            "description": "second number",
            "dimensionality": "scalar"
        },
    ],
}

Localize your add-in

After creating a JSON file for each language, update your add-in only manifest file with an override value for each locale that specifies the URL of each JSON metadata file. The following manifest XML shows a default en-us locale with an override JSON file URL for de-de (Germany). The functions-de.json file contains the localized German function names and IDs.

<DefaultLocale>en-us</DefaultLocale>
...
<Resources>
     <bt:Urls>
        <bt:Url id="Contoso.Functions.Metadata.Url" DefaultValue="https://localhost:3000/dist/functions.json"/>
          <bt:Override Locale="de-de" Value="https://localhost:3000/dist/functions-de.json" />
        </bt:url>
        
     </bt:Urls>
</Resources>

For more information on the process of localizing an add-in, see Localization for Office Add-ins.

Next steps

Learn about error handling best practices.

See also