Home / Works / VBA Automation Library & Add-ins

Overview

Packaged as add-ins, it replaced manual database builds, table refreshes and summary pivots, and runs automated data-quality checks on dates, payment terms and owner values that output an exception report for correction before data reaches reports.

How it works

Macros and add-ins: builds with a reference entering mid-way, checks, and stand-alone tools.

InputStepDecision / optionData storeOutput

Details

• Built a VBA automation library, packaged as add-ins, that replaced manual database builds, table refreshes and summary pivots, and runs automated data-quality checks on dates, payment terms and owner values that output an exception report for correction before data reaches reports.
• Library (personal macro workbook, about 30 modules): per-province database builds with summary pivots and option sheets; loading every table as a Power Query connection and building table-to-sheet relationships; generating per-period and append queries in M; standardising table headers and column order across sheets; date cleanup that rewrites text dates in remarks to ISO (YYYY-MM-DD); a payment-terms check column; inventory macros that list sheets, tables and pivot sources; calendar builds by month.
• Stand-alone add-ins (.xlam):
  • QGIS join cleaner: prepares a QGIS join export by formatting negotiation date columns as YYYY-MM-DD text and replacing commas with semicolons.
  • Invisible-character cleaners (two add-ins): clear cells that hold only invisible characters (control characters, non-breaking and zero-width spaces) so they read as truly empty.
  • Visit counter: worksheet functions that extract visit dates from free-text remarks in several date formats and return the visit count or a spilled list, optionally from a start date.
• Built with: Excel VBA, Excel add-ins (.xlam), Power Query (M)

About the code

Built for my department. The code stays with the company, so this page describes the tool only.