Overview
Product Details
| ISBN-13: | 9781615472598 |
|---|---|
| Publisher: | Holy Macro! Books |
| Publication date: | 07/19/2019 |
| Series: | Straight to the Point |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 42 |
| File size: | 3 MB |
About the Author
Read an Excerpt
CHAPTER 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-insbutton 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 Addbutton to install Script Lab 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.
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.
You should see the following in the Script Lab script editor.
Click the Blank Snippet button to edit script info and change the snippet name and description as shown below. Click the Update button to save the snippet info. The snippet name will be used right after the SCRIPTLAB namespace in the function.
Finally click the Register button in the add-in task pane, and your custom function is ready to be tested. Enter the function name as SCRIPTLAB.EFC.AVERAGE to cell B1 and provide the required arguments as cell A1 and A2. Your custom function will work just like any other user defined or internal Excel function.
Script Lab is an amazing tool that will help you testing your custom functions, and get the immediate results without actually building an Office add-in.
Now you are ready to proceed to the next chapter to start learning how to create your own Excel custom functions add-in!
CHAPTER 2Getting Prepared
INSTALLING THE DEVELOPMENT TOOLS
Although Office add-ins can be created by using a script editor like Notepad++ or Brackets, you will use various development tools to build and test the custom functions created in this book. By using these tools, you will not have to deal with manually sideloading the Office add-in to test your custom functions as well as manually editing the functions metadata file.
Node.js
The primary development tool is Node.js, an open source JavaScript runtime environment that allows you to run programs writing in JavaScript.
Go to Node.js page online-https://nodejs.org/-and select the download link for your platform.
Execute the downloaded installation file and follow the installation instructions by using the default options. After the installation, restart your computer, and verify the installation by running the npm -v command in the command prompt as shown below:
npm — Node Package Manager — is installed during the Node.js installation. You will install Yeoman generator for Office Add-ins package and run a local server to test and debug your custom functions easily by using npm.
Git
Git is a free and open source distributed version control system. Although Git is not required to create Office add-ins, the Yeoman Generator for Office Addins currently doesn't generate an Office add-in project without Git is installed. Therefore, go to Git page online — https://git-scm.com — and download the latest version for your platform. Install the application by using the default options then restart your computer.
You will not use Git in this book, or probably in any Office add-in project development, however you still need it to be installed to be able to use Yeoman Generator for Office Add-ins.
Yeoman Generator for Office Add-ins
Yeoman Generator for Office Add-ins simply creates the scaffolding of files for an Office add-in project. Once you create your Office add-in by using the generator, you can start extending the add-in with your own functionality in an editor of your own choice. The generator is an amazing tool that will give you a perfect starting point for your Office add-in project.
Open a command prompt, and execute the following command:
npm install -g yo generator-office
This command will install the generator, run the necessary checks to make sure it is installed properly and ready to be used in your system. You should see the following result at the bottom of the command prompt window:
[ILLUSTRATION OMITTED]
Visual Studio Code
Visual Studio Code is a great tool to help you develop your Office Add-ins and run in the web clients, Windows clients, iOS clients or on macOS. It dramatically makes easy to build and debug your applications.
Go to Visual Studio Code page online — https://code.visualstudio.com, download the stable build for your platform, and install it by following the instructions. Restart your computer and you are now ready to create your first custom functions Office add-in!
CREATING THE OFFICE ADD-IN
Yeoman Office Generator
You will use Yeoman Generator for Office Add-ins — yo — to create the Office add-in. Open a command prompt and go to a folder of your choice to create the add-in project folder. Type the following command and press the Enter key.
yo office
The generator will ask you the project type and other add-in properties. Select Excel Custom Functions Add-in project as the project type and answer the following questions to proceed.
Choose the script type as JavaScript since you will be using JavaScript in this book. And finally, name your Office add-in as excel-custom-functions.
As soon as you hit the Enter key, the generic Office add-in project repo will be downloaded and cloned to the current directory in a new folder with the add-in name you specified. Yeoman Generator will also execute npm install automatically to install the required dependencies. Once the installation is complete, go to the add-in directory in the command prompt by using the following command:
cd excel-custom-functions
Build the project for the first time by using npm command as shown below.
npm run build
This will also install the following certificate that your project needs to run. Accept the following confirmation to allow installing the certificate.
Office Add-ins use HTTPS to load add-in source files, even when you are developing. If you are prompted to install a certificate after you run npm run build, accept the prompt to install the certificate that Yeoman Office Generator provides. Otherwise you won't be able to start and sideload the Office add-in successfully.
Important note for Windows 7 users: If you are using Windows 7 then npm will fail at this step due to powershell command installing the certificate used in the build script doesn't exist in Windows 7.
In this case, go to %userprofile%\.office-addin-dev-certs\ folder, double click on the ca.crtcertificate file that was generated automatically, and manually install the certificate into the Trusted Root Certificate Authorities folder. After the certificate installation retry building the project by using npm run build command.
Finally, run the following command to sideload your add-in to test in Excel Desktop. This will open the Excel application, and sideload the Office add-in.
npm run start:desktop
Congratulations! You've just created a fully functional Excel Custom Functions Office Add-in. In this book, you will be editing the scripts and running the npm commands in Visual Studio Code. Therefore, launch Visual Studio Code, click the Explorer icon at the top of the left sidebar, and click the Open Folder button (or simply press Ctrl + K and O key combination). Find your project folder in the Open Folder dialog and click the Select Folder to load your add-in project folder. Now you are ready for the next chapter!
CHAPTER 3Getting Familiar
ADD-IN SOURCE FILES AND FILE STRUCTURES
At minimum, there are basically four source files that you will use to create the add-in to host the JavaScript custom functions:
1. The JavaScript functions file
2. The JSON metadata file
3. The loader HTML file
4. The add-in manifest file
Although you will see many other files in the add-in folder, these four files are the only files that you will need to work with in this project. In fact, since you will be adding JSDoc tags to the custom functions and using npm to build the actual add-in files, you won't even need to edit the JSON metadata file. However, it is a good practice to see the metadata file content and how it changes with the custom function JSDoc tags.
The JavaScript Functions File-/src/functions/functions.js
functions.js is a JavaScript file that contains the function codes. You will find this file in the/src/functions/ folder in the add-in directory root. You will write into this file the custom function codes in JavaScript language discussed in this book. Basically, this is the main file you will be mostly editing to create your custom functions.
The JSON Metadata File-/dist/functions.json
The JSON metadata file contains the metadata of the custom functions in JSON format and tells Excel how to present the associated custom functions to the user and execute them by using the properties and options listed in it.
The metadata file is auto-generated every time you rebuild the add-in in Visual Studio Code, and it is saved in the /dist/ folder. Unless you want to generate the metadata manually, you won't need to edit this file to create your custom functions add-in. Although it will be auto-generated, you can still see how its content changes with the JSDocs tags in the actual functions file to understand the custom function properties.
The Loader HTML File-/src/functions/functions.html
The Loader HTML file is the loader of the Office JavaScript API library for Excel custom functions that will provide the connection between Excel and the custom functions.
The add-in project automatically generated by Yeoman Office Generator will use the up-to-date link to the custom functions runtime, so you won't need to edit this file manually just like the JSON metadata file.
The Add-in Manifest File
An Office add-in manifest file is an XML file that is the main instructor of the add-in. It describes how the add-in should be activated when it is installed with Office applications. The add-in manifest file in custom functions add-in also helps Excel to locate the previously mentioned source files during the add-in installation.
The add-in manifest file has nothing to do with the function contents that you create in the add-in, so there is no function information in this file. Instead, the file contains the add-in properties to be displayed during the installation in the Excel application and the references to the actual source files. Yeoman Office Generator creates a generic Office add-in manifest file for custom functions add-in. The following are the elements that needs to be customized for the custom functions add-in which is created in this book:
Id: Unique ID for the Office add-in. Keep the auto-generated value.
Provider Name: Who developed this Office add-in. Max 125 characters.
Display Name: Name of the Office add-in. Max 125 characters.
Description: Description of the Office add-in. Max 250 characters.
Resources: Contains icons, strings, and URLs to be used in the manifest file specified by their IDs. Therefore, resources can be referred multiple times in the manifest file. This helps to keep the size of the manifest manageable. One particular string resource is important to mention here, the one identified as "Functions.Namespace". This optional string specifies the prefix for all custom functions in the add-in, which will be used in a worksheet cell during function entry.
Notice that all resources in the manifest file refers to https://localhost:3000/ as the add-in source files will be served on the local server which Node.js runs.
Find and change the following tags and resource values in the manifest file to customize the custom functions add-in. You can use your own information as well but use the same namespace value -ECF-as it will be used as the custom function prefix for all samples in this book.
Save the add-in manifest file and proceed next chapter to create your first custom function.
CHAPTER 4Warming Up
CREATING A NEW FUNCTION
The generic Office add-in project comes with some sample custom functions. Delete the default JavaScript functions file content, and copy and paste the following code into the file.
This function code block consists of three sections:
1. JSDoc tags
2. Function body
3. Function association
JSDoc Tags
Although you can create the JSON metadata file manually to build a custom functions add-in, you will use JSDoc tags to define the function properties in this book. These tags will be used at build time to create the metadata file automatically.
The JSDoc tags used in the AVERAGE function are explained below.
• @customfunction id name
You must specify this tag to define a function as a custom function. It takes two optional arguments separated with a space.
1. id: Unique identifier of the custom function. Contains only alphanumeric characters and period. If id is omitted, then the JavaScript function name is converted to uppercase after removing the disallowed characters and used as id.
2. name: The name of the custom function that end users see in Excel. If name is omitted, then id is also used as the name of the custom function. You will omit the name argument in the sample custom functions in this book, and simply let the JavaScript function name assigned as the provided id.
• @param {type} name description
Each custom function parameter is defined by this tag with the following properties:
1. {type}: The type of the parameter which can be set to boolean, number, string orany. The AVERAGE function's arguments are numeric values, so their types are defined as number. If {type} is not specified, then any is used as default type. {type}basically tells Excel to convert the provided argument to the specified type before using it in the function. To pass a matrix of values to a function, matrix parameter type can be set as a two dimensional array-{number[][]}.
2. name: Specifies the corresponding custom function parameter. This property is required. However, custom function's parameter could be set as optional. You can put square brackets around the parameter name if the function parameter is optional — [name].
3. description: A short description of the function that will show up in the available functions list. This is an optional property and it can include spaces.
• @returns {type}
Each custom function returns either a scalar or matrix type as a return value. A scalar return type can be set to boolean, number, string or any, and a matrix return type can be set as a two dimensional array-{number[][]}. A custom function can also return Error type in the case of an error in return and Promise type as the result of an asynchronous function.
(Continues…)
Excerpted from "Excel Custom Functions Straight to the Point"
by .
Copyright © 2019 Suat M. Ozgur.
Excerpted by permission of Holy Macro! Books.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.
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,