Let’s tweak the skeleton program VS generated.
First change the “Home.html” to this (copy & paste).
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8" />
<meta http-equiv="X-UA-Compatible" content="IE=Edge" />
<title>Excel Add-In with Commands Sample</title>
<script src="Scripts/jquery-3.6.0.js" type="text/javascript"></script>
<script src="Scripts/MessageBanner.js" type="text/javascript"></script>
<script src="https://appsforoffice.microsoft.com/lib/1/hosted/office.js" type="text/javascript"></script>
<!-- To enable offline debugging using a local reference to Office.js, use: -->
<!-- <script src="Scripts/Office/MicrosoftAjax.js" type="text/javascript"></script> -->
<!-- <script src="Scripts/Office/1/office.js" type="text/javascript"></script> -->
<script src="Home.js" type="text/javascript"></script>
<link href="Home.css" rel="stylesheet" type="text/css" />
<link href="../Content/Button.css" rel="stylesheet" type="text/css" />
<link href="../Content/MessageBanner.css" rel="stylesheet" type="text/css" />
<!-- For Office UI Fabric Core, go to https://aka.ms/office-ui-fabric to learn more. -->
<link rel="stylesheet" href="https://static2.sharepointonline.com/files/fabric/office-ui-fabric-core/9.6.0/css/fabric.min.css">
<!-- To enable the offline use of Office UI Fabric Core, use: -->
<!-- link rel="stylesheet" href="Content/fabric.min.css" -->
</head>
<!-- Office UI Fabric JS and it's components are no longer actively supported. Please see https://aka.ms/PnP-OfficeFabricReact for recommended Patterns and Practices -->
<!-- <link rel="stylesheet" href="https://appsforoffice.microsoft.com/fabric/2.1.0/fabric.components.min.css"> -->
<body class="ms-Fabric" dir="ltr">
<div id="content-main">
<div class="padding">
<button class="Button Button--primary" id="highlight-button">
<span class="Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span>
<span class="Button-label" id="highlight-button-text"></span>
</button>
<button class="Button Button--primary" id="address-button">
<span class="Button-icon"><i class="ms-Icon ms-Icon--plus"></i></span>
<span class="Button-label" id="address-button-text"></span>
</button>
</div>
</div>
<!-- Component used for displaying notifications -->
<div class="MessageBanner" id="notification-popup">
<div class="MessageBanner-content">
<div class="MessageBanner-text">
<div class="MessageBanner-clipper">
<div class="ms-font-m-plus ms-fontWeight-semibold" id="notification-header"></div>
<div class="ms-font-m ms-fontWeight-semilight" id="notification-body"></div>
</div>
</div>
<button class="MessageBanner-expand" style="display:none"><i class="ms-Icon ms-Icon--chevronsDown"></i> </button>
<div class="MessageBanner-action"></div>
</div>
<button class="MessageBanner-close"> <i class="ms-Icon ms-Icon--ChromeClose"></i> </button>
</div>
</body>
</html>
Now change the “Home.js” to this (copy & paste):
(function () {
"use strict";
var messageBanner;
// The initialize function must be run each time a new page is loaded.
Office.initialize = function (reason) {
$(document).ready(function () {
// Initialize the notification mechanism and hide it
var element = document.querySelector('.MessageBanner');
messageBanner = new components.MessageBanner(element);
messageBanner.hideBanner();
// If not using Excel 2016, use fallback logic.
if (!Office.context.requirements.isSetSupported('ExcelApi', '1.1')) {
console.log("Use higher version of Excel (2016 or after)");
return;
}
// Highlight Yellow
$('#highlight-button-text').text("Fill Yellow!");
// Add a click event handler for the highlight button.
$('#highlight-button').click(fill_yellow);
// Show address
$('#address-button-text').text("Show Address");
// Add a click event handler for the highlight button.
$('#address-button').click(show_address);
});
};
async function fill_yellow() {
await Excel.run(async (context) => {
var range = context.workbook.getSelectedRange();
range.format.fill.color = 'yellow';
await context.sync();
console.log("Debug info: Does it work?");
}).catch(errorHandler);
}
async function show_address() {
await Excel.run(async (context) => {
var range = context.workbook.getSelectedRange();
range.load("address");
await context.sync();
var str_adr = range.address;
range.getOffsetRange(0, 1).values = str_adr;
await context.sync();
}).catch(errorHandler);
}
// Helper function for treating errors
function errorHandler(error) {
// Always be sure to catch any accumulated errors that bubble up from the Excel.run execution
showNotification("Error", error);
console.log("Error: " + error);
if (error instanceof OfficeExtension.Error) {
console.log("Debug info: " + JSON.stringify(error.debugInfo));
}
}
// Helper function for displaying notifications
function showNotification(header, content) {
$("#notification-header").text(header);
$("#notification-body").text(content);
messageBanner.showBanner();
messageBanner.toggleExpansion();
}
})();
Click the start button to debug. The add-in now has two buttons.
“Fill Yellow!” button will fill the selected cell yellow and “Show Address” button will show the address of the selected cell right next it.
Let’s first look at the Home.html. It has two sections that starts with <button …. > and ends with </button>. One is for Fill Yellow and the other is for Show Address. This is how to create buttons in HTML.
The first button has
<button class="Button Button--primary" id="highlight-button">
and next one has
<button class="Button Button--primary" id="address-button">
“id” is the key that you can link this button with your JS code, like linking Macro to a button in VBA.
Also, this HTML imports your Home.js by
<script src="Home.js" type="text/javascript"></script>
You can look around other parts of HTML and make changes on it.
Let’s go to our main code in Home.js.
First thing you notice is
"use strict";
This is similar to Option Explicit in VBA. This requires the variables must be declared before use. For example, to use variable x, you have to declare first then use:
var x; x = 4;
I highly recommend using this option especially if you expect your project will become big and will require collaboration with other coders.
Next there is a function Office.initialize. This must be run each time a new page is loaded. If your follow further down, there are lines:
// Highlight Yellow
$('#highlight-button-text').text("Fill Yellow!");
// Add a click event handler for the highlight button.
$('#highlight-button').click(fill_yellow);
As you can easily guess, the first line set the text of the first button to “Fill Yellow!”. The next line links this button to “fill_yellow” JS function, which will be explained next. There are other two lines for Show Address button.
Finally, there are our main functions: fill_yellow and show_address. Let’s look at fill_yellow function.
async function fill_yellow() {
await Excel.run(async (context) => {
var range = context.workbook.getSelectedRange();
range.format.fill.color = 'yellow';
await context.sync();
console.log("Debug info: Does it work?");
}).catch(errorHandler);
}
var range = context.workbook.getSelectedRange();
This will get the selected range from the spread sheet and assign it to the variable range. If you are VBA user, it’s quite similar. If you have any OOP experience, it will not be difficult to read the code.
range.format.fill.color = 'yellow';
This changes the property of fill of format of range object.
console.log("Debug info: Does it work?");
I left this as an example if you want to write a debugging message to the browser debugging console.
The coding itself is quite similar to VBA. There are some strange things, though: async and await keywords. These are not in VBA and in many languages and are specific to web programming. I am not a web programming expert, but this is my explanation for these (correct me if I am wrong). The Excel Web Add-in is developed considering web-based collaboration. So, it is possible that while I am trying to change the selected cell color to yellow, someone else has already started working on the exactly same cell to change the color right before you. Then what does your program have to do? It has to wait until the other use finishes the job then change the color to yellow. The waiting is guaranteed by the keyword await: await context.sync();. Also, to use await keyword in a function, the function must be declared as an async function. However, calling methods in an object does not require await.
Without having web programming experience, this took me significant time to figure why some simple thing that works well in VBA throws errors on JS. So, simple rule: if you want to change the property of an object, 1) your function must be async, 2) change the property, and 3) call await context.sync().
catch(errorHandler)
This catches run-time errors and takes action. errorHandler function is defined at the end.
So, you can change the background color of a cell. Now, you can do anything as far as you can find find proper object, properties and methods.
Let’s look at the next function, show_address. This function shows the Excel address of the selected cell right next to the cell.
async function show_address() {
await Excel.run(async (context) => {
var range = context.workbook.getSelectedRange();
range.load("address");
await context.sync();
var str_adr = range.address;
range.getOffsetRange(0, 1).values = str_adr;
await context.sync();
}).catch(errorHandler);
}
async and await are already explained. In this function we want to get the “address” property of the range instance to get the cell address. To read a property, it requires always to call:
range.load("address")
So, before you actually use range.address, you need to load it first. So, this is the rule. For example, you want to use “formulas” property in the range object. Then 1) load the property first by range.load(“formulas”), 2) wait by await context.sync(), then 3) access the property by range.formulas. The logic behind this is the same as before. It’s possible that another user is change the formula of the cell while you are trying to read the formula from your JS. So, wait until the other user finishes the job and once the other user is done, do your job.
You can see that whenever properties are changed, there should be await like
range.getOffsetRange(0, 1).values = str_adr; await context.sync();
This is another layer of complexity compared to VBA but inevitable in cloud-based environment.
If you need to load multiple properties at once, you can call
range.load("address", "formulas") or range.load(["address", "formulas"])
then one await.
Once you understand this, JS coding and VBA coding are almost similar and you can start converting VBA code to JS code.