Service · VBA
VBA · Excel and Access
Office automation as one discipline, not two separate tools. Excel VBA for click trails, reports, Outlook integration. Access VBA for forms, reports, grown databases up to 5 million rows. Refactoring to Option Explicit, typed ranges, custom error classes. Backend migration to SQL Server when Access hits limits. Code that does not crumble at the next Office update.
The problem
What I find most often: a business-critical workflow sitting in one Excel file. SAP export lands in a sheet, one person clicks through twelve pivot tables, a PDF goes to leadership. Works as long as that person is there, no column moves, and no Microsoft patch touches the object model.
Typical: 800 lines of VBA without Option Explicit, globals jumping across modules, On-Error-Resume-Next as error handling, old FileSystemObject calls without late-binding protection. Often the file itself is the problem too: mixed types in the same column, merged cells in data zones, formulas with hard-coded references that break on every insert. Same picture on the Access side: forms hanging 8 seconds, reports taking 45, no transactions, no lock handling.
Excel and Access are not the problem. Excel and Access without structure are the problem. A 2-4-day refactor saves months of symptom fighting.
The solution
I clean up existing VBA into cleanly cut modules: data access, business logic, UI/reporting separated. Workbooks split into input, processing, and output zones with ListObjects instead of range addresses. Error handling with defined custom errors, logging into a dedicated worksheet or log file, no more silent Exit Subs.
For Access: 32→64-bit migration with PtrSafe sweep, DAO/ADO updates, ODBC driver audit, optional backend migration from .accdb to SQL Server or PostgreSQL via linked tables. Forms stay in Access because the UI layer is what users know. Performance tuning on indexes and queries: 45-second reports often down to 2-4 seconds.
Deliverables: refactored XLSM/XLSB, exported .bas/.cls modules for version control (I push them to your Git or Azure DevOps if you want), a README with setup, assumptions, known limits, and step-by-step maintenance notes. A second developer onboards in under a week.
Typical use cases
- Monthly management reporting: SAP/Salesforce exports in, join with master data, KPIs computed, PDF generated, Outlook sent
- Refactor of existing VBA (500-3000 lines) to Option Explicit, typed variables, custom error classes, module separation
- Bulk imports of CSV/XLSX with schema validation, plausibility checks, and detailed error log instead of crashing on the first invalid cell
- PDF export pipelines with reproducible layout, page-break logic, dynamic per-recipient attachments, filing on a fixed naming schema
- Access app stabilization: 32→64-bit migration, ODBC driver refactor, performance tuning on indexes and queries
- Access backend migration to SQL Server with linked tables: forms unchanged, real multi-user locking, scheduled backups, optional Power BI on top
Concrete benefits
- Maintainability: a second developer takes the file over in 3-5 days without verbal handover
- Robustness: custom error classes with logging instead of On-Error-Resume-Next, Office updates do not silently break things
- Versionability: VBA modules live as .bas/.cls in Git, diffs are readable, code review possible
- Performance: 10000-row operations of 90 seconds down to 3-4 seconds (Range-Variant instead of cell-by-cell, ScreenUpdating-Off, Calculation-Manual)
- Access reports often 5-20x faster after index tuning and subreport refactor
- Audit fitness: every data manipulation leaves a log trail, fit for auditor or compliance review
How we work together
Discovery (90 min, remote)
You show me the file and describe the process. I ask about source systems, frequency, users, data-protection constraints. You get a written read with risks and a recommendation: refactor, rebuild, or migrate the load layer to Power Query.
Audit (1-2 days, optional)
For larger estates: full review of the VBA codebase and data architecture. Output is a 4-6 page report with findings (missing Option Explicit, unprotected writes, critical performance hotspots), prioritized backlog, realistic effort.
Refactor / rebuild in 1-2-week increments
Iterative work with short reviews. Each increment is runnable, tested against anonymized real data, documented. You can exit at any point without a half-finished file.
Handover with docs, code walkthrough, optional retainer
Final file, README, exported modules for your Git, 30-60 min live walkthrough with recording. Optional retainer for maintenance, extensions, Office-update reviews.
Frequently asked questions
Is VBA outdated? Should we move to Python or Power Automate?
Depends on the use case. When the output stays inside Excel and the process never leaves the Office world, VBA in 2026 is still the soberest answer: runs offline, no extra license, available on every Office client. Once multiple source systems or non-Office outputs join, I will suggest Power Query, Python, or n8n myself. Honest advice costs less than the wrong tool.
Should we abandon Access?
Not always. When the app is running, 5-15 users use it, the data sits under 5-10 million rows, and nobody needs mobile access, Access is often the cheapest answer. Move away when multi-user contention is felt, mobile access is needed, or the business logic outgrows forms and reports.
Can we maintain the solution ourselves?
Yes, if the structure is right. That is exactly what typed modules, Option Explicit, exported .bas files, README, and code walkthrough are for. An employee with basic VBA knowledge takes over in a week. For deeper extensions a retainer can be agreed.
What about Office updates or a Microsoft 365 migration?
With Option Explicit, late binding for external libraries, and avoiding deprecated APIs, most macros are update-resilient. The most common breaks come from ActiveX controls and ListObjects from Excel 2003. I check the codebase for these risks before migrations.
How long does a typical project take, and what does it cost?
Refactor of a mid-size file (1500-2500 lines of VBA): 8-14 working days. New build of a reporting pipeline incl. Power Query layer: 10-20 working days. At €140-180/h that is €9000-25000 net. After the discovery workshop you get a real range, larger work can move to a fixed price.
Related services
Audit your VBA estate?
Send me the file or an anonymized version. Within two working days you get an honest read: refactor, rebuild, or is Power Query the better answer.