Playbook

How to modernize Excel VBA macros

A step by step way to move Excel VBA macros to a supported runtime without rewriting everything at once, with tests, version control and rollback.

By the Cloudverg engineering team · · 6 min read

The problem

VBA macros outlive the people who wrote them. A workbook starts as a small tool for one report, then grows import buttons and pricing rules. Three years later it runs a monthly close for the finance team and nobody can explain the middle module. The code sits in a locked project on a shared drive, edited in place. Two people open it at once and the last save wins.

The code itself holds habits that make change expensive. Procedures select cells instead of using ranges. File paths such as C:/Reports are typed into the code. On Error Resume Next hides failures, so a broken run looks like a quiet one. Public variables carry state between modules. Declare statements call Windows APIs and break when the file opens in 64-bit Office.

A big-bang rewrite fails because it assumes the rules can be written down. Some can't. A rule lives in a correction a clerk makes every month, or in a cell formula that feeds the macro. The file also has to keep working on Monday, so freezing it for a six month project is not realistic. Safe modernization moves in small steps, with the old path still available.

What a good solution looks like

This playbook, How to modernize Excel VBA macros, treats the workbook as a small system. The goal is not to delete VBA. It is to give each macro one entry point, put the rules in functions that take parameters and return values, and test those functions against known inputs. Excel stays as the screen where it helps, and the logic moves to a runtime you can support.

Start with an inventory, not code. List every workbook that matters, every entry point, the data sources it reads, the files and emails it writes, the owner, and what breaks if it stops. Count buttons, Workbook_Open, Worksheet_Change and ribbon callbacks as entry points. That table decides the order of work, and it shows what to retire. Retirement is often the cheapest fix.

Pick a runtime per macro, not per company. Office Scripts handle range work in Excel on the web and run from Power Automate. They cannot call COM, open user forms, or read the local disk the way VBA can. Power Automate fits scheduled file moves and email. A database or a small service fits rules that other systems also need, like price calculation. Clean, tested VBA is a valid target for a small file with one owner.

A good end state has four supports: version control, signed files, logging, and a rollback. If any of those is missing, the migration is not finished, even when the new code works.

How to build it

Inventory and freeze. Copy every workbook to an archive folder and mark it read-only. Export modules, class modules and forms as text files with the VBIDE export or Rubberduck, then commit them to Git. Write down the import step, even if it is manual. From that day the repo is the source of truth and the shared file is a build output.

Add a safety net before you change logic. Put Option Explicit at the top of every module and fix the compile errors. Build a smoke test workbook with inputs and expected outputs for the five most used macros. Record the current output as the baseline. Add a logging routine that writes macro name, user, timestamp and result to a hidden sheet or a text file.

Then strangle one entry point at a time. Wrap the original body in a new procedure, add logging, and put a switch in a config sheet with three states: old, new, both. In both mode, run each path and compare outputs. Move one rule into a typed function, for example Function ParsePrice(ByVal raw As String) As Currency, and call it from a Tests module. Fix 64-bit declares with PtrSafe and LongPtr as you go.

Handle data and distribution next. Move connection strings and folder paths into a config table. Replace string built SQL with ADODB.Command parameters. Sign the file with a code signing certificate and place it in a Trusted Location. Keep a version number in a cell. When a macro has no callers and no owner, retire it and note that in the release.

Risks and how to manage them

Hidden callers are the main risk. Personal.xlsb can call your macro with Application.Run. A second workbook may do the same. Ribbon XML, event handlers and scheduled tasks also point at procedure names. Renaming a Sub breaks those silently. Before any rename, search every archived workbook for the name and log calls for a month.

Environment differences cause failures that never show on the developer machine. A 32-bit Declare fails in 64-bit Office. Dates flip under a different locale, and a file saved with the 1904 date system shifts by four years. Excel on the web does not run VBA, so a workbook that moves to the browser loses its buttons. Test on both bitnesses and on a machine with a different regional setting.

Data actions need a dry run. Macros that delete rows, write to shared files, or email customers should start in a mode that logs what it would do. Keep a copy of the file and an export of the data before the first live run. If something goes wrong, flip the config switch back to old. That switch is the whole point of the approach.

How to measure success

Measure coverage first. Count entry points with a passing test, entry points with logging, workbooks under version control, and modules with Option Explicit. These numbers should move every week, and they show whether the work is real.

Then measure reliability. Track errors in the log per week, manual reruns per month, and the time to fix a reported bug. Record the run time of the slowest macro against the baseline you captured. A faster macro matters less than a failed run that now leaves a clear log entry.

Measure people last. How many people can change a rule without calling the original author? How long does it take a new analyst to run the month end process? Those answers tell you whether the knowledge left the code and entered the team.

If you want a structured inventory and a risk ranked plan before touching code, the Legacy modernization sprint at $8,500/month is one option. It runs three weeks, starts within ten business days, and ends with a migration sequence and rollback points. Other options include doing the inventory in house or hiring a contractor for one module at a time.

Frequently asked questions

How do I put Excel VBA macros under version control?

Export each module, class module and form as a text file, commit those files to Git, and treat the repository as the source of truth. The .xlsm file becomes a build output that you assemble by importing the modules. Rubberduck or the VBIDE export handles the export step, and a short checklist keeps the import step consistent.

Can Office Scripts replace VBA?

Only for part of the work. Office Scripts read and write ranges, tables and formulas in Excel on the web, and they run from Power Automate or from the Automate button. They cannot call COM, open VBA user forms, or touch the local file system the way VBA can. Keep VBA for those jobs or move the logic into a service.

Is it better to rewrite or refactor VBA?

Refactor first, and rewrite only the parts that fail the inventory. A macro with one owner, a clear input and a test can be cleaned in place. A macro that hides rules in long procedures, breaks on 64-bit Office, or duplicates work done in a database is a better candidate for replacement.

Monthly plan

Want help with this? Legacy modernization sprint

$8,500/month

3 weeks · Starts within 10 business days

Three weeks to understand an old system nobody wants to touch, rank the risks and agree a step by step plan that avoids a big bang rewrite.

Renews monthly. Cancel any time.
What is included

Related guides

More custom software guides   Custom software

Smallest way to start

Get a senior architect on your problem this week

Ninety minutes a month on the cloud, AI or security question that is blocking you, a pre-read of your material and a written summary within two business days.

$450/month

Renews monthly. Cancel any time.