Excel Custom Functions Straight to the Point
- 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.
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.
Microsoft Script Lab
- Installing and Using Microsoft Script Lab
Installing the Development Tools
- Yeoman Generator for Office Add-ins
- Visual Studio Code
Creating the Office Add-in
- Yeoman Office Generator
Add-in Source Files and File Structures
- The JSON Metadata File—/dist/functions.json
- The Loader HTML File—/src/functions/functions.html
- The Add-in Manifest File
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
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
- What's Next?
- The Final Function Code
About The 'Straight to the Point' SeriesBooks in this series are designed to thoroughly cover one targeted aspect of Excel.