Excel Custom Functions Straight to the Point
July 2019
I've been using VBA to automate Excel for decades and I've written a number of books about Excel VBA. But there is a new language available for Excel and it offers a cool trick: You can use Excel Custom Functions written in JavaScript on the Mac, on Windows, and something new: in Excel Online. Microsoft CEO Satya Nadella is betting big that Excel Online will be the next big thing. Getting up to speed with these Custom Functions will allow you to build solutions that will work cross-platform.
Add to Cart: $5.99 »- 41 Pages
- Publisher: Holy Macro! Books
- PDF ISBN: 978-1-61547-259-8
Excel users are used to create user defined functions (UDFs)—in Windows and Mac versions of the application. User defined functions can be simply written in Visual Basic Editor (VBE) using the Visual Basic for Applications (VBA) language.
User defined functions 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 functions, Excel Custom Functions, can be used like any other native or user defined functions in Excel.
The most beautiful aspect of this new functionality is that developers can take advantage of the power of widely used web services in Excel Custom Functions, and the end user doesn't even have to know about web application development.
This book starts with demonstrating Excel Custom Functions in Microsoft ScriptLab Office add-in, and shows the process of creating and debugging Excel Custom Functions by using Visual Studio Code and Node.js.
-
Quick Start
-
Microsoft Script Lab
- Installing and Using Microsoft Script Lab
-
-
Getting Prepared
-
Installing the Development Tools
- Node.js
- Git
- Yeoman Generator for Office Add-ins
- Visual Studio Code
-
Creating the Office Add-in
- Yeoman Office Generator
-
-
Getting Familiar
-
Add-in Source Files and File Structures
- The JavaScript Functions File—/src/functions/functions.js
- The JSON Metadata File—/dist/functions.json
- The Loader HTML File—/src/functions/functions.html
- The Add-in Manifest File
-
-
Warming Up
-
Creating a New Function
- JSDoc Tags
- Function Body
- Function Name Association
-
Building the Add-in and Testing the Function
- Testing the Custom Function in Excel Online
- Creating a New Function to Accept a Range as a Parameter
-
Debugging the Custom Function
-
-
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
- Batching Web Service Calls as a Better Alternative to Array Formula
- Creating a Streaming Custom Function
- Returning Two-Dimensional Array from a Streaming Function
-
-
Wrapping Up
-
Final Words
- What's Next?
- The Final Function Code
-
About The 'Straight to the Point' Series
Books in this series are designed to thoroughly cover one targeted aspect of Excel.
