Create custom functions in Excel

Artikel
11/11/2024

Custom functions enable developers to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions just as they would any native function in Excel, such as SUM().

Note

Custom function is a general term that is interchangeable with user-defined function. Both terms apply to VBA, COM, and Office.js add-ins. The Office Add-ins documentation uses the term custom function when referring to custom functions that use Office JavaScript APIs.

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

The following animated image shows your workbook calling a function you've created with JavaScript or TypeScript. In this example, the custom function =MYFUNCTION.SPHEREVOLUME calculates the volume of a sphere.

Animated image showing an end user inserting the MYFUNCTION.SPHEREVOLUME custom function into a cell of an Excel worksheet.

The following code defines the custom function =MYFUNCTION.SPHEREVOLUME.

/**
 * Returns the volume of a sphere.
 * @customfunction
 * @param {number} radius
 */
function sphereVolume(radius) {
  return Math.pow(radius, 3) * 4 * Math.PI / 3;
}

How a custom function is defined in code

If you use the Yeoman generator for Office Add-ins to create an Excel custom functions add-in project, it creates files which control your functions and task pane. We'll concentrate on the files that are important to custom functions.

File	File format	Description
./src/functions/functions.js or ./src/functions/functions.ts	JavaScript or TypeScript	Contains the code that defines custom functions.
./src/functions/functions.html	HTML	Provides a <script> reference to the JavaScript file that defines custom functions.
./manifest.xml	XML	Specifies the location of multiple files that your custom function use, such as the custom functions JavaScript, JSON, and HTML files. It also lists the locations of task pane files, command files, and specifies which runtime your custom functions should use.

Tip

The Yeoman generator for Office Add-ins offers multiple Excel Custom Functions projects. We recommend selecting the project type Excel Custom Functions using a Shared Runtime and the script type JavaScript.

Script file

The script file (./src/functions/functions.js or ./src/functions/functions.ts) contains the code that defines custom functions and comments which define the function.

The following code defines the custom function add. The code comments are used to generate a JSON metadata file that describes the custom function to Excel. The required @customfunction comment is declared first, to indicate that this is a custom function. Next, two parameters are declared, first and second, followed by their description properties. Finally, a returns description is given. For more information about what comments are required for your custom function, see Autogenerate JSON metadata for custom functions.

/**
 * Adds two numbers.
 * @customfunction 
 * @param first First number.
 * @param second Second number.
 * @returns The sum of the two numbers.
 */

function add(first, second){
  return first + second;
}

Tip

In Excel on the web, custom function descriptions and parameter descriptions display inline. This gives users additional information when writing custom functions. Learn how to configure the inline descriptions by exploring any of the custom functions Script Lab samples in Excel on the web. See the following screenshot for an example.

A custom function with inline descriptions displayed in Excel on the web.

Manifest file

The add-in only manifest file for an add-in that defines custom functions (./manifest.xml in the project that the Yeoman generator for Office Add-ins creates) does several things.

Defines the namespace for your custom functions. A namespace prepends itself to your custom functions to help customers identify your functions as part of your add-in.
Uses <ExtensionPoint> and <Resources> elements that are unique to a custom functions manifest. These elements contain the information about the locations of the JavaScript, JSON, and HTML files.
Specifies which runtime to use for your custom function. We recommend always using a shared runtime unless you have a specific need for another runtime, because a shared runtime allows for the sharing of data between functions and the task pane.

To see a full working manifest from a sample add-in, see the manifest in the one of our Office Add-in samples Github repositories.

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.

Coauthoring

Excel on the web and on Windows connected to a Microsoft 365 subscription allow end users to coauthor in Excel. If an end user's workbook uses a custom function, that end user's coauthoring colleague is prompted to load the corresponding custom functions add-in. Once both users have loaded the add-in, the custom function shares results through coauthoring.

For more information on coauthoring, see About coauthoring in Excel.

Next steps

Want to try out custom functions? Check out the simple custom functions quick start or the more in-depth custom functions tutorial if you haven't already.

Another easy way to try out custom functions is to use Script Lab, an add-in that allows you to experiment with custom functions right in Excel. You can try out creating your own custom function or play with the provided samples.

Delen via