What are the differences between Excel Macros and Scripts?

Mar 14, 2024 | Excel Hints and Tips

At first sight these 2 options are very similar.  They both allow users to automate repetitive tasks by running a set of steps that have either been recorded or programmed.  Although these steps may need to be edited you do not need advanced programming skills to still get a lot out of either of these solutions.

When can I use macros or scripts?

They are both be used in situations where you need to do the same thing over and over again.  Often these are situations where data you have been provided with needs to be cleaned.  This could include steps such as removing specific rows or columns, using find and replace to help with consistency in the data and applying formatting.  This is just one of many situations where this can be used  but most features in Excel can be recorded by a script or macro.

What are the differences between scripts and macros.

The fundamental difference is that VBA macros are developed for desktop solutions and Office Scripts are designed for secure, cross-platform, cloud-based solutions.

Also VBA macros are available in other Microsoft products while scripts are only available in Excel.  However, Excel VBA is not available in Excel for the web, only the desktop application while scripts can be used from either.

VBA can connect with COM and OLE but has no way to call out to the internet.  Scripts use JavaScript and can make calls to a limited set of web services.

Security

VBA macros have full access to your desktop where as scripts only have access to the workbook.  There is also more flexibility for the administrator to turn scripts on and off for different groups of staff than there is for macros.

Admins have three options for VBA macros: allow all macros on the tenant, allow no macros on the tenant, or allow only macros with signed certificates. This lack of granularity makes it hard to isolate a single issue. Currently, Office Scripts can be off for an entire tenant, on for an entire tenant, or on for a group of users in a tenant. Admins also have control over who can share scripts with others and who can use scripts in Power Automate.

Features Covered

Currently VBA has more complete coverage of Excel features as it covers all the features available on the desktops app.  Office script only covers those features on Excel on the web but as more features get added here these will also be supported.

PowerAutomate

There is a big difference here.  Scripts have to be run manually or using a PowerAutomate flows while macros can be run automatically by Excel level events such as opening the workbook.  VBA cannot be run though a PowerAutomate flow.

Summary

So as you can see there are pros and cons for both these options so there may be situations where one is favoured over the other.  It will be interesting to see if macros are retired at some point with the newer script taking over.

Further Reading

If you’ve enjoyed reading about the differences between Excel Macros and Scripts, there are some other blogs below that you might find useful:

Want to learn more about Microsoft Excel? Then email lara@laramellortraining.co.uk to discuss how I can help or have a look at the Microsoft Excel Courses I run.