VBAtoPython
Start Conversion
← All articles

What VBAtoPython Handles — And What It Doesn't

Honest coverage guide for the VBAtoPython converter. PASS, CLEAN, and USABLE rates on real-world corpora — and why we deliberately warn instead of guess.

Most VBA-to-Python converters either quietly produce broken code or hallucinate an idiomatic pandas rewrite and hope you don't notice the behavior change. We don't. This page is the honest reference for exactly what VBAtoPython handles, what it handles with warnings, and what it won't touch.

Our converter is deterministic and rule-based. It translates your VBA line-by-line — the output preserves the structure and control flow of your original code so you can audit it. That's the pitch for finance, payroll, and accounting teams who need to know their macro still does the same thing after the port.

Three numbers, not one

We benchmark against two corpora and report three rates, because they answer different questions:

  • PASS — 79.7% on a 374-file real-world corpus scraped from GitHub. The converter produces Python that compiles (passes ast.parse). Some files in this bucket also emit warnings — patterns where the translation might behave differently from VBA and you should review before running.
  • CLEAN — ~57% on the same corpus. Compiles and zero warnings. No review needed. Paste, run, ship. The remaining ~22 percentage points between PASS and CLEAN are files where we deliberately surface warnings rather than silently guess (see next section).
  • USABLE — 92.5% openpyxl, 82.9% libreoffice on a 42-case curated suite where we measure end-to-end runtime equivalence — does the converted Python actually produce the same workbook as the original VBA, when run? Three cases are documented architectural divergences (formula evaluation under openpyxl, scalar ByRef mutation, nested With blocks) and excluded from the denominator. Cases include real-world patterns from GitHub repos (UPC check digit, bubble sort, AR aging, fiscal quarter labelling, data fill-down) alongside team-authored stress tests.

On a finance-specific stress test (Monte Carlo simulators, option pricers, investment add-ins), CLEAN is higher — finance code is mostly the converter's strongest bucket (numerical, array, control-flow heavy).

Every rate is gated on every commit. The pre-push hook + GitHub Actions block pushes that drop any rate below its floor. When a customer reports a regression, it becomes a corpus case — once added, the gate makes sure it never breaks again.

Why we deliberately stop at ~60% CLEAN

The remaining ~40% of files aren't bugs we haven't gotten to — they're patterns where pushing higher would mean either guessing at semantics or producing confidently-wrong Python. We choose to warn instead. Specifically:

  • On Error Resume Next blocks (~11% of corpus). The "silently skip the bad line and check Err.Number afterward" idiom doesn't have a single right Python translation — the correct rewrite depends on intent we can't infer from the source alone. We emit the structure with a warning rather than fabricate try/except boundaries.
  • Win32 API calls (kernel32, user32, gdi32 — MoveMemory, GlobalAlloc, GDI graphics, etc.). No Python equivalent exists for most of these. We translate the timing APIs (GetTickCount, Sleep, QueryPerformanceCounter) with shims; the rest get an honest warning.
  • PivotTable creation, SpecialCells, charts. openpyxl can't create PivotTables — pandas pivot_table is the right target, but the source/field/aggregation structure needs a human to redesign. Same for chart objects and conditional-formatting rules.
  • UserForms and ActiveX controls. Pure UI code with no Python target. Strip + warn — there's nothing to translate.
  • Currency/Decimal precision, ByRef mutations, Range.Value vs Range.Value2. Translatable, but the semantics differ in ways that matter for payroll and accounting. We flag them so you don't ship a $0.01-rounding-error to production.

A non-deterministic AI layer could paper over these — guess at the right try/except shape, hallucinate a PivotTable rewrite, infer ByRef intent. But that breaks the core promise: same input always produces the same output, every change is documented, nothing is silently rewritten. CLEAN is capped because we chose to warn rather than guess. Competitors that quote higher numbers are either measuring PASS-equivalent (counting compile-success regardless of correctness) or using AI under the hood and trusting you not to check the diff.

How the converter doesn't crash on stuff it doesn't know

Every converted Python file includes a runtime safety net. When the converter sees a VBA API it doesn't yet translate (most often something like Application.WorksheetFunction.SumIfs or ActiveWindow.View), the safety net catches the call at runtime and prints a one-line warning to stderr instead of crashing the script.

In practice: a 500-line VBA file with 5 unmapped API calls produces 495 lines of correct output plus 5 named warnings — not a NameError on line 17 that kills the rest. The warnings tell you exactly what needs manual attention, with the unmapped name in the message.

This is a deliberate trade-off for trust. The customer gets a working baseline plus a precise punch list, instead of a binary "your script doesn't run." If the unmapped call was load-bearing for your business logic, you'll see the wrong number in the output and the warning explaining why — which is more actionable than a crash.

Two runtime targets

The converter emits Python tuned for one of two runtimes. Customers pick on conversion:

  • openpyxl (default) — pure Python, fastest, runs anywhere with pip install openpyxl. Cannot evaluate formulas — cells holding =A2+B2 return None when read.
  • libreoffice — emitted Python drives a headless LibreOffice instance via UNO. Formulas evaluate, WorksheetFunction.X calls can be delegated to LO, and Excel-style cell semantics work end-to-end. Requires python3-uno plus a system LibreOffice install.

The libreoffice target is the bigger differentiator versus naive line-by-line converters. If your macro reads back the result of formulas it wrote, you need this — openpyxl alone gets it wrong.

✅ Handles cleanly — no review needed

These patterns produce Python that runs without edits. Faithful line-by-line translation, preserved control flow, named variables kept intact.

Procedures and control flow

  • Sub / Function / Property Get/Let/Set with Dim, type hints, parameters, ByVal (and ByRef for objects/arrays)
  • If / ElseIf / Else / End If
  • Select Case → if/elif/else (numeric ranges, string match lists, Case Else)
  • For/Next, For Each/Next (including For Each over a Range — flattened automatically), Do/While/Loop, Exit Sub/Exit Function
  • With blocks flattened
  • ReDim / ReDim Preserve

Standard modules and class modules

  • .bas standard modules with Sub/Function definitions
  • .cls class modules with private/public members, Property Get/Let/Set
  • Multi-module projects — class modules talking to standard modules via method calls

Strings and numbers

  • Trim, Left, Right, Mid, Len, InStr, InStrRev, Replace, LCase, UCase, Split, Join, StrReverse, StrComp, StrConv, String(), Space()
  • Chr, Asc — and their Unicode counterparts ChrW → chr(), AscW → ord()
  • Format (common patterns: currency, dates, numbers, percent — see warnings below for edges)
  • Arithmetic, comparison, Like operator, Mod (with VBA's truncate-toward-zero negative semantics — not Python's % which floors)
  • Val, CStr, CInt/CLng/CDbl/CSng/CBool/CByte/CDec type conversions (with VBA's banker's rounding for the int casts; CDec → decimal.Decimal for full precision)
  • CDate, IsNumeric, IsDate, IsNull, IsEmpty, IsArray, IsObject, IsError
  • Math: Abs, Round, Sqr, Exp, Log, Sgn, Int, Fix
  • Financial: PMT, FV, PV, NPV, IRR, DDB, SLN — implemented as pure-Python helpers (no numpy_financial dependency)
  • Dates: Now, Date, DateSerial, DateAdd, DateDiff, DatePart, Year, Month, Day, Hour, Minute, Second, Weekday, MonthName, TimeSerial

Collections and dictionaries

  • Scripting.Dictionary → Python dict (with .Add(k, v), .Exists(k), .Keys, .Items, .Count, .Remove(k) mappings)
  • Collection → vendored helper class with VBA's mixed list/keyed-map semantics: positional 1-based indexing, optional string keys, .Add(item, key), .Remove(key), .Count
  • Add, Remove, Count, Exists, iteration

Basic Excel I/O

  • Range(...), Cells(r, c), .Value
  • Range("D" & i) style concat-indexing → ws[f"D{i}"] via f-string (no str+int errors)
  • Sheets(N) / Worksheets("Name") → workbook.worksheets[N-1] / workbook["Name"]
  • Multi-sheet patterns: wsOut.Range("A1").Value = ..., Worksheets("Input") and Worksheets("Output") cross-sheet reads/writes
  • Workbooks.Add, .Open, .Save, .SaveAs, .Close → openpyxl.Workbook(), load_workbook(), .save(), .close()
  • Cells(r, c).End(xlUp).Row → ws.max_row; Cells(r, c).End(xlToLeft).Column → ws.max_column; End(xlDown) → ws.max_row
  • Cells(r, c).Offset(dr, dc) → ws.cell(row=r+dr, column=c+dc)
  • .ClearContents → .value = None; .Address → .coordinate
  • Range.Replace(What="x", Replacement="y") → _vba_range_replace(ws["range"], "x", "y")
  • Range.Find(What="x") → _vba_find_in_range(ws["range"], "x")

Excel structural operations

  • Rows(n).Delete → ws.delete_rows(n); Columns(n).Delete → ws.delete_cols(n)
  • Rows(n).Insert → ws.insert_rows(n); Columns(n).Insert → ws.insert_cols(n)
  • Range("A1:B3").Merge → ws.merge_cells("A1:B3"); .UnMerge → ws.unmerge_cells(...); .MergeCells = True/False

WorksheetFunction arithmetic

The common aggregation functions are now translated to native Python — no safety net needed:

  • WorksheetFunction.Sum(...) → vba_wf_sum(*) (handles variadic args and range iterables)
  • WorksheetFunction.Average(...) → vba_wf_average(*)
  • WorksheetFunction.Max(...) / Application.Max(...) → max(...)
  • WorksheetFunction.Min(...) / Application.Min(...) → min(...)
  • WorksheetFunction.Count(...) → vba_wf_count(*) (numeric-only count)
  • WorksheetFunction.CountA(...) → vba_wf_counta(*) (non-empty count)
  • WorksheetFunction.Round(n, d) → round(n, d)
  • WorksheetFunction.Trim(s) / Application.Trim(s) → strips and collapses internal spaces
  • WorksheetFunction.Clean(s) / Application.Clean(s) → removes non-printable characters
  • Application.InchesToPoints(n) → n * 72

Complex lookup functions (SumIfs, VLookup, Index/Match) still fall through to the safety net — see warnings section below.

File I/O

  • Open / Close / Print # / Input # / Line Input #
  • Dir(pattern) file iteration → glob.glob() with proper next() iterator state
  • MkDir, RmDir, ChDir, Kill, FileCopy
  • Basic FileSystemObject operations → pathlib.Path

Win32 timing APIs

  • Sleep, GetTickCount, GetTickCount64, timeGetTime, QueryPerformanceCounter, QueryPerformanceFrequency, GetSystemTime, GetLocalTime → vendored shims that match the VBA semantics

External COM (the ones we cover)

  • MSXML2.DOMDocument / Microsoft.XMLDOM → xml.etree.ElementTree
  • MSXML2.XMLHTTP / WinHttpRequest → urllib.request-based helper
  • System.Security.Cryptography.* (SHA1/SHA256/SHA384/SHA512/MD5, HMAC variants) → hashlib / hmac
  • System.Text.UTF8Encoding (and ASCII/Unicode/UTF7/UTF32) → builtin .encode / .decode
  • ADODB.Stream → vendored helper class with full file/text/binary API

Error handling (basic cases)

  • On Error GoTo Label → try / except with labeled handler
  • On Error GoTo 0 → reset handler

⚠️ Translates with warnings — review before running

The converter produces working Python, but the output may behave differently from your VBA. Every conversion comes with a warning report listing these patterns explicitly. Always test before running on production data.

Range bulk-array round-trip — the highest-priority gap

arr = Range(Cells(1,1), Cells(n,m)).Value (read a 2D Variant array, then access via arr(i, j)) translates to a per-cell loop, not a true 2D-array round-trip. VBA's 1-based, row-major Variant arrays don't have a clean Python equivalent for arbitrary access patterns. The output works for sequential reads; dynamic indexing patterns may need manual review. This is the #1 item on our roadmap.

Financial math — Currency type precision

VBA's Currency type is fixed-point (scaled to 4 decimal places). Python's float isn't. For payroll, accounting, or anything involving cents, the translated code may be off by a rounding unit on edge cases. We flag this with a warning. Recommended: wrap money calculations in decimal.Decimal with quantize() before comparing or storing. If your macro processes payroll or reconciles accounts, review these lines by hand.

ByRef scalar parameter mutations

Python primitives are pass-by-value. If your VBA sub modifies a ByRef Long or ByRef String and expects the change to persist in the caller, the Python version won't. We warn on every case. Fix: refactor the sub to return the new value and assign it at the call site. (Object and array ByRef works correctly because Python passes references for those — only scalars need the rewrite.)

On Error Resume Next + If Err.Number checks

The "silent fail, then check Err.Number" idiom doesn't round-trip cleanly. In VBA, the bad line is silently skipped; in the generated Python, it'll either raise or hit our safety net. We emit a try/except shell, but the per-statement resumption semantics differ. Review these blocks before running on real data.

Range.Value vs Range.Value2

We treat them identically. If your macro relies on .Value2 returning raw unformatted doubles (common for performance-critical date or currency reads), you'll see type mismatches downstream. We flag the distinction.

Application.WorksheetFunction. — lookup and statistical functions*

Common arithmetic functions (Sum, Average, Max, Min, Count, CountA, Round, Trim, Clean) are now translated directly — see the "Handles cleanly" section above. The remaining lookup and statistical functions (SumIfs, VLookup, Index, Match, CountIf, SumIf, Rank, Large, Small, statistical distributions) are caught by the runtime safety net: the call doesn't crash, but it returns a stub and logs a warning. For these, the safety net keeps your script running while you decide whether to manually rewrite to df.query() / df.merge() / pandas equivalents. The libreoffice target sidesteps this — the function is evaluated by LO directly.

Collection + On Error Resume Next key probe

VBA developers often use New Collection with On Error Resume Next to test whether a key exists — catching the error when the key is missing. The converter detects this pattern and emits a # TODO: comment explaining the fix:

# TODO: Collection key-probe via On Error Resume Next detected.
# Python fix: replace Collection with dict and use dict.get(key, default).
# Example: existing = totals.get(region, 0); wasMissing = region not in totals

The surrounding code still runs, but the key-existence logic will need a manual rewrite to dict.get().

Format() pattern coverage gaps

Common patterns (currency, dates, percent, fixed-decimal) work. Some specific edges are partial: $#,##0.00 prefix style, 0.00% percent multiplication, leading-zero zero-pad with custom widths. Diff against your VBA output before relying on Format() for downstream parsing.

ListObjects (Excel Tables) and SpecialCells

openpyxl has partial Table support, but methods like DataBodyRange, ListRows.Add, and SpecialCells(xlCellTypeVisible) don't have direct equivalents. We emit guidance in comments; you'll need to adapt.

Charts and Conditional Formatting

openpyxl supports basic chart types and conditional-formatting rules; the converter passes through chart-related VBA constants (xlColumnClustered, xlLineMarkers, etc.) so they don't NameError. Complex chart APIs (ChartObjects.Add with custom layouts, data-driven SeriesCollection, multi-rule conditional formatting) translate partially or hit the safety net. If your chart logic is non-trivial, easier to rebuild in openpyxl or matplotlib from the translated data-prep code.

❌ Not supported — by design

These categories are out of scope. We document them clearly so you don't send them through expecting magic. If your macro is mostly one of these, our converter isn't the right tool.

UI code — UserForms, ActiveX controls. No openpyxl/Tkinter equivalent that's mechanical. Rebuild in Tkinter, PyQt, or a web framework. Safety net catches the references so the script still runs.

Event handlers — Workbook_Open, Worksheet_Change, Application.OnTime. Python has no Excel event model. If you trigger logic on workbook open, convert to a scheduled script or a file-watcher.

PivotTables — pandas pivot_table is the target, but the translation isn't mechanical. The source/pivot-field/aggregation structure needs a human to redesign.

Win32 Declare PtrSafe statements (beyond timing APIs) — MoveMemory, GlobalAlloc, clipboard APIs, etc. The timing ones we translate; everything else hits the safety net. Use ctypes manually in Python if you actually need them.

Memory pointer functions — StrPtr(s) and VarPtr(x) return the memory address of a VBA variable. Python has no equivalent. The converter emits a # TODO: comment on every line that uses these:

# TODO: ptr = StrPtr(s)  # StrPtr — no Python equivalent. Remove, or use ctypes.addressof() for Win32 calls.

UI file dialogs — Application.GetSaveAsFilename and Application.GetOpenFilename show an OS file-picker dialog. The converter emits a # TODO: with the Python equivalent:

# TODO: fname = Application.GetSaveAsFilename  # Use: tkinter.filedialog.asksaveasfilename(defaultextension='.xlsx')

Column AutoFit — .AutoFit on a range or column object is a display-only operation. openpyxl doesn't support automatic column width fitting. The converter emits a # TODO: with the manual alternative (ws.column_dimensions['A'].width = 20).

COM automation we don't cover — Outlook.Application, Word.Application, WScript.Shell, Shell.Application, CDO.Message, InternetExplorer.Application. These need pywin32 and a manual rewrite; the semantics aren't preservable through automatic translation. The safety net catches the calls so the surrounding script keeps running.

Full ADO/ADODB Recordset navigation — .MovePrevious, .Bookmark, multi-resultset navigation. Basic Open / Execute / Fields / MoveNext are partially supported, but complex cursor logic needs a manual port to sqlite3 or SQLAlchemy.

Data Validation rules — Excel-specific constraint system. openpyxl has DataValidation but the surface differs; we emit a partial translation with a warning.

How we keep this honest

Every claim above is gated by automated tests. The 374-file PASS rate, the 42-case USABLE rate (both targets), and 760+ golden-snapshot tests run on every commit via GitHub Actions. The pre-push hook + CI block pushes that drop any rate below its floor (75% PASS, 70% openpyxl USABLE, 75% libreoffice USABLE). When a customer reports a regression, it becomes a corpus case — once added, the gate makes sure it never breaks again.

We re-test the rate on every commit. The current numbers were verified by stress-testing with diverse cases (recursive functions, bubble sort, AR aging reports, fiscal quarter labelling, real-world UPC check digit from a GitHub repo, nested With blocks, long mixed-pattern macros, deep conditional logic, string-parsing pipelines) — not just the patterns the converter originally targeted. When stress-testing surfaced gaps, we fixed them and re-measured. The numbers above are the post-fix verified rates.

What the rate does and doesn't tell you

Honest disclosure: the 42-case USABLE corpus is curated by the team, not randomly sampled from customer code. The corpus includes both team-designed stress tests and cases adapted from real GitHub VBA repositories, but it is not a random sample of what customers actually paste. We've stress-tested by adding cases the converter wasn't tuned for, fixing the bugs that surfaced, and re-measuring. But a curated corpus is structurally different from "the rate on arbitrary customer files."

So:

  • 92.5% / 82.9% is the verified rate on these 42 cases. Useful for gating regressions and comparing fixes against each other.
  • It is NOT the same as "92.5% of customer files convert correctly." We don't know that number yet — and anyone who claims they do without telemetry on real customer conversions is guessing.
  • The safety net keeps your script running even when it touches features we don't translate, so the failure mode for unmapped patterns is "warning + working baseline," not "crash."
  • The pre-flight check runs against your specific file before you convert and tells you exactly what features it touches — that's the rate that matters for you, not our aggregate.

We're working on customer-pattern telemetry to break the curator loop entirely — logging what real customers paste (with consent) so the priority queue is data-driven rather than team-guessed. Until that ships, treat the published rate as "rate on a careful-but-curated benchmark," not "rate on the wild."

The full coverage matrix lives in docs/vba-coverage.md in the repo. It catalogs 240+ VBA features across 13 categories with current status (covered / partial / safety-net / unmapped). We update this article when the matrix changes.

What this means for you

If your VBA is a typical finance or reporting macro — data prep, aggregation, formatting, report generation — you're in the roughly 85–90% that translates cleanly with no manual review. Paste it in and you'll get Python you can read, audit, and adapt.

If your VBA is heavy on UserForms, PivotTables, events, or COM automation, the converter won't save you the rewrite. But the parts of your code that are data-prep and business logic will still translate — and that's usually the part that takes the longest to rewrite by hand. The safety net means you'll get partial-but-running output instead of a NameError on line 17.

Every conversion includes a warning report and a pre-flight check. The pre-flight scan runs before you hit Convert and shows you exactly what features the converter will hit — supported, partial, stubbed, or unmapped — with counts. Set expectations before you commit.