Improve and Upgrade Complex VBA-driven Excel Tool

I have a complex Excel tool written entirely in VBA. It currently handles
BOQ management.
– Maintains a “Bill of Quantities” sheet with item code, description, unit, contract qty and unit price.
– Automatically calculates line totals (qty × price).
Interactive search & navigation
– Real-time filtering form to find any item by code or description.
– Jumps the BOQ to the selected row and opens its dedicated summary sheet.
– Each summary sheet has a “Return to BOQ” link.
Sub-chapter summary report
– Aggregates all items by sub-chapter, prompts for missing descriptions, then writes a formatted summary with grand totals.
Per-item concentration sheets
– Copies a template for each BOQ item, populates it with the item’s code, description, qty and price.
– Adds navigation links back to the main BOQ.
External calculation-file integration
– Recursively scans a user-selected folder for Excel files.
– Identifies sheets by a header (“Item No.” in row 5), reads estimated and submitted values, and writes them into the item’s concentration sheet.
BOQ totals refresh
– Pulls each concentration sheet’s “totals row” back into the BOQ’s summary columns.
PDF export & merge
– Lets the user hide chosen columns, then exports all (or only non-empty) concentration sheets to individual PDFs.
– Automatically merges them into one PDF via PDFtk.
Some Key enhancements to implement
Dynamic row growth in concentration sheets
If a sheet’s template runs out of pre-allocated rows (12–31), insert new rows automatically so imported data never overflows or is lost.
Preserve manual entries
Allow users to add notes or manually adjust values (e.g. approved PNIMI, manager-approved amounts, custom comments) anywhere in a concentration sheet.
On each import/run, only overwrite data regions sourced from calculation files; leave user-entered cells intact.
Flexible template zones
Define clear “automation ranges” versus “manual ranges” on each sheet. The code writes only inside automation zones, so all manual footers/comments above or below remain untouched.
Centralized configuration
Move all sheet names, column letters, folder paths and other constants into an external JSON/XML config file, so future tweaks don’t require code changes.
Robust error handling & logging
Implement a try/catch-style framework with detailed log files to trace every import step, sheet creation and PDF export.
Modular architecture
Refactor into discrete components (Search, Summary, Sheet Creation, File Import, PDF Export) following SOLID principles, to ease future extensions.
Improved UI/UX
Replace raw VBA forms with a polished Ribbon interface and concise dialogs, complete with input validation and progress feedback.
Unit testing & documentation
Add XML comments on all public methods and build unit tests for core data-processing functions, ensuring reliability as the codebase grows.