12. Macros and VBA

12.18 Recording an Office Script and Reading the Code

By Ravindra Bagale · Last updated: 6 October 2026

Example first. Zoya does not want to write code yet. She wants Excel to watch her once: make the header row bold, fill it orange, and turn A1:I11 into a Table. The Action Recorder writes the script for her. Then she opens the code and finds that she can read most of it, because each recorded action becomes one line with a comment.

First look at the data (before → after):

Expected result — After running the recorded script

About this data: Teaching sample for a fictional shop “FreshGo Mart”. Figures and names (e.g. Aarti Kulkarni) are made up for this lesson on recording an office script and reading the code.

Automate › New Script › Create from Recording
do the steps … › Stop
Rename the script, then Edit to read the code

What the recorded code looks like (illustrative: your recording may differ slightly, for example in the colour code or comment wording)

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Set font bold to true for range A1:I1 on selectedSheet
    selectedSheet.getRange("A1:I1").getFormat().getFont().setBold(true);
    // Set fill color to #FFC000 for range A1:I1 on selectedSheet
    selectedSheet.getRange("A1:I1").getFormat().getFill().setColor("#FFC000");
    // Add a new table at range A1:I11 on selectedSheet
    let newTable = workbook.addTable(selectedSheet.getRange("A1:I11"), true);
}

About this data: Made-up rows so you can copy the pattern for recording an office script and reading the code. Currencies may use ₹, £, € or $.

Reading pattern. Almost every line follows "get something, then do something to it": getRange(…) › getFormat() › getFill() › setColor(…). Methods that start with get fetch an object or a value; methods that start with set change something.

Absolute vs relative recording (from Microsoft's recording page)

About this data: Fictional worksheet used in class. Safe to edit; nothing here is live production data.

To switch, open the drop-down menu in the Record Actions pane and select Relative mode. (This is the same idea as Use Relative References for VBA macros in lesson 12.2.)

Things to fix after recording

1. getActiveWorksheet() depends on which sheet is open. Replace it with workbook.getWorksheet("Orders") before you run the script from Power Automate; Microsoft's tutorial says flows should not use relative references like the active worksheet.
2. Running the script twice adds a second Table on top of the first and fails. Recorded scripts do not check "is it already done?"; you add that logic in the Code Editor (lesson 12.19).
3. Recorded scripts repeat the same getRange call on every line. Store it once in a variable, for example const header = selectedSheet.getRange("A1:I1");.

Microsoft sources: Record your actions as Office Scripts; Tutorial: automatically run scripts with Power Automate.

Practice task (try it before you open the answer):

1. Record a script that sets the number format of G2:G11 to show two decimals and makes column G wider.
2. Open it in the Code Editor and find the line that changes the number format.
3. Change the script so it uses the sheet named Orders instead of the active sheet.

Follow the formula or ribbon steps shown in this lesson (keep values numeric).

Quick revision cards

Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.