EUC Modernisation Lab

This repository demonstrates a practical approach to modernising small Excel and Access-style end-user computing applications with normal software engineering controls and LLM-assisted SDLC workflows.

The first sample is a fictional departmental spend review workbook. It represents the kind of internal EUC asset that often grows around a stable but important process: monthly finance exports, cost-centre checks, approval thresholds, exception handling, and management reporting.

The aim is not to present VBA as a greenfield platform. The aim is to show how an opaque Office file can be decomposed into source-controlled artefacts, documented, tested, risk-reviewed, and prepared for stabilisation or migration.

Portfolio Narrative

This repository shows that legacy Office assets can be handled with normal delivery controls before anyone starts a rewrite. The samples demonstrate how to recover source from Excel and Access, define the behavioural surface with characterisation tests, inventory the moving parts, use LLM prompts for review and documentation, and keep migration options open through COM and .NET boundaries.

This is deliberately a small synthetic estate rather than a polished production application. The value is in the modernisation workflow: make the hidden system visible, test what it does today, and then choose whether to stabilise, wrap, or replace it.

Portfolio Preview

Start Here

Evidence	Where to look
Case-study narrative	CASE_STUDY.md
Excel workbook sample	samples/acme-spend-tracker/README.md
Access database sample	samples/acme-access-case-register/README.md
Excel characterisation tests	samples/acme-spend-tracker/tests/test_spend_rules.py
Access characterisation tests	samples/acme-access-case-register/tests/test_case_rules.py
Source-level object inventories	Excel inventory and Access inventory
VBA static analysis	Excel findings and Access findings
Business-rule catalogue	Excel rules and Access rules
Migration risk review	Excel review and Access review
.NET migration boundary	samples/acme-spend-tracker/docs/03-dotnet-migration-spike.md
Practical limitations	LIMITATIONS.md

Samples

samples/acme-spend-tracker contains the Excel sample:

• workbook source data and metadata
• generated Excel workbook output
• VBA modules intended for manual import or COM-based hydration
• characterisation tests for approval and exception rules
• SDLC notes and LLM prompts for reverse engineering and review

samples/acme-access-case-register contains the Access sample:

• database schema and seed data
• generated Access .accdb output
• Access modules imported with LoadFromText
• SaveAsText verification of source-controlled modules
• characterisation tests for case health and escalation rules

The workbook scenario is deliberately modest:

1. Finance exports monthly spend transactions into Excel.
2. A departmental user reviews claims against cost-centre and category rules.
3. VBA flags approval routes, exceptions, and missing evidence.
4. Summary reports are produced for managers and finance teams.
5. The modernisation workflow extracts the rules, tests them, and documents the migration options.

Build

Generate the workbook artefact:

python samples\acme-spend-tracker\tools\generate_workbook.py

Run the characterisation checks:

python samples\acme-spend-tracker\tests\test_spend_rules.py
python samples\acme-access-case-register\tests\test_case_rules.py

The generated .xlsx workbook is macro-ready in structure, but the VBA source is kept separately under src/vba. That keeps the Git story simple and provides a safe manual import path for Office 2016.

If Excel is installed and Trust Center permits programmatic VBA project access, samples\acme-spend-tracker\tools\hydrate_xlsm.ps1 can save the generated workbook as .xlsm and import the VBA modules automatically.

Excel COM Smoke Tests

To test local Office automation separately from the sample workbook:

powershell -ExecutionPolicy Bypass -File .\tools\test_excel_com.ps1

For a minimal VBScript path:

cscript .\tools\test_excel_com.vbs

Both scripts create an .xlsx, create an .xlsm, and attempt to inject a tiny VBA module so you can distinguish basic Excel COM access from Trust Center VBA project access.

If VBProject is null, inspect the active Office version and the Trust Center registry setting:

.\tools\diagnose_excel_vbide.ps1

To set AccessVBOM = 1 for existing per-user Excel security keys:

.\tools\diagnose_excel_vbide.ps1 -EnableAccessVBOM

Close all Excel instances before rerunning the smoke test.

Documentation

Document	Purpose
GETTING_STARTED.md	Setup, prerequisites, and verification steps
ARCHITECTURE.md	Source tree convention, manifest schema, and how to apply this to your own workbook
CASE_STUDY.md	Before/after modernisation narrative for reviewers
LIMITATIONS.md	Practical constraints around Office COM, bitness, templates, and hosted CI
TROUBLESHOOTING.md	Common problems: COM errors, Trust Center, Python issues
BACKLOG.md	Prioritised modernisation backlog

One-Command Verification

For a quick confidence check after returning to the project:

.\tools\verify_all.ps1

To include Excel COM, VBA import, and .xlsm verification:

.\tools\verify_all.ps1 -IncludeExcelCom

To include Access COM, .accdb generation, and module export verification:

.\tools\verify_all.ps1 -IncludeAccessCom

Generate source-level object inventories:

python .\tools\new_object_inventory.py .\samples\acme-spend-tracker excel
python .\tools\new_object_inventory.py .\samples\acme-access-case-register access

Generate business-rule catalogues and migration-risk reviews:

python .\tools\generate_business_rule_catalogue.py .\samples\acme-spend-tracker excel
python .\tools\generate_business_rule_catalogue.py .\samples\acme-access-case-register access
python .\tools\generate_migration_risk_review.py .\samples\acme-spend-tracker excel
python .\tools\generate_migration_risk_review.py .\samples\acme-access-case-register access

For Access DAO/CurrentDb automation diagnostics:

.\tools\test_access_currentdb_context.ps1
.\tools\test_access_dao_dbengine.ps1

Migration Spike

The Excel sample includes a small .NET Framework COM Interop spike under:

samples\acme-spend-tracker\src\dotnet\Acme.SpendRules

It is not part of the generated workbook yet. It shows the migration direction: keep Excel as the UI, thin the VBA layer, and move business rules into a tested .NET library callable through COM.