CHAPTER 27
An introduction to creating Office add-ins
In this chapter, you will:
Create an Office add-in
Add interactivity to an Office add-in
Learn the basics of HTML and JavaScript
Use XML to define an Office add-in
With Office 2013, Microsoft introduced Office add-ins, applications that provide expanded functionality to a sheet, such as a selectable calendar, or an interface with the web, such as retrieving information from Wikipedia or Bing. Like Excel add-ins, once Office add-ins are installed, they’re always available. But unlike Excel add-ins, the Office add-ins have limited interaction with sheets and do not use VBA.
An Office add-in consists of an HTML file that provides the user interface on a task or content pane, a CSS file to provide styles for the HTML file, a JavaScript file to provide interactivity to the HTML file, and an XML file to register the Office add-in with Excel. This might sound like a lot of new programming skills, but it’s not. I’ve designed only the most basic web pages, and that was years ago, but I was able to apply my VBA programming skills to JavaScript, which is where the bulk of the programming goes. The language is a little different, but it’s not so different that you can’t create a simple, useful app.
This chapter introduces you to creating an Office add-in to distribute locally and to the basics of the various programming languages. It is not meant to provide in-depth instruction, especially for JavaScript.
Note JavaScript custom functions are user-defined functions (UDFs) you create for use with Excel Online. They use the same JavaScript API as Office add-ins. This book doesn’t cover creating them. For more information, see Excel JavaScript UDFs Straight to the Point by Suat M. Ozgur (ISBN 978-1-61547-247-5).
Tip You don’t need a fancy program to write the code for any of the files in an Office add-in. The Notepad program that comes with Windows does the job. But when you consider the case sensitivity of some programming languages, like JavaScript, using a program that provides some help is a good idea. I spent a couple of hours in frustration over some of the samples in this chapter, wondering why they didn’t work when the code was perfect. Except the code wasn’t perfect. Again and again I missed the case sensitivity in JavaScript and XML, and, in one case, I had a curly apostrophe instead of a straight one.
Switching to Notepad++ (www.notepad-plus-plus.org) was a quick and easy solution because it highlights keywords and grays out strings (which is how I found the incorrect apostrophe around a string).
Creating your first Office add-in—Hello World
Hello World is probably the most popular first program for programmers to try out. It’s a simple program, just outputting the words “Hello World,” but it introduces the basics required by the application. So, with that said, it’s time to create a Hello World Office add-in.
Caution A network is used to distribute the Office add-in locally. You cannot use a local drive or a network drive mapped to a drive letter. If you do not have access to a network, you will not be able to test your Office add-in.
Note In the following steps, you enter text into a text editor. Unlike with the VB Editor, there isn’t a compiler to point out mistakes before you run the program. It is very important that you enter the text exactly as written, including the case of text within quotation marks.
To open a file for editing, such as with Notepad, right-click the file and select Open With. If you see Notepad, select it; otherwise, select Choose Another App. From the dialog box that opens, find Notepad. Make sure that Always Use This App To Open filetype Files is not selected and then click OK. The next time you need to edit the file, Notepad appears in the quick list of available programs in the Open With option.
Follow these steps to create your Office add-in:
Create a folder and name it HelloWorld. This folder can be on your local drive while you are creating the program. All the program files will be placed in this folder. When you’re finished, you’ll move it to the network.
Create the HTML program by inserting a text file in the folder and naming it HelloWorld.html. Then open the HTML file for editing and enter the following code in it:
<!DOCTYPEhtml>
<html>
<head>
<meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=Edge"/>
<link rel="stylesheet" type="text/css" href="program.css"/>
</head>
<body>
<p>Hello World!</p>
</body>
</html>
Save and close the file.
Create the CSS file to hold the styles used by the HTML file by inserting a text file into the folder and naming it program.css. Note that this is the same file name used in the HTML file in the
<link rel>tag. Open the CSS file for editing and enter the following code in it:body
{
position:relative;
}
li :hover
{
text-decoration: underline;
cursor:pointer;
}
h1,h3,h4,p,a,li
{
font-family: "Segoe UI Light","Segoe UI",Tahoma,sans-serif;
text-decoration-color:#4ec724;
}
Save and close the file.
Create the XML file by inserting a text file in the folder and naming it HelloWorld.xml. Then open the XML file for editing and enter the following code in it.
Caution The following code sample and others that follow include lines that extended beyond the width of the page, so I needed to add a _ to indicate a line that is continued. Unlike in VBA, in this case you should not type the underscores. Instead, when you get to an underscore, just ignore it and continue inputting the code after it on the same line.
<?xml version="1.0" encoding="utf-8"?>
<OfficeApp xmlns="http://schemas.microsoft.com/office/appforoffice/1.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:type="TaskPaneApp">
<Id>08afd7fe-1631-42f4-84f1-5ba51e242f98</Id>
<Version>1.0</Version>
<ProviderName>Tracy Syrstad</ProviderName>
<DefaultLocale>EN-US</DefaultLocale>
<DisplayName DefaultValue="Hello World app"/>
<Description DefaultValue="My first app."/>
<IconUrl DefaultValue=
"http://officeimg.vo.msecnd.net/_layouts/images/general/ _
officelogo.jpg"/>
<Capabilities>
<Capability Name="Document"/>
<Capability Name="Workbook"/>
</Capabilities>
<DefaultSettings>
<SourceLocation DefaultValue="\workpcMyAppsHelloWorld _
HelloWorld.html"/>
</DefaultSettings>
<Permissions>ReadWriteDocument</Permissions>
</OfficeApp>
Do not close the XML file yet.
While the XML file is still open, note the ID
08afd7fe-1631-42f4-84f1-5ba51e242f98. This is a globally unique identifier (GUID). If you are testing on a private network and not distributing this file, you can likely use this GUID. But if you’re on a business network with other programmers or if you’re distributing the file, you must generate your own GUID. See the section “Using XML to define an Office add-in,” later in this chapter, for more information on GUIDs.
Note GUID stands for globally unique identifier. A GUID is a unique reference number that identifies software. It’s usually displayed as 32 alphanumeric digits separated into five groups (8-4-4-4-12) by hyphens. A GUID has so many digits that it's rare for identical ones to be generated.
Move the HelloWorld folder to a network share folder if it’s not already there. Note the path to the folder and to the HTML file because you will be making use of this information. The path to the folder should be \myservermyfolder. For example, my HelloWorld folder is located at \workpcMyAppsHelloWorld.
Open the XML file for editing and change
<SourceLocation>(located near the bottom of the code) to the location of the HTML file on your network. Save and close the file.Configure your network share as a Trusted Catalog Address by following these steps:
Start Excel and go to File, Options, Trust Center and click Trust Center Settings.
Select Trusted Add-In Catalogs.
Enter your folder path in the Catalog URL field and click Add Catalog. The path is added to the list box.
Select the Show In Menu box.
Click OK. You should see a prompt indicating that the Office add-in will be available the next time Excel starts (see Figure 27-1). Click OK twice.
Restart Excel.
Caution Only one network share at a time can be configured to show in the catalog. If you want users to have access to multiple Office add-ins at once, the XML for the Office add-ins must be stored in the same network share. Otherwise, users will have to go into their settings and select which catalog to show.
Insert the Office add-in you just created into Excel by selecting Insert, Add-Ins, Store. Then, in the Office Add-Ins dialog box, select Shared Folder. If you don’t see anything when you’ve selected the link, click Refresh. The Hello World Office add-in should be listed, as shown in Figure 27-2.
Note If you still do not see anything after refreshing, there is something incorrect in the files or the setup. Carefully review all the code and steps. If you do not see anything incorrect, try changing the GUID.
FIGURE 27-1 Configure the location of your Office add-ins under Trusted Add-In Catalogs.
FIGURE 27-2 The Shared Folder lists any Office add-ins available in the active catalog.
Select the Office add-in and click Insert. A task pane on the right side of the Excel window opens, as shown in Figure 27-3, and displays the words “Hello World!”
FIGURE 27-3 By creating Hello World, you take a first step in creating interactive Office add-ins.
Adding interactivity to an Office add-in
The Hello World Office add-in created in the preceding section is a static one; it doesn’t do anything except show the words in the code. But as you browse the web, you run into dynamic web pages. Some of those web pages use JavaScript, a programming language that adds automation to elements on otherwise static websites. In this section, you modify the Hello World Office add-in by adding a button to write data to a sheet and another button that reads data from a sheet, performs a calculation, and writes the results to the task pane.
Tip You don’t have to restart Excel if you are editing the code of an installed Office add-in. Instead, right-click in the Office add-in’s task pane and select Reload.
To add these interactive features to the Hello World Office add-in, follow these steps:
To create the JavaScript file that will provide the interactivity for the two buttons Write Data To Sheet and Read & Calculate Data From Sheet, first insert a text file in the Hello World folder and name the file program.js. Then open the JavaScript file for editing and enter the following code in it:
Office.initialize = function (reason) {
//Add any needed initialization
}
//declare and set the values of an array
var MyArray = [[234],[56],[1798], [52358]];
//write MyArray contents to the active sheet
function writeData() {
Office.context.document.setSelectedDataAsync(MyArray, _
{coercionType: 'matrix'});
}
/*reads the selected data from the active sheet
so that we have some content to read*/
function ReadData() {
Office.context.document.getSelectedDataAsync("matrix", _
function (result) {
//if the cells are successfully read, print the results in the task pane
if (result.status === "succeeded"){
sumData(result.value);
}
//if there was an error, print the error in the task pane
else{
document.getElementById("results").innerText = _
result.error.name;
}
});
}
/*the function that calculates and shows the result
in the task pane*/
function sumData(data) {
var printOut = 0;
//sum together all the values in the selected range
for (var x = 0 ; x < data.length; x++) {
for (var y = 0; y < data[x].length; y++) {
printOut += data[x][y];
}
}
//print the results in the task pane
document.getElementById("results").innerText = printOut;
}
Save and close the file.
Note In JavaScript, lines prefixed by // and /* are comments.
Edit the HelloWorld.html file so that it points to the JavaScript file program.js, and add the two buttons used by the JavaScript code. To do this, replace the existing code with the following:
<!DOCTYPEhtml>
<html>
<head>
<meta charset="UTF-8"/>
<meta http-equiv="X-UA-Compatible" content="IE=Edge"/>
<link rel="stylesheet" type="text/css" href="program.css"/>
<!--begin pointer to JavaScript file-->
<script src = "https://appsforoffice.microsoft.com/lib/1.0/ _
hosted/office.js"></script>
<script src= "program.js"></script>
<!--end pointer to JavaScript file-->
</head>
<body>
<!--begin replacement of body-->
<button onclick="writeData()">Write Data To Sheet</button></br>
<button onclick="ReadData()">Read & Calculate Data From Sheet _
</button></br>
<h4>Calculation Results: <div id="results"></div> </h4>
<!--end replacement of body-->
</body>
</html>
In this new code, you’ve added
<script>tags and replaced the code between the<body>tags. Comment tags,<!--comments-->, are included to show where the changes are.Save and close the file.
After creating the JavaScript file and updating the HTML file, reload the Office add-in and test it by clicking the Write Data To Sheet button. It should write the numbers from MyArray onto the sheet. With those cells selected, click Read & Calculate Data From Sheet, and the results of adding the selected numbers together will appear in the Calculation Results line of the task pane, as shown in Figure 27-4.
FIGURE 27-4 Use JavaScript to create an Office add-in that can perform a calculation with data from a sheet.
A basic introduction to HTML
The HTML code in an Office add-in controls how the task or content pane will look, such as the text and buttons. If you open the HTML file from either of your Hello World files, it opens in your default browser and looks as it did in Excel’s task pane (though without any functionality). You can design the Office add-in as you would a web page, including adding images and links. The following sections review a few basics to get you started in designing your own Office add-in interface.
Using tags
HTML consists of elements, such as images, links, and controls, that are defined by the use of tags enclosed in angle brackets. For example, the starting tag <button> tells the code that what follows, inside and outside the tag’s brackets, relates to a button element. For each start tag, you have an end tag, which is usually the same as the opening tag but with a slash—like </button>—but some tags can be empty—like />. A browser does not display tags or anything within a tag’s brackets. Text that you want displayed needs to be outside the tag's brackets.
Comments have a tag of their own and don’t require your typical end tag. As in VBA, commented text doesn’t appear on the screen. Add comments to your HTML code like this:
<!--This is a comment-->
A multiline comment would appear like this:
<!--This is a multiline comment.
Notice that nothing special is needed -->
Adding buttons
To create the code for a button, you need to label the button and link it to a function in the JavaScript file that will run when the button is clicked. Here’s an example:
<button onclick="writeData()">Write Data To Sheet</button>
The first part, <button onclick="writeData()">, identifies the control as a button and assigns the function writeData to the click event for the button. Notice that the function name is in quotation marks and includes argument parentheses, which are empty. The second part, Write Data To Sheet, is the text of the label on the button. The label name is not in quotation marks. The line ends with the closing tag for the button.
To change other attributes of the button, you just need to specify those attributes. For example, to change the button text to red, add the style attribute for color, like this:
<button onclick="writeData()" style="color:Red">Write Data To Sheet</button>
To add a tooltip that appears when the mouse is placed over the button, as shown in Figure 27-5, use the title attribute, like this:
<button onclick="writeData()" style="color:Red"
title = "Use to quickly add numbers to your sheet">
Write Data To Sheet</button></br>
Use a space to separate multiple attributes. After an attribute name, such as style, put an equal sign and then the value in quotation marks. Also notice that HTML is rather forgiving about where you put your line breaks. Just don’t put them within a string, or you might also get a line break on the screen in that position.
FIGURE 27-5 Add other attributes to your button to change colors or add tooltip text for users.
Using CSS files
CSS stands for Cascading Style Sheets. You create styles in Excel and Word to make it easy to modify how text looks in an entire file without changing every occurrence. You can do the same thing with an Office add-in by creating a separate style file (CSS) that your HTML code references. In the file, you set up rules for various elements of the HTML file, such as layout, colors, and fonts.
The CSS file provided in the Hello World example can be used for a variety of projects. It includes styles for h1, h3, and h4 headings, hyperlinks (a), paragraph tags (p), and bullets (li).
Using XML to define an Office add-in
XML defines the elements needed to display and run an Office add-in in Excel, including the GUID, Office add-in logo, and location of the HTML file. XML also configures how the Office add-in will appear in the Office Add-Ins store and can provide a version number for the program.
Caution XML tags are case sensitive. When you make changes to the provided Hello World sample, be sure you don’t change any of the tags but only their values.
Two types of user interfaces are available for an Office add-in: a task pane or a content pane. A task pane starts off docked on the right side of the Excel window, but a user can undock it and move it around the window. A content pane appears as a frame in the middle of the Excel window. Which type you use is up to you. To tell an Office add-in which type of pane to use, set the xsi:type value to either TaskPaneApp or ContentApp.
You should always use a unique identifier when creating an Office add-in. Websites such as http://www.guidgen.com generate GUIDs for you.
In the Hello World sample, the store icon used is an online icon that Microsoft has made available. But you can also use your own .jpg file. The image should be small, about 32×32 pixels. Update IconURL with the full path to the image, like this:
<IconUrl DefaultValue="\workpcMyAppsHelloWorldmrexcellogo.jpg"/>
The SourceLocation tag is used to set the full path to the HTML file. If the HTML file cannot be found when the Office add-in is being installed, an error message appears, stating that the file couldn’t be found.
Note If you make changes to XML after you’ve already configured the location of the catalog or installed the Office add-in, be sure to click the Refresh link in the Office Add-Ins dialog box. For example, if you switch between TaskPaneApp and ContentApp, the change might not be reflected even if you select to install the Office add-in again. To be safe, refresh the Office Add-Ins dialog box.
Using JavaScript to add interactivity to an Office add-in
JavaScript provides the wow factor behind an Office add-in. You can create a very useful reference with just HTML, but to make an interactive Office add-in, such as a function calculator, you need JavaScript.
The following sections provide a basic introduction to JavaScript. If you are already familiar with JavaScript, you can go ahead to “JavaScript changes for working in the Office add-in.”
Note The document.getElementById("results").innerText command used in the following examples is the command for the code to put the returned value in the place reserved by the “results” variable in the HTML file.
Note Microsoft is always making improvements to the JavaScript API, expanding its capabilities to handle Excel’s objects. You can keep up with these changes at the API reference site at https://docs.microsoft.com/en-us/javascript/api/excel?view=office-js.
The structure of a function
JavaScript code consists of functions called by HTML code and by other JavaScript functions. Just as in VBA, each JavaScript function starts with function followed by the name of the function and any arguments in parentheses. But unlike in VBA, there is no End Function at the end; instead, you use curly braces to group the function. See the following subsection, “Curly braces and spaces,” for more information.
JavaScript is case sensitive, including variable and function names. For example, if you create a function called writeData but then try to call WriteData from another function, the code does not work because in one case, write is in lowercase, and in the other it has a capital W. JavaScript recognizes these as different functions. Create case rules for yourself, such as initial caps for each word in a variable, and stick to them. This helps reduce troubleshooting of JavaScript code issues.
Curly braces and spaces
Curly braces ({}) are characters used in JavaScript but not in VBA. You use them to group blocks of code that should be executed together. You can have several sets of braces within a function. For example, you would use them to group all the code in a function; then, within the function, you would use them to group lines of code such as within an if statement.
After you’ve finished typing a line in VBA and gone to another line, you might notice that the line adjusts itself, adding or removing spaces. In JavaScript, spaces don’t usually matter; the exceptions are spaces in strings and spaces between keywords and variables in the code. In the code samples in this section, notice that sometimes I have included spaces (a = 1) and sometimes I have not (a=1).
Semicolons and line breaks
You’ve probably noticed the semicolons (;) used in JavaScript code. They might have appeared at the end of every line, or maybe only on some lines. Perhaps you’ve noticed a line without a semicolon or noticed a semicolon in the middle of a line. The reason the use of semicolons appears inconsistent is that, under normal circumstances, semicolons are not required. A semicolon is a line break. If you use hard returns in your code, you are already placing line breaks, so the semicolon is not needed. If you combine multiple lines of code onto one line, though, you need a semicolon to let the code know that the next piece of code is not part of the previous code.
Comments
There are two ways to comment out lines in JavaScript. To comment out a single line, place two slashes (//) at the beginning of the line, like this:
//comment out a single line in the code like this
If you want to comment out multiple lines in VBA, you have to preface each line with an apostrophe. JavaScript has a cleaner method. At the beginning of the first line you want to comment out, place a slash and an asterisk (/*). At the end of the last line of the comment, place an asterisk and a slash (*/). It looks like this:
/* Comment out
multiple lines of code
like this */
Variables
In VBA, you have the option of declaring variables. If you do declare them, you don’t have to declare the variable type, but after a value is assigned to a variable, it’s not always easy to change the type. In JavaScript, you don’t declare variables, except for arrays. (See the later subsection “Arrays” for more information.) When a value is assigned to a variable, it becomes that type, but if you reference the variable in another way, its type might change.
In the following example, the string “123” is assigned to myVar, but in the next line, a number is subtracted:
myVar = "123"
myVar = myVar-2
JavaScript just goes with it, allowing you to change the variable from a string to a number. If you ran this code, myVar would be 121. Note that myVar+2 would not deliver the same result. See the next subsection, “Strings,” for more information.
If you need to ensure that a variable is of a specific type, use one of these functions to do so: Boolean, Number, or String. For example, you have a function that is reading in numbers imported onto a sheet. As is common in imports, the numbers could be stored as text. Instead of having to ensure that the user converts the data, use the Number keyword when processing the values like this to force the number to be a number:
Number(importedValue)
Strings
As in VBA, in JavaScript you reference strings by using double quotations marks (“string”), but, unlike in VBA, you can also use single quotation marks ('string'). The choice is up to you; just don’t start a string with one type of quotations marks and end with another. The capability to use either set can be useful. For example, if you want to show quoted text, you use the single quotes around the entire string, like this:
document.getElementById("results").innerText = 'She heard him shout, "Stay away!"'
This would be the result in the pane:
She heard him shout, "Stay away!"
To concatenate two strings, use the plus (+) sign. You also use the plus to add two numbers. So what happens if you have a variable hold a number as text and add it to a number, as in this example:
myVar = "123"
myVar = myVar+2
You might think that the result would be 125. After all, in the previous example, with -2, the result was 121. In this case, concatenation has priority over addition, and the answer is actually 1232. To ensure that the variable is treated like a number, use the Number function. If the variable it is holding cannot be converted to a number, the function returns NaN, for “not a number.”
Arrays
Arrays are required for processing multiple cells in JavaScript. Arrays in JavaScript are not very different from arrays in VBA. To declare an unlimited-size array, do this:
var MyArray = new Array ()
Note If you are unfamiliar with using arrays in VBA, see Chapter 8, “Arrays.”
To create an array of limited size, such as 3, do this:
var MyArray = new Array(3)
You can also fill an array at the same time that you declare it. The following creates an array of three elements, two of which are strings and the third of which is a number:
var MyArray = ['first value', 'second value', 3]
The array index always starts at 0. To print the second element, second value, of the preceding array, do this:
document.getElementById("results").innerText = MyArray[1]
If you’ve declared an array with a specific size but need to add another element, you can add the element by specifying the index number or by using the push() function. For example, to add a fourth element, 4, to the previously declared array, MyArray, do this (because the count starts at 0, the fourth element has an index of 3):
MyArray [3] = 4
If you don’t know the current size of the array, use the push() function to add a new value to the end of the array. For example, if you don’t know the index value for the last value in the preceding array, you can add a new element, fifth value, like this:
MyArray.push('fifth value')
Refer to the section “How to do a For each..next statement in JavaScript” if you need to process the entire array at once. JavaScript has other functions for processing arrays, such as concat(), which can join two arrays, and reverse(), which reverses the order of the array’s elements. Because this is just a basic introduction to JavaScript, those functions are not covered here. For a tip on applying a math function to an entire array with a single line of code, see the section “Math functions in JavaScript.”
JavaScript for loops
When you added interactivity to the Hello World Office add-in earlier in this chapter, you used the following code to sum the selected range:
for (var x = 0 ; x < data.length; x++) {
for (var y = 0; y < data[x].length; y++) {
printOut += data[x][y];
}
}
The two for loops process the array, data, that is passed into the function, with x as the row and y as the column.
A for loop consists of three separate sections separated by semicolons. When the loop is started, the first section, var x=0, initializes any variables used in the loop. Multiple variables would be separated by commas. The second section, x < data.length, tests whether the loop should be entered. The third section, x++, changes any variables to continue the loop, in this case incrementing x by 1 (x++ is shorthand for x=x+1). This section can also have more than one variable, with commas separating them.
Tip To break out of a loop early, use the break keyword.
How to do an if statement in JavaScript
The basic if statement in JavaScript has this syntax:
if (expression){
//do this
}
Here, expression is a logical function that returns true or false, just as in VBA. If the expression is true, the code continues and runs the lines of code in the //do this section. To execute code if the expression is false, you need to add an else statement, like this:
if (expression){
//do this if true
}
else{
//do this if false
}
How to do a Select..Case statement in JavaScript
Select..Case statements are very useful in VBA as an alternative to using multiple If..Else statements. In JavaScript, similar functionality is in the switch() statement. Typically, this is the syntax of a switch() statement:
switch(expression){
case firstcomparison : {
//do this
break;
}
case secondcomparison : {
//do this
break;
}
default : {
//no matches, so do this
break;
}
}
Here, expression is the value you want to compare to the case statements. The break keyword is used to stop the program from comparing to the next statement, after it has run one comparison. That is one difference from a Select statement: Whereas in VBA, after a comparison is successful, the program leaves the Select statement, in JavaScript, without the break keyword, the program continues in the switch statement until it reaches the end. Use default as you would a Case Else in VBA—to cover any comparisons that are not specified.
The preceding syntax works for one-on-one comparisons. If you want to see how an expression fits within a range, the standard syntax won’t work. You need to replace the expression with true to force the code into running the switch statement. The case statements are where you use the expression compared to the range. The following code is a BMI calculator UDF converted to JavaScript. It compares the calculated BMI to the various ranges and returns a text description to post to the task pane:
Office.initialize = function (reason) {
//Add any needed initialization.
}
function calculateBMI() {
Office.context.document.getSelectedDataAsync("matrix", function (result) {
//call the calculator with the array, result.value, as the argument
myCalculator(result.value);
});
}
function myCalculator(data){
var calcBMI = 0;
var BMI="";
//Perform the initial BMI calculation to get the numerical value
calcBMI = (data[1][0] / (data[0][0] *data [0][0]))* 703
/*evaluate the calculated BMI to get a string value because we want to evaluate range, instead of switch(calcBMI), we do switch (true) and then use our variable as part of the ranges */
switch(true){
//if the calcBMI is less than 18.5
case (calcBMI <= 18.5) : {
BMI = "Underweight"
break;
}
//if the calcBMI is a value between 18.5 and (&&) 24.9
case ((calcBMI > 18.5)&&(calcBMI <= 24.9)):{
BMI = "Normal"
break;
}
case ((calcBMI > 24.9)&&(calcBMI <= 29.9)) : {
BMI = "Overweight"
break;
}
//if the calcBMI is greater than 30
case (calcBMI > 29.9) : BMI = "Obese"
default : {
BMI = 'Try again'
break;
}
}
document.getElementById("results").innerText = BMI;
}
How to use a For each..next statement in JavaScript
If you have a collection of items to process in VBA, you might use a For each..next statement. One option in JavaScript is for (... in ...). For example, if you have an array of items, you can use the following code to output the list:
//set up a variable to hold the output text
arrayOutput= ""
/*process the array
i is a variable to hold the index value.
Its count starts as 0*/
for (i in MyArray) {
/*create the output by adding the element
to the previous element value.
is used to put in a line break */
arrayOutput += MyArray[i] + ' '
}
//write the output to the screen
document.getElementById("results").innerText = arrayOutput
You can do whatever you need to each element of the array. In this example, you’re building a string to hold the element value and a line break so that when it prints to the screen, each element appears on its own line, as shown in Figure 27-6. The MyArray variable used in this code was filled in the earlier section, “Arrays.”
FIGURE 27-6 JavaScript has its own equivalents to many VBA looping statements, such as for..in loop, which was used to output each result to its own line.
Mathematical, logical, and assignment Operators
JavaScript offers the same basic operators as VBA plus a few more to shorten your code. Table 27-1 lists the various operators. Assume here that x = 5.
TABLE 27-1 JavaScript Operators
Operator |
Description |
Example |
Result |
|---|---|---|---|
|
Addition |
|
|
|
Subtraction |
|
|
|
Division |
|
|
|
Multiplication |
|
|
|
Remainder after division |
|
|
|
Override the usual order of operations |
|
35, whereas x+2*5=15 |
|
Unary minus (for negative numbers) |
|
|
|
Values are equal |
|
|
|
Values and types are equal |
|
|
|
Greater than |
|
|
|
Less than |
|
|
|
Greater than or equal to |
|
|
|
Less than or equal to |
|
|
|
Values are not equal |
|
|
|
Values and types are not equal |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Increment |
|
|
|
Decrement |
|
|
|
Equal to with addition |
|
|
|
Equal to with subtraction |
|
|
|
Equal to with multiplication |
|
|
|
Equal to with division |
|
|
|
Equal to with the remainder |
|
|
The increment and decrement operators are two of my favorites; I wish we had them in VBA. Not only do they reduce your code, but they offer a flexibility that VBA lacks (post- and pre-increments). You might remember the use of x++ in the Hello World program earlier in this chapter. You used this in place of x=x+1 to increment the for loop. But it doesn’t just increment the value. It uses the value and then increments it. This is called a post-increment. JavaScript also offers a pre-increment, which means the value is incremented and then used. So if you have x=5, both of the following lines of code return 6:
//would increment x and then post the value
document.getElementById("results").innerText = ++x //would return 6
//would post the value of x (now 6 after the previous increment) then increment
document.getElementById("results2").innerText = x++ //would return 6
Math functions in JavaScript
JavaScript has several math functions available, as shown in Table 27-2. Using these functions is straightforward. For example, to return the absolute value of the variable myNumber, do this:
result = Math.abs(myNumber)
TABLE 27-2 JavaScript math functions
Function |
Description |
|---|---|
|
Returns the absolute value of |
|
Returns the arc cosine of |
|
Returns the arc sine of |
|
Returns the arc tangent of |
|
Returns the arc tangent of |
|
Returns the integer closest to |
|
Returns the cosine of |
|
Returns the exponent of |
|
Rounds down, and returns the integer closest to |
|
Returns the log of |
|
Returns the maximum of |
|
Returns the minimum of |
|
Returns |
|
Returns a random number between 0 and 1 (but not including 0 or 1). |
|
Rounds up or down and returns the integer closest to |
|
Returns the sine of |
|
Returns the square root of |
|
Returns the tangent of |
Tip If you need to apply a math function to all elements of an array, you can do so by using the map() function and the desired Math function. For example, to ensure that every value in an array is positive, use the Math.abs function. The following example changes each element in an array to its absolute value and then prints the results to the screen, as shown in Figure 27-7:
result = 0
arrayOutput = ""
arrNums = [9, -16, 25, -34, 28.9]
result = arrNums.map(Math.abs)
for (i in result){
arrayOutput += result[i] +' '
}
document.getElementById("results").innerText = arrayOutput
FIGURE 27-7 Using arrays is a common way of storing data in JavaScript, which offers many functions for simplifying working with those arrays.
Writing to the content pane or task pane
After you’ve processed a user’s data, you need to display the results. This can be done on the sheet or in the Office add-in’s pane. Assuming that arrayOutput holds the data you want to write to the pane, do this:
document.getElementById("results").innerText = arrayOutput
This code writes data to the Office add-in’s pane, specifically to the results variable reserved in the HTML code. To write to the sheet, see the later subsection “Reading from and writing to a sheet.”
JavaScript changes for working in an Office add-in
Not all JavaScript code will work in an Office add-in. For example, you cannot use the alert or document.write statements. There are also some new statements for interacting with Excel provided in a JavaScript API that you link to in the HTML file with this line:
<script src = "https://appsforoffice.microsoft.com/lib/1.0/hosted/office.js">
</script>
Like the APIs used in VBA, the JavaScript API gives you access to objects, methods, properties, and events that JavaScript can use to interact with Excel. You’ve now seen some of the most commonly used objects. For more information on these and other available objects, go to http://msdn.microsoft.com/en-us/library/office/apps/fp142185.aspx.
Initializing an Office add-in
The following event statement must be placed at the top of the JavaScript script:
Office.initialize = function (reason) { /*any initialization*/}
It initializes the Office add-in to interact with Excel. The reason parameter returns how the Office add-in was initialized. If the Office add-in is inserted into the document, then reason is inserted. If the Office add-in is already part of a workbook that’s being opened, reason is documentOpened.
Reading from and writing to a sheet
Office.context.document represents the object that the Office add-in is interacting with—the sheet. It has several methods available, most importantly the two that enable you to read selected data and write to a range.
The following line uses the setSelectedDataAsync method to write the values in MyArray to the selected range on a sheet:
Office.context.document.setSelectedDataAsync(MyArray, {coercionType: 'matrix'});
The first argument, MyArray, is required. It contains the values to write to the selected range. The second argument, coercionType, is optional. Its value, matrix, tells the code that you want the values treated as a one-dimensional array.
The method for reading from a sheet, getSelectedDataAsync, is similar to the write method:
Office.context.document.getSelectedDataAsync("matrix", function (result) {
//code to manipulate the read data, result
});
The first argument, matrix, is the coercionType and is required. It tells the method how the selected data should be returned—in this case, in an array. The second argument shown is an optional callback function, with result being a variable that holds the returned values (result.value) if the call was successful and an error if not.
To find out whether the call was successful, use the status property, result.status. To retrieve the error message, use this:
result.error.name
Next steps
Read Chapter 28, “What’s new in Excel 2019 and what’s changed,” to learn about more features that have changed significantly in Excel 2019.