Excel Custom Functions Straight to the Point E-Book

Suat M Ozgur

• Herausgeber: Packt Publishing
• Kategorie: Lebensstil
• Sprache: Englisch

Seitenzahl: 48

Veröffentlichungsjahr: 2024

Beschreibung

This book is a step-by-step guide for developers to build custom Excel functions with JavaScript. Beginning with the installation of tools like Node.js and Git, it explores setting up Microsoft Script Lab, building add-ins, and understanding essential file structures. Readers will learn to create and debug custom functions, handle asynchronous data, and integrate streaming web services.
The book progresses through practical examples, teaching developers to fetch data from APIs and use advanced techniques like batching and streaming for real-time Excel updates. It demystifies the JSON manifest, JavaScript function files, and debugging processes essential for successful add-in development.
By the end, readers will have a robust understanding of creating custom Excel functions, optimizing workflows, and integrating dynamic web services. Whether you're an Excel enthusiast or an experienced developer, this book offers a practical, engaging pathway to enhance Excel functionality through coding.

Leseprobe

Straight to the Point

The Straight to the Point e-books are designed to thoroughly cover one targeted aspect of Excel.

EXCEL CUSTOM FUNCTIONSStraight to the Point

Suat M. Ozgur

Holy Macro! Books

PO Box 541731, Merritt Island FL 32953

Excel Custom Functions

© 2019 by Suat M. Ozgur

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information or storage retrieval system without written permission from the publisher.

All terms known in this book known to be trademarks have been appropriately capitalized. Trademarks are the property of their respective owners and are not affiliated with Holy Macro! Books

Every effort has been made to make this book as complete and accurate as possible, but no warranty or fitness is implied. The information is provided on an “as is” basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.

First Published: July 2019

Author: Suat M. Ozgur

Cover Design: Shannon Travise, Creative Correspondence

Cover Illustration: Shannon Mattiza, 6'4 Productions

Technical Editor: Bill Jelen

Published by: Holy Macro! Books, PO Box 541731, Merritt Island, FL 32953

Distributed by Independent Publishers Group, Chicago, IL

ISBN 978-1-61547-259-8

Table of Contents

Introduction

About the Author

About This Book

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

This page intentionally left blank

Introduction

About the Author

Suat M. Ozgur has worked behind the scenes with MrExcel.com since 2002. He has been using JavaScript since 2005. When Microsoft introduced the new Excel Custom Functions, he was uniquely qualified to work with them, thanks to his experience in both JavaScript and Excel.

Suat is the author of Excel JavaScript UDFs and a co-author of Office VBA Macros You Can Use Today.

He currently lives in Merritt Island, Florida with his wife, Muge, and their cat, Cuma.

About This Book

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 due to the missing VBA capability on the web platform. 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 shows the process of creating Excel Custom Functions in Excel Desktop for Windows and Mac, and Excel Online. All custom function samples created in this book have been installed and tested in the following platforms.

Excel Version 1906 (Build 11727.20210) in Windows 10Excel Version 16.25 (19051201) in macOS MojaveExcel Online in Microsoft Edge (44.17763.1.0)Excel Online in Google Chrome (74.0.3729.157)Excel Online in Safari (12.1)

Quick Start

Microsoft Script Lab

Installing and Using Microsoft Script Lab

You will learn the custom function Office add-in file structure, and detailed information about how to create Excel Custom Functions in the following chapters of this book. However, as a quick start, you will see how you can write and test your custom functions without building an add-in to sideload in Excel by using an amazing tool that Microsoft developed—Script Lab.

Script Lab is an Office add-in that you can install from the Office Add-ins Store and create your custom functions easily.

Start Excel and create a new workbook. Go to Insert tab on the ribbon and click the Get Add-ins button in the Add-ins menu item. Then search for the "script lab" by using the search box in the Office Add-ins dialog, and press Enter key. When you get the search results, click the Add button to install Script Lab Add-in.

Figure 1 Install Script Lab Office add-in.

Script Lab will be installed, and a new ribbon tab will be created to run the add-in functions. Go to the new Script Lab ribbon tab and click the Code button to run the add-in task pane.

Figure 2 Script Lab task pane.

Click the plus sign at the top of the page opened in the task pane, then you will see the script editor window with sample code. Select and delete all the code in the editor, and copy and paste the following custom function code.

The AVERAGE custom function

/**

* Calculates the average of two numbers