Custom functions (similar to user-defined functions, or UDFs) are JavaScript functions that you can add to Excel. This sample accompanies the Custom Functions Overview topic.
- Change History
- Prerequisites
- To use the project
- Making changes
- Debugging
- Intellisense for the JSON file in Visual Studio Code
- Questions and comments
- Additional resources
- Oct 27, 2017: Initial version.
- April 23, 2018: Revised and expanded.
- May 7, 2018: Updated for the Build preview release of custom functions
- Install Office 2016 for Windows or Mac and join the Office Insider program. You must also have Office build build 9325+ on Windows or 13.329+ on Mac.
Follow these instructions to use this custom function sample add-in:
- Publish the code files (HTML, JS, and JSON) to localhost.
- Replace
http://127.0.0.1:8080
in the manifest file (there are 4 occurrences) with the URL you used, if needed (you might be using a different port number). - Sideload the manifest using the instructions found at https://aka.ms/sideload-addins.
- Test a custom function by entering
=CONTOSO.ADD42
in a cell. - Try the other functions in the sample:
=CONTOSO.ADD42ASYNC
,CONTOSO.ISPRIME
,CONTOSO.NTHPRIME
,CONTOSO.GETDAY
,CONTOSO.INCREMENTVALUE
, andCONTOSO.SECONDHIGHEST
. - Follow @OfficeDev on Twitter for updates, post suggestions to the Excel Add-ins UserVoice page and tag Stack Overflow questions with the custom-functions-excel tag.
If you make changes to the sample functions code (in the JS file), close and reopen Excel to test them.
If you change the functions metadata (in the JSON file), close Excel and delete your cache folder Users/<user>/AppData/Local/Microsoft/Office/16.0/Wef/CustomFunctions
. Then re-insert the add-in using Insert > My Add-ins.
Debugging is only available for asynchronous functions on Excel for Windows currently. To debug:
- Enable script debugging in Internet Explorer (IE > Options > Advanced).
- Trigger an asynchronous function in Excel (like
CONTOSO.ADD42ASYNC
). This step ensures that the asynchronous function process is loaded invisibly and ready for debugging. - Attach a debugger to the hidden iexplore.exe script process (you could use the Windows F12 debugger or Visual Studio).
For intellisense to help you edit the JSON file, follow these steps:
-
Open the JSON file (it has a .json extension) in Visual Studio Code.
-
If you are starting a new file from scratch, add the following to the top of the file:
{ "$schema": "https://developer.microsoft.com/en-us/json-schemas/office-js/custom-functions.schema.json",
-
Press Ctrl+Space and intellisense will prompt you with a list of all items that are valid at the cursor point. For example, if you pressed Ctrl+Space immediately after the
"$schema"
line, you are prompted to enterfunctions
, which is the only key that is valid at that point. Select it and the"functions": []
array is entered. If the cursor is between the[]
, then you are prompted to enter an empty object as a member of the array. If the cursor is in the object, then you are prompted with a list of the keys that are valid in the object.
We'd love to get your feedback about this sample. You can send your feedback to us in the Issues section of this repository.
Questions about Microsoft Office 365 development in general should be posted to Stack Overflow. If your question is about the Office JavaScript APIs, make sure that your questions are tagged with [office-js] and [API].
- Custom Functions Overview
- Office add-in documentation
- More Office Add-in samples at OfficeDev on Github
This project has adopted the Microsoft Open Source Code of Conduct. For more information, see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.
Copyright (c) 2017 Microsoft Corporation. All rights reserved.