Skip to content

Latest commit

 

History

History
150 lines (110 loc) · 6.76 KB

custom-functions-naming.md

File metadata and controls

150 lines (110 loc) · 6.76 KB
title description ms.date ms.localizationpriority
Naming and localization for custom functions in Excel
Learn requirements for names of Excel custom functions and how to localize custom functions.
02/29/2024
medium

Custom functions naming and localization

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.

[!includeExcel custom functions note]

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.

[!includemanifest guidance]

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 XML 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 XML 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