How to Create a Macro in Excel: Excel VBA vs Office Scripts for Task Automation

For desktop-heavy Excel work, VBA is still the best macro choice; for browser-based Microsoft 365 workflows, Office Scripts is usually the cleaner option. Both can automate repetitive tasks, but they serve different users. VBA lives inside traditional Excel files. Office Scripts runs through Excel for the web and connects neatly with Power Automate.

TLDR: A finance analyst who formats 20 weekly sales reports can save 60 to 90 minutes by recording a VBA macro or writing an Office Script. For example, a 12-step cleanup process that takes 4 minutes per file can drop to about 15 seconds per file after automation. VBA works best when the task depends on desktop Excel, legacy workbooks, buttons, forms, or add-ins. Office Scripts fits better when a team needs cloud automation, scheduled runs, or integration with emails, SharePoint, and Teams.

VBA vs Office Scripts: the quick difference

Excel VBA, short for Visual Basic for Applications, is the older automation system built into desktop Excel. It can control worksheets, ranges, charts, PivotTables, forms, workbooks, and many parts of the Excel application itself. It is powerful, flexible, and often messy in the way only long-running business tools can be.

Office Scripts is Microsoft’s newer automation option for Excel on the web. It uses TypeScript-style code and works well with Microsoft 365. It is cleaner for cloud use, easier to share across modern teams, and useful when paired with Power Automate.

The catch is that neither option fully replaces the other. VBA does more inside desktop Excel. Office Scripts does better in the browser and in automated cloud flows. The right choice depends on where the workbook lives, who runs the automation, and whether the task needs to run without someone opening the file.

When VBA is the better choice

VBA is a strong fit when the work happens mostly in Excel for Windows or Mac. It is also the safer choice when a company already has older macro-enabled files, custom toolbar buttons, UserForms, event-based actions, or detailed workbook logic.

Best for: desktop Excel automation.
File type: usually .xlsm.
Skill level: beginner-friendly for recorded tasks, harder for advanced logic.
Strength: deep control over Excel features.
Weakness: sharing and security warnings can annoy users.

It drives many teams crazy that a simple macro workbook can trigger trust center warnings, blocked content messages, and “Enable Macros” prompts. Those warnings exist for good reasons, but they add friction. In some companies, one blocked macro can turn a five-minute report into a support ticket.

How to create a VBA macro in Excel

A user can create a basic VBA macro by recording actions. This is the easiest starting point because Excel writes the code in the background.

Open Excel on the desktop.
Go to File > Options > Customize Ribbon.
Enable the Developer tab.
Select Developer > Record Macro.
Name the macro, such as FormatWeeklyReport.
Choose where to store it: the current workbook, a new workbook, or the Personal Macro Workbook.
Perform the repeated steps, such as formatting headers, resizing columns, and applying filters.
Click Stop Recording.
Run it later from Developer > Macros.

After recording, the user can inspect the code by selecting Developer > Visual Basic. A recorded macro may look clunky, but it works as a starting draft. Extra recorded lines can be cleaned up later.

For example, a macro that formats a report could bold the first row, set column widths, freeze panes, apply a currency format, and add filters. Once saved as an .xlsm file, the workbook keeps the macro.

When Office Scripts is the better choice

Office Scripts is a better match when files are stored in OneDrive or SharePoint and opened in Excel for the web. It is also useful when automation needs to run from Power Automate, such as every Monday at 8:00 a.m. or whenever a new file lands in a folder.

Best for: Microsoft 365 cloud workflows.
File type: standard workbooks such as .xlsx can be used.
Skill level: easier for users already familiar with JavaScript or TypeScript.
Strength: sharing and scheduled automation.
Weakness: less control over older desktop-only Excel features.

Honestly, it feels like Office Scripts solves one headache and creates another. It removes many old macro security hassles, but users may hit licensing, browser, or feature limits. A script that runs smoothly in Excel for the web may not cover a legacy workbook full of ActiveX controls or form buttons.

How to create an Office Script in Excel

A user can create an Office Script directly in Excel for the web. The workbook should be stored in OneDrive or SharePoint, and the Microsoft 365 account must support Office Scripts.

Open the workbook in Excel for the web.
Select the Automate tab.
Click New Script.
Use the script editor to write or record actions.
Rename the script with a clear name, such as CleanSalesData.
Click Run to test it.
Save the script for later use.

A simple Office Script can clear blank rows, format headings, convert a range into a table, or place summary values on a dashboard sheet. If the same workbook receives new raw data each week, the script can prepare it in seconds.

Office Scripts becomes even more useful with Power Automate. A team can create a flow that starts when a file is uploaded, runs a script, then sends a message to Teams. That means the user does not need to open Excel at all.

Which one should a team choose?

The fastest answer is practical. If the workbook already depends on VBA, desktop Excel, or heavy workbook interaction, VBA is the sensible choice. If the workbook lives in SharePoint and needs scheduled or shared cloud automation, Office Scripts is the better fit.

Need	Better option
Automating old macro workbooks	VBA
Running tasks in Excel for the web	Office Scripts
Creating custom forms or workbook events	VBA
Connecting Excel to Power Automate	Office Scripts
Sharing automation across a Microsoft 365 team	Office Scripts
Using advanced desktop Excel controls	VBA

Common macro tasks worth automating

Most Excel automation starts with boring tasks. That is fine. Boring tasks often waste the most time.

Cleaning imported CSV files.
Formatting monthly reports.
Creating summary sheets.
Refreshing PivotTables.
Adding formulas to new rows.
Splitting data by department or region.
Sending processed workbook data into another workflow.

A small operations team might process 35 spreadsheets per week. If each file needs 3 minutes of cleanup, that is 105 minutes lost weekly. A macro or script that cuts the job to 20 seconds per file saves more than 90 minutes every week. Over a year, that is almost a full workweek recovered.

Best practices before using macros or scripts

Automation should not be built on a fragile workbook. A user should clean the source data, use consistent column names, and test on a copy before running anything on a live file.

Use clear names: names like FormatInvoiceReport beat Macro1.
Keep backups: macros can change many cells at once.
Test small: run automation on 10 rows before using 10,000.
Document the steps: future users need to know what the automation does.
Control access: only trusted users should edit production scripts.

FAQ

Can Office Scripts replace VBA?

Not fully. Office Scripts is great for web-based Microsoft 365 automation, but VBA still has deeper desktop Excel control.

Can a user record a macro in Excel for the web?

Excel for the web uses Office Scripts instead of traditional VBA macros. Users can record actions through the Automate tab when the feature is available.

Are VBA macros safe?

They can be safe when they come from trusted sources. Unknown macro files can carry harmful code, so organizations often block them by default.

Does Office Scripts require coding?

Basic tasks can be recorded. More advanced work usually requires editing TypeScript-style code.

Which is easier for beginners?

For desktop users, recorded VBA macros may feel easier at first. For users familiar with web tools and Microsoft 365, Office Scripts may feel cleaner.