Simple Office Script in Excel

If you want to automate something in Excel, you might want to use Macros and Visual Basic. That’s what I have been doing.

Let’s take a very simple example. When you have a sum cell, the result should be framed with a bigger font.

When I recorded the macro, the result was sum-macro. The VBA script is attached to the end of this blog.

Check if you have automate tab available in the Excel.

We can record new scripts in a bit similar way as recording a VBA macro.

Press “Record Actions”.

Record actions is writing a log.

Frame the cell and make the font bigger.

The Record Actions recorded my actions.

Press stop.

I can change the script name.

I replaced the “Script 4” by more descriptive “Font_frame”.

This is the start of the script. Green lines with slash slash are comment lines. The script has generated also comments, which is a benefit compared to VBA.

In the sixth line, the script has created a static cell reference, C4. It would be better if we had relative references like active cell. The changes are done into active cell, not C4, unless C4 is the active cell.

We need to change the selectedSheet.getRange(“C4”) with workbook.getActiveCell().

I also changed the comments, even though it does not affect the result. The change should be done to active cell, not any predefined cell.

Office Scripts are stored in OneDrive as osts files.

If you want to execute an Office Script, select “All Scripts” under automate ribbon. Then press play.

The result.

The VBA and Office script are doing the same task, they both make frame to selected sell and make the font bigger. The VBA code is quite long, even though I expected the VBA to be shorter clearer than Office Script.

VBA is older technology than Office Script. It is good to familiarize yourself with Office Scripts.

Here are the scripts.

Sub sum()

‘

‘ sum Macro

‘

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

Selection.Borders(xlInsideVertical).LineStyle = xlNone

Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

With Selection.Font

.Name = “Aptos Narrow”

.Size = 14

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontMinor

End With

End Sub

And here is the Office Script, I used.

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Set font name to “Aptos Narrow” for selected range on selectedSheet

workbook.getActiveCell().getFormat().getFont().setName(“Aptos Narrow”);

// Set font size to 16 for selected range on selectedSheet

workbook.getActiveCell().getFormat().getFont().setSize(16);

// Set font strikethrough to false for selected range on selectedSheet

workbook.getActiveCell().getFormat().getFont().setStrikethrough(false);

// Set font superscript to false for selected range on selectedSheet

workbook.getActiveCell().getFormat().getFont().setSuperscript(false);

// Set font subscript to false for selected range on selectedSheet

workbook.getActiveCell().getFormat().getFont().setSubscript(false);

// Set font underline to “none” for selected range on selectedSheet

workbook.getActiveCell().getFormat().getFont().setUnderline(ExcelScript.RangeUnderlineStyle.none);

// Set font color to “#000000” for selected range on selectedSheet

workbook.getActiveCell().getFormat().getFont().setColor(“#000000”);