AI as Your Legacy Code Archaeologist: Extracting Business Rules from VBA

When the Business Logic Lives in Scattered VBA Macros

The Hidden Treasure Problem

Consider a personnel scheduling application built in VB6, used by several hundred customers across Germany. At its core: 28,000 lines of VBA code implementing the logic for shift planning, time tracking, and — crucially — compliance with German Tarifverträge.

Tarifverträge are collective bargaining agreements that govern working conditions in German industries. They specify everything from maximum shift lengths to mandatory rest periods, from overtime calculations to holiday entitlements. But here’s the complexity: these rules differ by industry, by region, sometimes by individual company. A hospital in Bavaria operates under different rules than a manufacturing plant in North Rhine-Westphalia. A retail chain’s distribution center has different requirements than its store locations.

Over two decades, each customer installation evolved to implement their specific Tarifvertrag requirements. Night shift premiums calculated one way here, another way there. Break time rules that depend on shift length, but with different thresholds per customer. Overtime that accrues differently based on contract type, employment status, and which collective agreement applies.

The business rules aren’t in a requirements document. They’re scattered across thousands of lines of VBA, written by developers who understood both the code and the labor law — some have moved on, but many remain available as domain experts who can validate what the AI extracts.

When these systems need modernization, organizations face a fundamental problem: even when the original developers remain available, they can’t dedicate months to explaining every module. The documentation, if it ever existed, describes intent rather than implementation. Users know what the system does for them, but not how — and certainly not how all the pieces interact.

Why Traditional Approaches Fall Short

The Documentation Gap

Most VBA customizations were never formally documented. A developer wrote code to solve an immediate problem, it worked, and everyone moved on. Even when documentation exists, it typically describes what someone intended to build, not what was actually built — and certainly not the subtle modifications made over years of maintenance.

The Interview Limitation

Business users can describe what they do with the system. They know their workflows, their edge cases, their workarounds. But they describe behavior, not logic. When they say “the system calculates our commission,” they can’t tell you the seven nested conditions that determine which commission rate applies, or the special handling for discontinued products, or the quarterly adjustment that kicks in for high-volume accounts.

The Manual Code Review Problem

Skilled developers can read VBA code. But reading thousands of lines across dozens of customized installations, identifying which patterns represent business rules versus technical implementation, and synthesizing it all into coherent understanding — this takes months. Even then, the review is colored by what the reviewer expects to find. Unexpected patterns get overlooked.

Enter AI-Assisted Code Archaeology

When I started working with this client as a Developer Advocate, the challenge was clear: extract enough understanding of the existing business rules to rebuild the system as a modern Progressive Web App — deployable both on-premises for customers who require it and as a SaaS offering for those ready for the cloud.

Traditional analysis would take a year or more. The domain experts know the business, but they don’t read VBA. The people who wrote the code are still around, but they’ve taken on other responsibilities — customer support, implementation consulting, training — and can’t dedicate months to walking developers through every module. And with several hundred customer variants, manually reviewing each installation was impractical.

Large language models trained on code bring something new to this analysis. Unlike previous attempts to simplify software development, AI doesn’t promise to eliminate the need for expertise — it amplifies it. These models have seen millions of code patterns across languages. They can recognize what code is trying to accomplish even when the implementation is convoluted. They don’t get tired after reading the hundredth subroutine. And they can be directed to look for specific types of business logic.

What AI Does Well

Pattern Recognition at Scale: AI models can process vast amounts of code and identify recurring patterns. They can spot where similar business logic appears across different customer installations, even when implemented differently.

Translation to Natural Language: Given VBA code, an AI can generate plain-language descriptions of what the code does. Not perfect descriptions — the AI may misunderstand context — but starting points that humans can verify and refine.

Comparison and Contrast: When analyzing multiple installations, AI can highlight where they differ. “Installation A calculates tax this way, but Installation B has this additional condition.” These differences often represent critical business rules specific to each customer.

Question Answering: Rather than just summarizing code, AI can answer specific questions. “How does this system handle returns?” “What triggers the credit limit warning?” “Where does the discount calculation happen?”

What AI Cannot Do

Understand Business Context: AI doesn’t know why a rule exists, only what it does. A human must determine whether a peculiar calculation reflects genuine business requirements or a long-forgotten workaround.

Guarantee Completeness: AI may miss subtle interactions between code modules. It may overlook conditional logic that rarely executes. It provides assistance, not certainty.

Replace Domain Expertise: The AI can identify that a calculation exists and describe its mechanics. Only someone who understands the business can judge whether the calculation is correct, necessary, or obsolete.

A Practical Approach

The approach we’ve developed treats AI as a bridge between code and domain expertise. The AI reads the VBA. The domain experts validate the business rules. The output isn’t just understanding — it’s executable specifications that will drive the new implementation.

The Account Challenge

A large portion of the VBA code deals with accounts — the building blocks for calculating employee payments. There are hundreds of different account types: base salary accounts, overtime accounts, night shift premium accounts, holiday accounts, sick leave accounts, each with their own calculation rules that vary by Tarifvertrag and customer configuration.

Understanding how each account type works — when it accrues, how it calculates, what triggers adjustments — is essential for the new system to produce correct paychecks.

Phase 1: AI-Driven Extraction to GitHub Issues

We direct the AI to analyze specific functional areas of the VBA code. “Examine this module and identify all account types. For each account, describe when it accrues, how amounts are calculated, and what conditions affect the calculation.”

The AI doesn’t just produce a report — it creates a GitHub Issue for each discovered business rule or account type. The issue contains the AI’s analysis: what the code appears to do, the conditions that apply, any variations it noticed, and questions where the logic was unclear.

Phase 2: Human Review and Scheduling

The subject matter experts who review each issue are the same people who wrote the original VBA code. They’ve since moved into customer-facing roles — implementation, support, training — but they remember the logic. They don’t need to re-read their own VBA; they read the AI’s business-language description and judge whether it matches what they built years ago.

Sometimes the AI got it exactly right. Sometimes it missed a nuance that the original author catches immediately. Sometimes the AI surfaced behavior that even the author had forgotten about — edge case handling they added for a specific customer a decade ago.

Validated issues get scheduled for implementation. Unclear issues get flagged for deeper investigation, often involving a quick conversation to refresh memories about that particular customer’s requirements.

Phase 3: AI-Assisted Implementation

Implementation follows a similar pattern. Given a validated issue describing a business rule, we prompt the AI to write the C# code and associated tests for the new PWA.

The AI produces both the implementation and the test cases. The tests encode the business rules in executable form: “Given an employee with contract type X working shift pattern Y, when calculating the night premium account, then the amount should be Z.”

Phase 4: Targeted Human Validation

The SMEs review the generated tests — but not line by line. They know which accounts are straightforward and which hide complexity. They know which Tarifvertrag rules have subtle edge cases and which are simple calculations.

They focus their attention on the critical and difficult parts. When they see a test for overnight shift premiums, they check whether it handles the midnight boundary correctly. When they see holiday accrual tests, they verify the logic for partial-year employees. Their expertise guides where to look, not whether to look at everything.

This targeted review is far more efficient than exhaustive verification. The SMEs apply judgment about where errors are likely to hide, rather than treating all code as equally risky.

The Augmentation Mindset

The domain experts at this company wrote the original VBA code. They know German labor law intimately, and they know the code because they built it. But their time is scarce — they’re busy with customer implementations, support calls, and training. Having them re-read thousands of lines of their own code to explain it to new developers isn’t a good use of their expertise.

The AI creates a translation layer. It reads the code and produces business-language descriptions. The original authors validate those descriptions — quickly, because they recognize correct explanations of their own work. Together, they surface understanding in hours that would otherwise take weeks of interrupted conversations.

This collaborative model defines the Developer Advocate approach: hands-on technical work that amplifies existing expertise rather than replacing it. The AI is a tool. The domain experts provide judgment. The Developer Advocate orchestrates the process and ensures the extracted knowledge becomes executable specifications — not just documentation that will drift out of sync.

This matters especially for

legacy modernization projects where understanding the existing system is

craft work — investigation, hypothesis, verification. AI accelerates the investigation and verification phases while keeping humans firmly in control of the judgment calls.

Practical Considerations

Model Selection

Different AI models have different strengths. For code analysis, models with strong coding capabilities and large context windows work best. Claude Sonnet 4.5 and similar models can handle substantial code files and maintain coherence across long analysis sessions.

Context Management

VBA modules can be large. Providing enough context for the AI to understand what it’s looking at — without overwhelming it with irrelevant code — requires judgment. Sometimes you need to feed related modules together; sometimes you need to focus on specific functions.

Prompt Engineering

How you ask matters enormously. “What does this code do?” produces generic answers. “This VBA code appears to calculate pricing discounts. Identify all the conditions that affect which discount rate is applied, and explain each condition in business terms” produces useful analysis.

Version Control

When you’re analyzing code from multiple customer installations, keeping track of which code came from where is essential. The AI doesn’t care, but the humans interpreting its output certainly do.

Beyond Extraction: Building for the Future

Understanding legacy business rules is only the first step. The larger goal is typically to rebuild this logic in a modern system — documented, tested, maintainable.

The patterns you extract from legacy VBA can become the basis for:

• Executable specifications that document exactly what the system should do
• Test suites that verify the new implementation matches the old behavior
• Domain models that make business rules explicit rather than buried in code

AI assistance doesn’t stop at extraction. The same models can help translate identified business rules into modern code, generate test cases, and document the intended behavior. The entire arc from understanding to rebuilding becomes faster.

The Real Value

The personnel scheduling system represents decades of accumulated knowledge about German labor law — not abstract legal theory, but practical implementation of how hundreds of organizations actually apply these rules. That knowledge has enormous value, but it was locked in VBA code that only a handful of people could read.

Now that knowledge is becoming explicit. The tests we’re creating don’t just validate the new PWA implementation — they document exactly how each Tarifvertrag rule works in practice. When a customer asks “how do you handle rest periods for part-time employees in retail?”, the answer exists in readable, executable form.

This changes the modernization economics fundamentally. A year of manual analysis compresses into weeks. The domain experts — who would have spent that year answering questions from developers trying to understand the code — instead validate extracted rules at the business level. Their expertise is leveraged, not consumed.

For organizations facing legacy systems with extensive customization, this represents a new capability. Work that previously required months of expensive specialist time now happens in weeks. Analysis that was too expensive to justify becomes affordable. Business knowledge that was locked in decades-old code becomes accessible to people who can actually use it.

The code isn’t going anywhere. But now we have better tools to understand what it knows.