Excel VBA vs Office Scripts
Do you need to switch?
If you have used macros in Excel you know that they can save you a tremendous amount of time when performing common tasks along with greatly improving the accuracy of your workflows.
Simple macros (defined as macros with basic, linear process flows) can be easily created using the macro Recorder. More complex macros, like those that may solicit the user for input, contain branching logic, or perform error checking need to be written by hand inside the macro code editor.
Macros are written in a language called Visual Basic for Applications, or VBA for short, which is a subset of Visual Basic.
How do Office Scripts fit into this workflow automation scheme? What are some of the differences between VBA macros and Office Scripts? Are Office Scripts the successor to VBA macros?
Let’s look at the facts and see if this New Kid on the Block is here to dethrone Old Faithful.
“What do VBA and Office Scripts have in common?”
- VBA and Office Scripts are used to automate common, repetitive tasks.
- The target audience for both tools is business users. It is not required that the user be an experienced programmer, but programmers will have an easier time learning the tools.
“Where do VBA and Office Scripts differ?”
- VBA is limited to use in the desktop application space. VBA is not supported in the Web version of Office or when using Microsoft Teams.
- Office Scripts are currently limited to use in the Web version of Office applications and Microsoft Teams, but it is likely that Office Script functionality will eventually migrate down to the desktop application environment.
Based on the above statements, it appears as though one of these tools has a brighter future than the other. Can you guess which?
Examples of VBA and Desktop Macros
Simple VBA Macros
Below is a dataset and a chart. The chart was created from the dataset using a pre-recorded VBA macro.
The macro was written in such a way that it can dynamically detect the number of rows in the data and build the chart accordingly.
Creating the macro was done using a tool called the Macro Recorder. This tool writes the VBA code for us based on observing which buttons are pushed and what data is selected. It did require a small amount of hand-coding to dynamically determine the data range, but the vast majority was created without needing any knowledge of the VBA language.
This macro is showcased in a previous post/video. If you are interested in learning how this was made, click the following link for a complete, step-by-step tutorial and video on the construction of the macro and chart.
Running a VBA Macro
Macros can be launched in a variety of methods:
- Select View -> Macros -> View Macros -> {select macro by name} -> OK.
- Selecting Developer -> Macros -> {select macro by name} -> Run.
- Assign a shortcut key (ex: CTRL-r).
- Assign a launch button to the Quick Access Toolbar.
- Assign a launch button to a ribbon.
More exotic users may even assign macros to shapes, images, or icons that when clicked will execute the macro. Those are the REALLY cool kids.
Complex VBA Macros
VBA Macros can be as simple or as complex as you need them to be.
Below is an example of a set of macros that solicit the user for a data file, create a report based on the user’s needed topic, export the results a data file or PDF, etc.
The user clicks code launch buttons to execute specific tasks in any order they choose.
The macros can perform tasks in a variety of order and purpose. This makes for a highly dynamic environment where the output can be different from use to use. The logic is not confined to a predetermined linear path.
Unlike the simple VBA macro example, this solution can’t be created using the Macro Recorder. The branching logic, process looping, and user prompts are too complex and require hand-crafted code to operate properly.
If you are interested in learning how to write VBA code like the example above, check out my full VBA course on the XelPlus website.
Unlock Excel VBA & Excel Macros
Office Scripts
Remember, Office Scripts are only available when using Excel (or other Office 365 applications) on the Web.
Login to the Office.com website with your Microsoft 365 account, launch Excel for the Web, and start a blank workbook.
Select the Automate tab.
From here, you can record your actions, view your scripts, or try out some pre-recorded scripts.
NOTE: If you do not have the Automate table it is because either your network administrator has deactivated this feature via a policy, or you don’t have a Microsoft 365 Business or Enterprise account.
Recording an Office Scripts “Macro”
Just like in Excel and VBA, we can record a “macro”, or in this case, a script.
This tool will automatically generate the necessary code based on your actions.
The “Record Actions” feature isn’t as sensitive as the VBA Recorder; some actions are not recorded using this tool. Admittedly, the VBA Recorder doesn’t catch every action either, but it is a bit more robust than the “Record Actions” feature.
Keep in mind, Office Scripts is still in its infancy compared to VBA which has been around for decades and has seen dozens of development cycles.
The fact that we have a script recorder at all makes it easier for business users to begin learning the underlying code.
Recording a Script
We can record a simple Office Script by pressing the “Record Actions” button, then perform a series of common tasks, such as:
- Enter text in cells, like titles and subtitles.
- Make the text bold.
- Change the text color.
- Change the font size.
- Add a border to the title.
The above actions are recorded by the script recorder and listed on the right of the window.
We stop the recorder and give the script the name “Formatting”.
Clicking the Edit button will display the underlying script code.
The code is written in a language called TypeScript which is a superset of JavaScript which adds optional static typing to the language.
You can write traditional JavaScript in the code window if needed.
Automatic Documentation
A fantastic feature of the script recorder is the inclusion of automatic comments to document the code in a more natural language format.
“Where are Scripts Saved?”
Scripts are saved in a file that is separate from the Excel file from which it was created. The scripts are saved in a file named “OfficeScripts” in a folder on your OneDrive site.
This means that the script is easily repurposed for use in any other Excel file. This is like storing VBA Macros in the Personal Macro Workbook.
The scripts can be connected to a file, so it travels with the file when shared with other users.
I can go to any other Excel file and run this script from the Automate ribbon.
“What about looping, IF statements, & more complex code?”
More complex code can be hand-crafted in the Code Editor, but there are many differences between VBA and TypeScript.
One difference is object selection. In VBA, if you perform an action, like selecting a cell, and you do not tell VBA which sheet holds the target cell, VBA assumes you want the cell on the currently selected sheet (i.e., the Active Sheet).
In TypeScript, you are required to tell the code which sheet you wish to manipulate.
Another HUGE difference between VBA and Office Scripts is the method by which target ranges are selected.
If we wish to get the cell address of the first cell on the sheet (the cell in the first column and the first row), we could write a statement like the following.
let MyAddress = selectedSheet.getCell(1,1).getAddress()
We will display the results using the Console Log feature. This will display the results on the screen in the Code Editor panel.
console.log(MyAddress)
If we ran our code on a sheet named “Sheet3” we will see the following result.
Notice that TypeScript thinks that the cell on Row 1 and Column 1 is cell B3. This appears to be incorrect.
The issue is that Office Scripts is a zero-based language; it starts counting at 0 (zero) instead of 1 like VBA.
We need to adjust our brains to remember to offset the position value by one, like in the following example.
let MyAddress = selectedSheet.getCell(0,0).getAddress()
Office Scripts Limitations
Remember when we launched a VBA Macro we had many ways to perform this task, like assign macros to icons and launch buttons.
Sadly, we are not able to attach Office Scripts to shapes and icons. This functionality will hopefully appear in a future update.
Another limitation is the inability to have pop-up boxes and message boxes.
We are also restricted from using Events to trigger the execution of scripts. Excel can launch a macro based on an event, like the contents of a cell changing or the selection of a sheet.
That doesn’t mean it’s all bad news.
We can use Power Automate to trigger a script’s execution. This means we don’t even have to have Excel open to run a script.
We use Power Automate based on a trigger. The trigger can be a scheduled time, the receipt of an Outlook email, the selection of specific rows in a different Excel file. The list of trigger options is vast and ever-growing.
“What’s in store for the future?”
In an upcoming video (next week) we will look at how to create a column chart using dynamic ranges. We will use the Script Recorder to write most of the needed code, then we will make some minor adjustments to the code to make it more dynamic.
“Is this the end of VBA?”
Are Office Scripts destined to replace VBA? I think eventually yes, but not any time soon.
Many companies use the Desktop version of Excel, a domain where Office Scripts currently have no residence.
Many companies will not be upgrading to Office 365.
Much time and money have been invested in complex VBA solutions to automate many business processes. These processes cannot at present be upgraded to Office Scripts due to incompatibility or lack of features.
Likely, VBA will still be around for another decade or so.
ليست هناك تعليقات:
إرسال تعليق