Most of the time that you are working in Excel, you will be working with ranges. And working with ranges in Office Script is very different than working with ranges in Visual Basic for Applications!
Using a very simple macro, we’ll compare the differences between how to get data from cells in Excel and Excel Online.
Getting Started
Before getting started with Office Script, you need to ensure that Office Scripts are available for recording. This is detailed in a prior post; recording your first Office Script.
For VBA, no special settings are required.
The Test Output
Since we’re just looking to test basic differences between VBA and Office Script, we’ll be building a very simple macro.
In this sheet, we will:
- Enter “Hello World!” into cell A1.
- Bold the text in cell A1.
- Resize column A to 159 pixels (22 point, 119.25 standard units).
- Enter the =NOW() formula into cell A2.
- Copy and paste the values into cell A2.
Let’s record the macros in VBA and Office Script and see how they are different!
The Recorded Files
For the file recorded in VBA, the code is available within a module in the file that is linked below.
For the Office Script, all the code is located within the Office Script file.
In order to ensure that the script will run in your environment, you need to copy the Office Script file into the Documents > Office Scripts path in your OneDrive for Business.
Make sure that you don’t copy it to a personal OneDrive if you have multiple OneDrives on the same workstation!
Looking at the Code
Even when looking at a very simple code recording, the code is very different! Just looking at the 2 scripts below, you can see that there are some very significant differences!
The changes go much deeper than just the fact that Office Script is TypeScript as opposed to Visual Basic!
Office Script
VBA
What are the major differences?
Even in a simple 8 line macro, there are some very important differences between the two recorded macros!
- Office Script has a let selectedSheet to start the code.
- getRange and setValues methods are used to enter values into cells in Office Script.
- Office Script uses a getFormat method.
- VBA uses a Copy / PasteSpecial, whereas Office Script uses a copyFrom method due to the lack of a clipboard.
Ranges in Office Script require a worksheet to access.
In Office Script, the first lines of your code will typically be establishing what worksheet that you are using for your code!
Every range function requires a worksheet in order to properly access a range on the worksheet.
VBA; however, does not require the worksheet name, as the active worksheet and workbook will be used if no name is provided.
In Office Script ranges, you need to identify ranges before changing values.
In VBA, the following code will insert the text “Some Value” into cell A1. However; this will not work in Office Script, even if the worksheet is properly identified.
In the VBA code, the Values property is used by default. As a result, the code below will achieve the same objective.
While it is more verbose, it provides explicit details as to which sheet is being used and what property is being modified.
The VBA code with no variables being used closely resembles an Office Script where no variables have been created for active workbooks.
Using Brackets for Objects
In VBA, when referring to an object that is a child to another object (like a worksheet within a workbook, or a cell on a worksheet), a period is used to identify the new object.
In Office Script, objects require brackets to identify (similar to the way parameters are used in functions).
Separate Function to Set Values
In VBA, value is a property of a cell. This means that a value can be directly assigned to a cell. In the example below, cell A1 is assigned “Some Value”.
With Office Script, a set method is required in order to modify values in a cell or range.
Set functions are required to modify other properties as well, including formatting.
Properties such as formatting have their own methods.
There are 2 key differences between VBA and Office Script in this example with respect to formatting:
- The font in Office Script is a subset of the getFormatting() method; in VBA fonts are subsets of the range.
- A set method is required to bold a cell, similar to when setting a value.
Fonts are no longer subsets of the range.
In VBA, fonts are a property of the range. So, as a result, the font can be called directly against the range.
However, in an Office Script, formatting such as fonts and row height are stored under a getFormat() object.
Set methods are required to adjust formatting.
Similar to adding a value to a spreadsheet, changing formatting also requires a set method.
In VBA, the bold font is assigned directly by just stating that the bold is true.
In Office Script, bold cannot be assigned directly to a cell – so a setBold method is used instead.
There is no clipboard. All Office Script ranges are copy / pasted in the same line.
When working with VBA, the macro recorder will copy results to the Windows clipboard. After the results have been copied to the clipboard, they can be pasted back to Excel.
However; in Excel Online, there are limitations for copying and pasting due to browser technology.
Due to this fact, the copying and pasting need to occur in the same command. There is no paste method in Office Script.
Instead, the copy and paste is achieved using the copyFrom method. If other steps are taken between the cutting and pasting (e.g. adding new values in another cell), they will be added in the code before the copyFrom method!
Conclusion
While the largest difference between VBA and Office Script is the TypeScript syntax, the additional security required in order to run Excel in a browser needs to also be taken into account.
This results in some actions needing to be coded very differently in Office Script versus VBA!