Excel JavaScript UDFs Straight to the Point
June 2018
After twenty-five comfortable years of writing VBA in Excel, the writing is on the wall: A new language is coming to Excel developers. Starting in May 2018, User-Defined-Functions written in JavaScript will run cross-platform. What you write for Excel for Windows will also work in Excel Online. JavaScript is a new thing for those of us comfortable with VBA. This book will get you up to speed on writing and deploying JavaScript UDFs.
- 41 Pages
- Publisher: Holy Macro! Books
- PDF ISBN: 978-1-61547-247-5
Notice
In the spring of 2019, Microsoft released the Excel Custom Functions feature in production. Therefore, we rewrote the book with the up-to-date and stable release content. Click here to see the new book: Excel Custom Functions Straight to the Point
Excel users are used to build custom functions—called user-defined functions (UDFs)—in Windows and Mac versions of the application. UDFs can be simply written in Visual Basic Editor (VBE) using the Visual Basic for Applications (VBA) language.
UDFs are not portable to Excel Online and Excel for iOS due to the missing VBA capability on these platforms. Therefore, Microsoft has improved the JavaScript API that was already being used for Office add-in development to allow developers to create custom functions attached to the add-ins. These custom functions can be used like any other native functions or UDFs in Excel. While Microsoft calls these functions JavaScript custom functions, they can also be called JavaScript UDFs, which is the name used in this book.
This book shows the process of creating JavaScript UDFs in Excel in Developer Preview for Office Insider program subscribers.
-
The Office Insider Program
-
Joining the Office Insider Program
- Downloading and Installing Office Insider
- Signing Up for the Office Insider Program
-
-
Getting Familiar
-
Add-in Source Files and File Structures
- The Loader HTML File—udfs.loader.html
- The JSON Metadata File—udfs.metadata.js
- The JavaScript Functions File—udfs.functions.js
- The Add-in Manifest File—udfs.manifest.xml
-
-
Warming Up
-
Setting Up the Environment
- Sharing the Catalog Folder on the Network
- Adding the Trusted Add-in Catalog in Excel
-
Installing and Testing the JavaScript Add-in
- Inserting the Excel JavaScript UDFs Add-in in Excel
- Testing the Add-in
- About Caching the Source Files
-
Extending the Add-in
- Creating a New Function to Accept a Range as a Parameter
-
-
Getting Confident
-
Fetching Data from Web Services
- Using a Web Service for Stock Prices
- Adding an Asynchronous Function
- Fetching Multiple Symbol Prices with a Single Web Service Call
-
Adding a Final Touch
- Creating a Streamed Function
-
-
Wrapping Up
-
Final Words
- What's Next?
- The Final Function Code
-
-
Appendix
-
Hosting Source Files Locally
- Installing IIS on Windows 7
- Adding MIME Type for .json Extension
- Copying Sources Files to the Local Server
- Updating the Manifest File
-
About The 'Straight to the Point' Series
Books in this series are designed to thoroughly cover one targeted aspect of Excel.Updates
- 2019-05-11
Sample files in the source folder provided in the book has been updated with the recent changes applied by Microsoft.
-
2018-09-25: Office JavaScript library and method change.
-
Custom Functions developers at Microsoft switched to a dedicated JavaScript library.
udfs.loader.html file, line 9:
Changed from:
<script src="https://unpkg.com/@microsoft/office-js@1.1.7-adhoc.3/dist/office.js" type="text/javascript"></script>to:
<script src="https://officedev.github.io/custom-functions/lib/custom-functions-runtime.js" type="text/javascript"></script> -
Instead of OfficeExtension.Promise method, Promise object is used alone.
udfs.functions.js file, line 26 and 58:
Changed from:
return new OfficeExtension.Promise(function(setResult, setError){to:
return new Promise(function(setResult, setError){
-
