VBA Shared Library (LIB)

Developer Reference Manual  |  Version 1.0  |  March 2026

1. What Is the Shared Library?

The Shared Library is a collection of nine .bas module files that contain battle-tested VBA utility functions extracted from real production macros in this repository. Every function has been used in at least one working project.

Because VBA cannot share compiled code between workbooks at runtime (there is no DLL or add-in mechanism in this setup), the library is a copy-paste reference: you import the modules you need into your new project, and they become a permanent part of that workbook. Changes you make in one workbook do not automatically propagate to others — that is intentional.

What is NOT in this library

• AppLock — the Application.ScreenUpdating / EnableEvents / DisplayAlerts / Calculation pattern always lives in the project's own Orchestrator module. See CLAUDE.md §9.
• frmProgress UserForm — LIB6 is a wrapper skeleton; the actual form must be created or imported separately for each project.
• Business logic — any code that is specific to a single domain (BPA file generation, wire payment review, etc.) stays in that project's modules.

2. How to Import Modules Into a New Project

1. Open the target workbook in Excel.
2. Press Alt + F11 to open the VBA Editor (VBE).
3. In the menu bar: File → Import File…
4. Navigate to SharedLibrary\ and select the .bas file(s) you need. Import one file per operation.
5. The module appears in the Project Explorer under Modules. Its Attribute VB_Name will be the name shown (e.g. LIB1_StringUtils).
6. Check the Dependencies column in the table below — if your module calls another LIB module, import that one too.

3. Renaming the LIB Prefix

The LIB prefix is registered to the Shared Library folder itself. When you import a module into a real project (e.g. BPA), you have two options:

1. Keep the LIB prefix — simplest option, works fine when only one project lives in the workbook. The module is called LIB1_StringUtils, etc.
2. Rename to your project's prefix — do this when multiple projects share one workbook or when you want every module to use a consistent prefix. After importing, go to the module's Properties window (F4 in VBE), change (Name) to e.g. BPA1_StringUtils, and update the Attribute VB_Name line at the top of the file. Also update every internal call from LIB1. to BPA1. if VBA ever needs to qualify the module name (it usually does not, because all public functions are visible project-wide without a module qualifier).

4. Dependency Map

5. LIB1 — String Utilities

Safe string conversion, Unicode-aware text cleaning, accent normalisation, path/filename helpers, and splitting. No dependencies. Used by almost every other module in the library.

Function reference

NzStr(v) → String

Returns "" for Null, Error values, or anything that can't be converted with CStr(). Use everywhere you read from a cell — it prevents the "Type mismatch" and "Invalid use of Null" runtime errors that occur when a cell contains an Excel error formula result (#N/A, #VALUE!, etc.) or a database Null.

Dim cellVal As String
cellVal = NzStr(ws.Cells(i, colAmount).Value)   ' safe even for #N/A cells

GF_StrictClean(s) → String

Full Unicode-aware sanitiser. Removes all ASCII control characters (via Excel's CLEAN()), normalises every Unicode space variant (NBSP, thin space, em space, etc.) to a regular space, strips zero-width characters and BOM, then collapses runs of multiple spaces and trims. Use this before any lookup, comparison, or code substitution.

Dim vendor As String
vendor = GF_StrictClean(CStr(ws.Cells(i, colVendor).Value))
If vendor = "" Then GoTo NextRow

GF_RemoveWeirdSpaces(s) → String

Lighter version of the above. Does NOT remove control characters (so bullet characters and other printable non-ASCII characters survive). Use for email body text or any field where you want to preserve special characters but still fix invisible-space paste artifacts.

CleanText(txt) → String

Calls GF_StrictClean then removes any remaining Chr(160) (NBSP). Drop-in replacement for the old manual CleanText helper used across earlier projects. Identical to GF_StrictClean for practical purposes.

StripNonAlphanumeric(s) → String

Keeps only A–Z, a–z, 0–9, and space. Use when a config flag like Strip_Special_Chars = YES is set, or when normalising a code field before exact comparison.

Dim code As String
code = StripNonAlphanumeric("ACC-0012 / AU")   ' -> "ACC0012  AU" then collapsed -> "ACC0012 AU"

NormalizeForFuzzy(s) → String

Prepares a person or company name for fuzzy comparison. Lower-cases, strips honourifics (Mr., Mrs., Dr., Prof., etc.), replaces all punctuation with spaces, collapses spaces. Use on both sides before calling Similarity() or ScorePair().

Dim a As String, b As String
a = NormalizeForFuzzy("Spafford, Ms. Torie")   ' -> "spafford torie"
b = NormalizeForFuzzy("TORIE SPAFFORD")          ' -> "torie spafford"

NormalizeText(txt) → String

Lower-cases and maps accented / diacritic characters to their ASCII equivalents. Covers standard Latin accents (à→a, é→e, ü→u, ñ→n, etc.) and Polish diacritics (ą→a, ć→c, ę→e, ł→l, etc.). Use for fuzzy name matching across inputs from different locales and source systems.

Dim n As String
n = NormalizeText("Müller")    ' -> "muller"
n = NormalizeText("Łukasz")    ' -> "lukasz"

NormalizeHeaderText(s) → String

For column-agnostic header matching. Trims, collapses spaces, normalises all Unicode dash variants (en-dash, em-dash, hyphen, etc.) to a plain hyphen, lower-cases. Use together with GetColByHeader() in LIB2 to survive Excel files where headers were pasted from Word and contain typographic dashes.

' "E–mail" (en-dash)  and  "E-mail" (hyphen)  both become "e-mail"
Dim h As String
h = NormalizeHeaderText(ws.Cells(1, c).Value)

ConcatNote(note, piece) → String

Appends piece to note with "; " as separator. Returns piece if note is currently empty (so you never get a leading "; "). Use for building audit notes and log action strings.

Dim note As String
note = ConcatNote(note, "Price normalised")
note = ConcatNote(note, "Lead time clamped to upper bound")
' -> "Price normalised; Lead time clamped to upper bound"

StripOuterQuotes(s) → String

Removes one layer of matching double-quotes from both ends of a string. Use when reading CSV-style values that were pasted with surrounding quotes.

Dim v As String
v = StripOuterQuotes("""hello""")   ' -> "hello"
v = StripOuterQuotes("hello")       ' -> "hello" (no change, no outer quotes)

SplitCommaSafe(s) → String()

Splits by comma and trims each resulting token. Safer than a raw Split(s, ",") call because it handles leading/trailing spaces around commas.

Dim parts() As String
parts = SplitCommaSafe("  AU_611 , US_151 , GB_201 ")
' parts(0) = "AU_611", parts(1) = "US_151", parts(2) = "GB_201"

PathJoin(a, b) → String

Joins two path segments with Application.PathSeparator. Handles the case where a already ends with a separator, so you never get double backslashes.

Dim fullPath As String
fullPath = PathJoin(ws.Range("B2").Value, ws.Range("B3").Value)
' -> "C:\Output\Files\report.xlsx"  even if B2 ended with "\"

SanitizeSegment(s) → String

Makes a string safe for use as a Windows filename token. Collapses spaces to underscores, removes all illegal Windows filename characters (\ / : * ? " < > |), strips leading/trailing underscores.

Dim token As String
token = SanitizeSegment("Hello World!")     ' -> "Hello_World"
token = SanitizeSegment("Q1 / AU Review")  ' -> "Q1_AU_Review"
Dim fname As String
fname = FormatDateForFilename(Date) & "_" & token & ".xlsx"
' -> "26_MAR_2026_Q1_AU_Review.xlsx"

6. LIB2 — Sheet Utilities

Worksheet safety wrappers (no more Runtime Error 9), header-column finding at any row, auto-detection of the header row in messy files, column management, folder creation, formula-to-value conversion, and Dictionary helpers. Depends on LIB1.

Sheet safety

GetOrCreateSheet(wb, sheetName) → Worksheet

Returns the named sheet, creating it at the end of the workbook if it does not yet exist. Never raises Runtime Error 9. Use this for output sheets that must exist before you write to them.

Dim wsLog As Worksheet
Set wsLog = GetOrCreateSheet(ThisWorkbook, "Log")
wsLog.Cells(1, 1).Value = "Ready"

GetSheetIfExists(wb, sheetName) → Worksheet (or Nothing)

Returns the named sheet, or Nothing if it does not exist. Never crashes. Use this instead of Worksheets("name") whenever the sheet's existence is not guaranteed. Always test If ws Is Nothing Then after calling it.

Dim wsOptional As Worksheet
Set wsOptional = GetSheetIfExists(ThisWorkbook, "Config")
If wsOptional Is Nothing Then
    ShowError "Config sheet missing"
    GoTo CleanUp
End If

SheetExists(wb, sheetName) → Boolean

Returns True if the named sheet exists in the workbook.

If Not SheetExists(ThisWorkbook, "Archive") Then
    MsgBox "Run SetupWorkbook first to create the Archive sheet."
    Exit Sub
End If

ClearSheetAndTables(ws)

Deletes all ListObjects (Excel Tables) on the sheet first, then clears all cells. Use at the start of a run to reset an output sheet without leaving orphaned table objects that would cause errors when you re-write headers.

ClearSheetAndTables wsTodo
wsTodo.Cells(1, 1).Value = "Line Number"   ' safe to write headers now

Header finding

FindHeaderCol(ws, headerText) → Long

Case-insensitive match in row 1 using Application.Match. Returns the 1-based column index, or 0 if not found. Fastest option when the header is always in row 1.

Dim colVendor As Long
colVendor = FindHeaderCol(ws, "Vendor Name")
If colVendor = 0 Then
    MsgBox "Column 'Vendor Name' not found"
    Exit Sub
End If

FindHeaderInRow(ws, headerText, headerRow) → Long

Case-insensitive scan in a specific row number. More reliable than Application.Match when the header contains extra spaces. Use when you already know which row the header is in but want to handle leading/trailing spaces.

Dim colEmail As Long
colEmail = FindHeaderInRow(ws, "Email", 3)   ' header is in row 3

FindHeaderColMultiRow(ws, headerText [, maxRow]) → Long

Scans rows 1 through maxRow (default 3) for an exact case-insensitive match. Use when source files are inconsistent — sometimes the header is in row 1, sometimes row 2 (because of a title row above it).

Dim colSupplier As Long
colSupplier = FindHeaderColMultiRow(ws, "Supplier Number", 5)

Auto-detecting the header row (variable-format files)

Use these three functions together when you receive files where the header row position can vary and you cannot hardcode it:

DetectHeaderRow(ws, requiredNames [, startRow, endRow]) → Long

Scans rows startRow through endRow and returns the first row where at least 3 of the supplied column names are found. Returns 0 if no qualifying row is found.

MapHeaderColumns(ws, headerRow) → Object (Dictionary)

Returns a Scripting.Dictionary mapping normalised header text to its 1-based column number. All keys are processed through NormalizeHeaderText() so Unicode dashes, extra spaces, and case differences do not matter.

GetColByHeader(hdrMap, names) → Long

Tries each name in the names array against the map dictionary. Returns the column of the first match, or 0. Use synonym arrays to handle alternative spellings in one call.

' Full three-step pattern for variable-format files:
Dim hdrRow As Long
hdrRow = DetectHeaderRow(ws, Array("OU", "Supplier Num", "Email"), 1, 5)
If hdrRow = 0 Then MsgBox "Cannot find header row": Exit Sub

Dim hdrMap As Object
Set hdrMap = MapHeaderColumns(ws, hdrRow)

Dim colOU     As Long : colOU     = GetColByHeader(hdrMap, Array("OU", "Org"))
Dim colEmail  As Long : colEmail  = GetColByHeader(hdrMap, Array("E-mail", "Email", "Email Address"))
Dim colSupNum As Long : colSupNum = GetColByHeader(hdrMap, Array("Supplier Num", "Supplier Number", "Vendor ID"))

Column management

GetOrAddOutputCol(ws, headerText) → Long

Looks for an existing column with that header in row 1 and returns it. If not found, appends the header at the first empty column and returns that new column number. Use to add output columns (BUYER_ID, SOURCING_RULE, STATUS) to a copy of the source data.

Dim colStatus As Long
colStatus = GetOrAddOutputCol(wsTodo, "Processing Status")
wsTodo.Cells(todoRow, colStatus).Value = "DONE"

Header styling

StyleHeader(rng [, bgColor])

Applies bold + background colour + white font to a range. Default colour is Excel blue (RGB(68, 114, 196)). Pass any RGB() value as the second argument to use a different colour.

StyleHeader ws.Rows(1)                           ' default blue
StyleHeader ws.Range("A1:K1"), RGB(0, 176, 240)  ' custom cyan

Folder creation

EnsureFolderExists(folderPath) → Boolean

Creates a folder path including all intermediate parent folders using FileSystemObject. Works with UNC paths and deep nesting. Returns True on success, False if an error occurs (e.g. no write permission).

Dim outFolder As String
outFolder = PathJoin(ws.Range("B5").Value, "Output\2026-03")
If Not EnsureFolderExists(outFolder) Then
    MsgBox "Cannot create output folder: " & outFolder
    GoTo CleanUp
End If

Formula-to-values conversion

ConvertFormulasToValues(ws)

Converts every formula cell in ws.UsedRange to its displayed string value. Uses cell.Text (not cell.Value) so that leading zeros are preserved — for example a cell formatted as "00000" containing =A1 will become the string "00048" rather than the number 48. Call this before SaveAs or any column scan on files that may contain formula-driven account numbers.

ConvertFormulasToValues dataWS   ' call once on the source sheet after opening

Dictionary helpers

NewDict([compareModeText]) → Object

Creates a new Scripting.Dictionary. Default is case-insensitive (vbTextCompare = 1). Pass False to get a case-sensitive dictionary.

Dim cache As Object
Set cache = NewDict()           ' case-insensitive (default)
Set cache = NewDict(False)      ' case-sensitive
cache("VENDOR A") = 42
cache("vendor a") = 42          ' same key in case-insensitive mode

DictHasKey(d, k) → Boolean

Safe key-existence check that handles Nothing without crashing. Equivalent to d.Exists(k) but safe to call even if the dictionary was never initialised.

InCollection(c, s) → Boolean

Linear search for a string value in a Collection.

JoinCollection(c, delim) → String

Joins all items in a Collection with a delimiter string. Equivalent to Join(arr, delim) but works on Collection objects.

7. LIB3 — Date Utilities

Locale-insensitive date formatting for filenames, cells, email subjects, and report titles. Safe date parsing that never crashes on empty or badly-formatted inputs. No dependencies.

Formatting functions

' Building a dated output filename:
Dim fname As String
fname = FormatDateForFilename(Date) & "_" & SanitizeSegment(projectName) & ".xlsx"
' -> "26_MAR_2026_Q1_AU_BPA.xlsx"

Validation

ValidateRunDate(dateStr, outDate) → Boolean

Parses dateStr and checks that the result is today or in the future. Writes the parsed date back through the outDate ByRef parameter on success. Use to validate a run-date entered by the user in a cell or TextBox.

Dim runDate As Date
If Not ValidateRunDate(ws.Range("B1").Value, runDate) Then
    MsgBox "Run date must be today or a future date."
    Exit Sub
End If

Safe parsing

TryParseDate(s, d) → Boolean

Returns True and sets d on success; returns False on any failure. Never raises an error. Use when you want explicit success/fail branching.

Dim d As Date
If TryParseDate(CStr(ws.Cells(i, colDate).Value), d) Then
    ws.Cells(i, colOutput).Value = FormatDateLong(d)
Else
    ws.Cells(i, colOutput).Value = "INVALID DATE"
End If

TryCDate(s) → Date  /  SafeDate(s) → Date

Returns the parsed date, or 0 (= #30 Dec 1899#) on any failure. Use when you want a simple return value rather than a ByRef out-parameter. SafeDate is an alias for TryCDate.

Dim d As Date
d = TryCDate(CStr(ws.Cells(i, colDate).Value))
If d = 0 Then
    SafeLog ThisWorkbook, "DateParser", "Row " & i & " — unparseable date"
End If

8. LIB4 — Fuzzy Match

Levenshtein edit distance, similarity scoring, token-sort comparison, and company-name preprocessing. Used for matching vendor names from different source systems where spelling, punctuation, and word order differ. No dependencies.

Core functions

Levenshtein(s, t) → Long

Returns the minimum number of single-character edits (insert, delete, substitute) needed to transform s into t. Dynamic-programming O(n×m) implementation. Rarely called directly — use Similarity() or ScorePair() instead.

Similarity(a, b) → Double (0.0–1.0)

Converts Levenshtein distance to a 0.0–1.0 similarity score. 1.0 = identical, 0.0 = no characters in common. Use this for simple two-string comparisons; use ScorePair() for the full vendor-name matching pipeline.

If Similarity("ACME CORP", "ACME CORPORATION") > 0.8 Then
    ' close enough
End If

CleanCompanyName(s) → String

Prepares a vendor or company name for comparison: upper-cases, strips punctuation (periods, commas, dashes, ampersands, brackets, slashes), removes noise words as whole words (LLC, INC, LTD, THE, CORP, CO). Always apply to both sides before scoring.

CleanCompanyName("Smith, LLC.")       ' -> "SMITH"
CleanCompanyName("The Widget Co.")    ' -> "WIDGET"
CleanCompanyName("ACME & Partners")  ' -> "ACME PARTNERS"

TokenSortKey(s) → String

Sorts the space-separated tokens of a cleaned name alphabetically. This means "WIDGET BLUE ABC" and "ABC BLUE WIDGET" produce the same key, so word order differences (common when the same vendor is entered differently in different systems) do not reduce the similarity score.

ScorePair(aClean, aTok, bClean, bTok) → Double

The main entry point for vendor name matching. Combines three strategies and returns the maximum:

1. Levenshtein similarity on cleaned names (handles typos and abbreviations)
2. Levenshtein similarity on token-sorted keys (handles word-order differences)
3. Substring containment boost — returns 0.9 if one cleaned name is contained within the other (handles short codes vs full names)

Pre-compute CleanCompanyName and TokenSortKey once for each side before entering the inner loop to avoid O(n²) redundant work.

' Pre-compute once for the query name:
Dim aClean As String : aClean = CleanCompanyName(queryVendor)
Dim aTok   As String : aTok   = TokenSortKey(aClean)

' Inner loop over candidates:
Const ACCEPT_THRESHOLD As Double = 0.72

Dim i As Long
For i = 2 To lastRow
    Dim bClean As String : bClean = CleanCompanyName(CStr(oracleWS.Cells(i, colName).Value))
    Dim bTok   As String : bTok   = TokenSortKey(bClean)
    Dim score  As Double : score  = ScorePair(aClean, aTok, bClean, bTok)
    If score >= ACCEPT_THRESHOLD Then
        ' Found a match — record it and exit
        matchName = CStr(oracleWS.Cells(i, colName).Value)
        Exit For
    End If
Next i

QuickSortStrings(arr(), first, last)

In-place ascending quicksort on a string array slice. Used internally by TokenSortKey. Can also be used directly on any string array you need sorted.

9. LIB5 — Logging

Two complementary logging models in one module. Both create the Log sheet automatically on first call and never crash if the sheet is absent. Depends on LIB2.

Model A — SafeLog (3-column, event-level)

SafeLog(wb, where, message)

Appends one row to the Log sheet with three columns: Timestamp, Where (caller label), Message. Best for event-level tracing: recording that something happened, that a file was opened, that a match was not found.

SafeLog ThisWorkbook, "FuzzyMatch", "No match found for vendor: " & vendorName
SafeLog ThisWorkbook, "BuildRegistry", "Registry loaded: " & entryCount & " entries"

Model B — GF_LogRowAction (9-column, row-level)

GF_LogRowAction(wb, macroName, sourceFile, sourceSheet, sourceRow, indexVal, decisionVal, outcome, actions)

Appends one row to the Log sheet with nine columns: Timestamp, Macro, Source File, Source Sheet, Source Row, Index, Decision, Outcome, Actions/Notes. Best for row-by-row processing records where you need a full audit trail of which rows were accepted, skipped, or fixed and why.

' Summary event (no specific row):
GF_LogRowAction ThisWorkbook, "BuildToDoSheet", "", "", 0, "", "", _
                "START", "Log cleared for new run"

' Row skipped:
GF_LogRowAction ThisWorkbook, "BuildToDoSheet", srcFile, srcTab, i, idxVal, _
                "", "SKIPPED", "Duplicate of source row 14 (KEEP_FIRST mode)"

' Row fixed and kept:
GF_LogRowAction ThisWorkbook, "BuildToDoSheet", srcFile, srcTab, i, idxVal, _
                "", "FIXED_AND_PROCESSED", "PRICE: [1.234,56] -> [1234.56] (EU format converted)"

GF_EnsureLogSheet(wb) → Worksheet

Ensures the Log sheet exists with 9-column headers. Returns the worksheet. Called internally by GF_LogRowAction; you rarely need to call it directly unless you want to pre-create the sheet before the first action row.

GF_ClearLogSheet(wb)

Clears all data rows (A2 downward) while keeping the header row intact. Call at the start of a new run so the log only contains entries for the current session.

' Always clear at the start of a run:
GF_ClearLogSheet ThisWorkbook
GF_LogRowAction ThisWorkbook, "MyMacro", "", "", 0, "", "", "START", "New run started"

10. LIB6 — Progress UI

A thin wrapper module that connects your macro's main loop to a progress bar UserForm. The wrapper isolates all form references so the macro compiles and runs cleanly even when the form is not present (useful during testing). Requires an frmProgress UserForm in the same workbook.

Setup for a new project

1. Import LIB6_ProgressUI.bas into the project.
2. Create (or import from another project) a UserForm named frmProgress that exposes these members:
   • Sub Init(titleText As String, totalItems As Long)
   • Sub UpdateProgress(processed As Long)
   • Sub SetTitle(s As String)
   • Sub ConfigureTimeGate(enable As Boolean, ms As Long, min As Long)
   • Sub BeginMarquee(msg As String)
   • Sub EndMarquee()
   • Property Get IsCancelled() As Boolean
3. If you rename the form (e.g. to frmBPAProgress), update the two lines marked ' *** rename here in the module.

Function reference

ProgressStart(titleText, totalItems)

Shows the form, calls Init, sets a 200 ms time gate (so fast loops don't flicker), and yields to Windows with DoEvents. Call once before the main loop.

ProgressUpdate(processed)

Updates the bar value and calls DoEvents. The DoEvents here is critical — it is what prevents Excel from going "Not Responding" during long loops and what allows the Cancel button click to be processed. Call once per loop iteration.

ProgressCancelled() → Boolean

Returns True if the user clicked the Cancel button on the form. Check this inside your loop to stop gracefully.

ProgressMarquee(msg) / ProgressEndMarquee()

Switches the bar to indeterminate (spinning) mode for phases where you don't know the total item count (e.g. sorting, saving). Call ProgressEndMarquee when that phase ends to return to normal mode.

ProgressFinish()

Hides and unloads the form, clears the status bar. Call from the CleanUp: label so it always runs, even if the macro aborted early.

' Typical main-loop pattern:
ProgressStart "Processing rows...", lastRow - 1

Dim i As Long
For i = 2 To lastRow
    If ProgressCancelled() Then
        SafeLog ThisWorkbook, "MyMacro", "User cancelled at row " & i
        GoTo CleanUp
    End If
    ProgressUpdate i - 1

    ' ... process row i ...
Next i

ProgressMarquee "Saving output file..."
' ... save file ...
ProgressEndMarquee

CleanUp:
    ProgressFinish

11. LIB7 — Numeric Utilities

Digit extraction, account-number normalisation (scientific notation, leading zeros), and price format conversion from EU comma-decimal to US dot-decimal with salvage logic. No dependencies.

Digit extraction

OnlyDigits(s) → String

Returns only the digit characters (0–9) from a string. All other characters are removed. Leading zeros are preserved because the result is a string, not a number.

OnlyDigits("ACC-0012345 ")   ' -> "0012345"
OnlyDigits("AU_611_GDSAU_OU") ' -> "611"

IsNumericDigits(s) → Boolean

Returns True if s is non-empty and consists entirely of digits 0–9.

TailMask(acct) → String

Masks an account number to its last 4 digits for safe display in notes, logs, or emails. Strings shorter than 4 digits are returned unchanged.

TailMask("1234567890")   ' -> "...7890"
TailMask("AB12")          ' -> "AB12" (too short to mask, returned as-is)

Account number normalisation

ExpandScientificNotation(s) → String

Expands an Excel scientific-notation string (e.g. 1.53697E+11) to its full plain digit string (153697000000) without using VBA's numeric types, which would lose precision. Returns "" if no E character is found.

NormalizeAccountStr(s) → String

Uses VBScript.RegExp for robust detection of scientific notation; returns the expanded digit string. Non-scientific strings are returned unchanged. Use when reading account numbers from cells that Excel may have auto-formatted as scientific notation.

NormalizeAccountStr("1.53697E+11")   ' -> "153697000000"
NormalizeAccountStr("ACC001234")      ' -> "ACC001234" (unchanged)

CanonicalizeAccountStr(s) → String

Full normalisation pipeline: handles scientific notation, strips all non-digit characters, removes trailing .00 decimal portions. Use to normalise both sides before exact-match comparison of account numbers from different source systems.

CanonicalizeAccountStr("ACC-001234.00")   ' -> "001234"
CanonicalizeAccountStr("1.23E+8")          ' -> "123000000"
CanonicalizeAccountStr("  007 500  ")      ' -> "007500"

Price normalisation (EU → US format)

GF_PriceNeedsNormalization(v) → Boolean

Returns True if the value looks like it needs conversion. Checks for: letter or currency symbol characters (€, $, £, ¥), multiple dots, multiple commas, any comma, or any space within a numeric-looking value.

If GF_PriceNeedsNormalization(ws.Cells(i, colPrice).Value) Then
    ' warn user or auto-fix
End If

GF_NormalizePriceToUS(v, wasChanged, note) → String

Strips currency symbols, converts EU comma-decimal format to US dot-decimal, forces exactly 2 decimal places. Returns a formatted string like "1234.56". Sets wasChanged = True if any transformation was applied. Sets note to a plain-English description of what changed. Returns "" if the value is empty or cannot be parsed after cleanup.

Salvage logic (controlled by module-level constants):

• "13.218.22" → "13218.22" (multi-dot: last segment is decimals, earlier dots are thousands separators)
• "13,218,22" → "13218.22" (multi-comma: same logic)

Dim priceNorm As String
Dim wasChanged As Boolean, priceNote As String
priceNorm = GF_NormalizePriceToUS(ws.Cells(i, colPrice).Value, wasChanged, priceNote)
If Len(priceNorm) = 0 Then
    GF_LogRowAction ThisWorkbook, "MyMacro", srcFile, srcTab, i, idxVal, _
                    "", "SKIPPED", "Unparseable price: [" & CStr(ws.Cells(i, colPrice).Value) & "]"
    GoTo NextRow
End If
ws.Cells(i, colPrice).NumberFormat = "@"
ws.Cells(i, colPrice).Value = priceNorm
If wasChanged Then SafeLog ThisWorkbook, "PriceFix", "Row " & i & ": " & priceNote

GF_FormatDot2NoThousands(n) → String

Locale-safe "0.00" formatting. Always outputs a dot as decimal separator and no thousands separator, regardless of Windows regional settings. Use when writing a price to a cell or file that will be processed by another system.

GF_IsDecisionYes(v) → Boolean

Returns True for "YES" or "Y" (case-insensitive). Use to parse a decision/flag column where the user types Yes, YES, Y, or yes.

12. LIB8 — HTML Utilities

HTML email composition utilities: entity encoding, plain-text-to-HTML conversion with bullet detection, email address validation and normalisation, attachment path building, and ON/OFF flag parsing. No dependencies.

HTML encoding

HtmlEncode(s) → String

Escapes the five HTML special characters so they display literally in a browser or Outlook: & → &, < → <, > → >, " → ", ' → '.

HtmlEncode("AT&T <sender>")   ' -> "AT&amp;T &lt;sender&gt;"

Plain text to HTML conversion

PlainToHtml(txt) → String

Simple conversion: encodes entities and converts line breaks to <br>. Use for signature blocks or plain paragraphs where you do not need bullet list detection.

PlainToHtmlRich(txt) → String

Full rich conversion:

• Blank lines → <br><br> (paragraph break)
• Lines starting with •, -, or * → <li> items inside <ul>
• All other lines → encoded text + <br>
• Lists are automatically opened and closed as the bullet/non-bullet state changes

Dim body As String
body = "Dear Vendor," & vbCrLf & vbCrLf
body = body & "Please review your account details:" & vbCrLf
body = body & "- Bank: ANZ" & vbCrLf
body = body & "- Account: ...7890" & vbCrLf
Dim htmlBody As String
htmlBody = PlainToHtmlRich(body)
mail.HTMLBody = WrapHtml(htmlBody)

NormalizeParagraphs(s) → String

Collapses 3 or more consecutive blank lines to 1. Call on the body string before passing it to PlainToHtmlRich to avoid excessive vertical whitespace when users paste from Word.

Email body composition

ComposeBodyHtml(baseText, accountsHtml) → String

Replaces the placeholder [[BANK_ACCOUNTS]] in baseText with accountsHtml, converting the rest of the body to rich HTML. If the placeholder is absent, the accounts block is appended at the end. Use for templated vendor notification emails where the account list is injected at a specific position in the body.

Dim tmplBody As String
tmplBody = CStr(ws.Range("B5").Value)   ' "Please review [[BANK_ACCOUNTS]] above."
Dim acctHtml As String
acctHtml = BuildAccountsHtml(acctList)
mail.HTMLBody = WrapHtml(ComposeBodyHtml(tmplBody, acctHtml))

BuildAccountsHtml(listL) → String

Converts a comma- or newline-separated list of account numbers to an HTML bullet list (<ul>). Returns <em>Not currently on file.</em> for empty input.

WrapHtml(bodyInner) → String

Wraps a body fragment in a complete HTML document with charset UTF-8 and Calibri/Arial 11pt body font. Assign the result directly to mail.HTMLBody.

Email address utilities

IsValidEmail(e) → Boolean

Basic structural check: verifies @ exists with at least one character before it, a dot exists after the @, and the dot is not the last character. Not a full RFC validator — fast enough for filtering obviously bad addresses.

NormalizeEmails(s) → String

Splits a semicolon-or-comma-separated list of addresses, validates each with IsValidEmail, and returns the valid ones joined with semicolons (Outlook format). Returns "" if no valid addresses are found.

mail.To = NormalizeEmails(ws.Cells(i, colEmail).Value)
If Len(mail.To) = 0 Then
    SafeLog ThisWorkbook, "SendEmail", "Row " & i & " — no valid To address, skipped"
    GoTo NextRow
End If

Attachment path and flag parsing

BuildAttachmentPath(folder, fname) → String

Builds a full attachment file path from a folder and filename. Returns "" if either part is empty. Handles the case where the folder already ends with a separator.

ParseOnOff(v [, defaultVal]) → Boolean

Interprets ON/OFF/YES/NO/TRUE/FALSE/1/0 strings as Boolean. Returns defaultVal (default: True) for any unrecognised input (including blank). Use to read toggle settings from cells or configuration tables.

Dim sendAttach As Boolean
sendAttach = ParseOnOff(ws.Range("B8").Value, False)   ' default off if blank

13. LIB9 — Org Code Utilities

Oracle Operating Unit (OU) code extraction and symmetric matching. Handles the three ORG formats that appear in Oracle ERP: full string, short code, and digits only. No dependencies.

Oracle ORG formats

Extraction

OrgShortCode(orgFull) → String

Returns "AU_611" from "AU_611_GDSAU_OU". Returns "" if the string does not match the expected pattern (2-char country code + underscore + 3-digit OU).

OrgDigitsCode(orgFull) → String

Returns "611" from "AU_611_GDSAU_OU". Returns "" if the string does not match.

Matching

OrgMatches(sourceOrg, userOrg) → Boolean

Returns True if sourceOrg and userOrg refer to the same Operating Unit, regardless of which format either side uses. The matching is symmetric — either argument can be in any format. A blank userOrg is treated as a wildcard and always returns True.

' Filtering Oracle rows by user-selected OU:
Dim userOU As String
userOU = Trim$(CStr(wsRun.Range("K6").Value))   ' "" means "all OUs"

Dim i As Long
For i = 2 To lastRow
    Dim rowOrg As String
    rowOrg = GF_StrictClean(CStr(oracleWS.Cells(i, colOrg).Value))
    If Not OrgMatches(rowOrg, userOU) Then GoTo NextRow
    ' ... process row i ...
NextRow:
Next i

14. Use-Case Scenarios

These end-to-end examples show how the library modules work together in realistic situations drawn from the macros in this repository.

' Step 1: auto-detect the header row anywhere in rows 1-5
Dim hdrRow As Long
hdrRow = DetectHeaderRow(dataWS, Array("Supplier Num", "Email", "OU"), 1, 5)
If hdrRow = 0 Then
    MsgBox "Cannot identify header row in source file."
    GoTo CleanUp
End If

' Step 2: map all headers to column numbers
Dim hdrMap As Object
Set hdrMap = MapHeaderColumns(dataWS, hdrRow)

' Step 3: look up each column with synonym lists
Dim colSupplier As Long
Dim colEmail    As Long
Dim colOU       As Long
Dim colVendName As Long

colSupplier = GetColByHeader(hdrMap, Array("Supplier Num", "Supplier Number", "Vendor ID"))
colEmail    = GetColByHeader(hdrMap, Array("E-mail", "Email", "Email Address", "Contact Email"))
colOU       = GetColByHeader(hdrMap, Array("OU", "Org", "Operating Unit"))
colVendName = GetColByHeader(hdrMap, Array("Vendor Name", "Supplier Name", "Name"))

' Step 4: process rows (hdrRow+1 onward)
Dim lastRow As Long
lastRow = dataWS.UsedRange.Row + dataWS.UsedRange.Rows.Count - 1

Dim i As Long
For i = hdrRow + 1 To lastRow
    Dim supplierNum As String
    supplierNum = GF_StrictClean(NzStr(dataWS.Cells(i, colSupplier).Value))
    If Len(supplierNum) = 0 Then GoTo NextRow

    Dim email As String
    email = NormalizeEmails(NzStr(dataWS.Cells(i, colEmail).Value))

    ' ... process row ...
NextRow:
Next i

Const ACCEPT_THRESHOLD As Double = 0.72

' Pre-load Oracle names into an array once:
Dim oracleCount As Long
oracleCount = oracleWS.Cells(oracleWS.Rows.Count, colOracleName).End(xlUp).Row - 1
Dim oracleClean() As String, oracleTok() As String
ReDim oracleClean(1 To oracleCount)
ReDim oracleTok(1 To oracleCount)
Dim j As Long
For j = 1 To oracleCount
    oracleClean(j) = CleanCompanyName(NzStr(oracleWS.Cells(j + 1, colOracleName).Value))
    oracleTok(j)   = TokenSortKey(oracleClean(j))
Next j

' Match each bank row:
Dim i As Long
For i = 2 To lastBankRow
    Dim bankName As String
    bankName = GF_StrictClean(NzStr(bankWS.Cells(i, colBankName).Value))
    Dim aClean As String : aClean = CleanCompanyName(bankName)
    Dim aTok   As String : aTok   = TokenSortKey(aClean)

    Dim bestScore As Double : bestScore = 0
    Dim bestIdx   As Long   : bestIdx   = 0
    For j = 1 To oracleCount
        Dim score As Double
        score = ScorePair(aClean, aTok, oracleClean(j), oracleTok(j))
        If score > bestScore Then
            bestScore = score
            bestIdx   = j
        End If
    Next j

    If bestScore >= ACCEPT_THRESHOLD Then
        bankWS.Cells(i, colMatchName).Value  = NzStr(oracleWS.Cells(bestIdx + 1, colOracleName).Value)
        bankWS.Cells(i, colMatchScore).Value = Format$(bestScore, "0.00")
        bankWS.Cells(i, colMatchStatus).Value = "MATCHED"
    Else
        bankWS.Cells(i, colMatchStatus).Value = "NO MATCH"
        SafeLog ThisWorkbook, "FuzzyMatch", "No match for: " & bankName & _
                              " (best=" & Format$(bestScore, "0.00") & ")"
    End If
Next i

Public Sub RunGenerateReport()
    On Error GoTo CleanUp

    ' 1. Build output folder and filename
    Dim outFolder As String
    outFolder = PathJoin(ws.Range("B5").Value, FormatDateForFilename(Date))
    If Not EnsureFolderExists(outFolder) Then
        MsgBox "Cannot create output folder: " & outFolder
        GoTo CleanUp
    End If

    Dim outFile As String
    outFile = PathJoin(outFolder, _
              FormatDateForFilename(Date) & "_" & SanitizeSegment(ws.Range("B2").Value) & ".xlsx")

    ' 2. Prepare output sheet
    Dim wsOut As Worksheet
    Set wsOut = GetOrCreateSheet(ThisWorkbook, "Output")
    ClearSheetAndTables wsOut
    wsOut.Cells(1, 1).Value = "Vendor Name"
    wsOut.Cells(1, 2).Value = "Account"
    wsOut.Cells(1, 3).Value = "Status"
    StyleHeader wsOut.Rows(1)

    ' 3. Clear log and log the run start
    GF_ClearLogSheet ThisWorkbook
    GF_LogRowAction ThisWorkbook, "RunGenerateReport", "", "", 0, "", "", "START", _
                    "Output: " & outFile

    ' 4. Show progress bar and process rows
    Dim lastRow As Long : lastRow = dataWS.UsedRange.Rows.Count
    ProgressStart "Generating report...", lastRow - 1

    Dim outRow As Long : outRow = 2
    Dim i As Long
    For i = 2 To lastRow
        If ProgressCancelled() Then
            GF_LogRowAction ThisWorkbook, "RunGenerateReport", "", "", 0, "", "", "ABORTED", _
                            "User cancelled at row " & i
            GoTo CleanUp
        End If
        ProgressUpdate i - 1

        Dim vendName As String : vendName = GF_StrictClean(NzStr(dataWS.Cells(i, 1).Value))
        If Len(vendName) = 0 Then GoTo NextRow

        Dim acctRaw As Variant : acctRaw = dataWS.Cells(i, 2).Value
        Dim acctNorm As String : acctNorm = CanonicalizeAccountStr(CStr(acctRaw))

        wsOut.Cells(outRow, 1).Value = vendName
        wsOut.Cells(outRow, 2).Value = acctNorm
        wsOut.Cells(outRow, 3).Value = "PROCESSED"
        outRow = outRow + 1

NextRow:
    Next i

    ' 5. Save the output file
    ProgressMarquee "Saving file..."
    ThisWorkbook.SaveCopyAs outFile
    ProgressEndMarquee

    GF_LogRowAction ThisWorkbook, "RunGenerateReport", "", "", 0, "", "", "DONE", _
                    "Rows written: " & outRow - 2

CleanUp:
    ProgressFinish
End Sub

Dim i As Long
For i = 2 To lastRow
    Dim rawEmail  As String : rawEmail  = NzStr(ws.Cells(i, colEmail).Value)
    Dim toAddrs   As String : toAddrs   = NormalizeEmails(rawEmail)
    If Len(toAddrs) = 0 Then
        GF_LogRowAction ThisWorkbook, "SendVendorEmail", srcFile, "Data", i, _
                        NzStr(ws.Cells(i, colIndex).Value), "", _
                        "SKIPPED", "No valid email address in: [" & rawEmail & "]"
        GoTo NextRow
    End If

    ' Build accounts HTML from a comma-separated cell value:
    Dim acctList  As String : acctList  = NzStr(ws.Cells(i, colAccounts).Value)
    Dim acctHtml  As String : acctHtml  = BuildAccountsHtml(acctList)

    ' Compose full body from template with placeholder injection:
    Dim tmplBody  As String : tmplBody  = NzStr(templateWS.Range("B2").Value)
    Dim bodyHtml  As String : bodyHtml  = ComposeBodyHtml(tmplBody, acctHtml)

    ' Build attachment path (if configured):
    Dim attachPath As String
    attachPath = BuildAttachmentPath(ws.Cells(i, colAttachFolder).Value, _
                                     ws.Cells(i, colAttachFile).Value)

    ' Send via Outlook:
    Dim mail As Object
    Set mail = CreateObject("Outlook.Application").CreateItem(0)
    With mail
        .To        = toAddrs
        .Subject   = "Bank Account Verification — " & FormatDateLong(Date)
        .HTMLBody  = WrapHtml(bodyHtml)
        If Len(attachPath) > 0 Then .Attachments.Add attachPath
        .Send
    End With

    GF_LogRowAction ThisWorkbook, "SendVendorEmail", srcFile, "Data", i, _
                    NzStr(ws.Cells(i, colIndex).Value), "", _
                    "DONE", "Sent to: " & toAddrs

NextRow:
Next i

Dim userOU As String
userOU = GF_StrictClean(NzStr(wsRun.Range("K6").Value))
' userOU could be "", "611", "AU_611", or "AU_611_GDSAU_OU"

Dim i As Long
For i = 2 To lastRow
    Dim rowOrg As String
    rowOrg = GF_StrictClean(NzStr(oracleWS.Cells(i, colOrg).Value))

    If Not OrgMatches(rowOrg, userOU) Then GoTo NextRow
    ' Row's OU matches the filter (or filter is blank) — include it

    ' ... process the row ...
NextRow:
Next i

' Pre-scan: count how many rows need fixing to warn the user first
Dim issueCount As Long : issueCount = 0
Dim i As Long
For i = 2 To lastRow
    If GF_PriceNeedsNormalization(dataWS.Cells(i, colPrice).Value) Then
        issueCount = issueCount + 1
    End If
Next i

If issueCount > 0 Then
    Dim answer As VbMsgBoxResult
    answer = MsgBox(issueCount & " rows have non-standard price formatting." & vbCrLf & _
                    "Click YES to auto-convert, NO to skip those rows.", _
                    vbYesNo + vbExclamation, "Price Format Warning")
    If answer = vbNo Then
        MsgBox "Macro cancelled."
        Exit Sub
    End If
End If

' Processing loop:
For i = 2 To lastRow
    Dim rawPrice As Variant : rawPrice = dataWS.Cells(i, colPrice).Value
    Dim normPrice As String
    Dim wasChanged As Boolean, priceNote As String
    normPrice = GF_NormalizePriceToUS(rawPrice, wasChanged, priceNote)

    If Len(normPrice) = 0 Then
        GF_LogRowAction ThisWorkbook, "ImportPrices", srcFile, srcTab, i, _
                        NzStr(dataWS.Cells(i, colIndex).Value), "", _
                        "SKIPPED", "Unparseable price: [" & CStr(rawPrice) & "]"
        GoTo NextRow
    End If

    dataWS.Cells(i, colPrice).NumberFormat = "@"
    dataWS.Cells(i, colPrice).Value = normPrice

    If wasChanged Then
        GF_LogRowAction ThisWorkbook, "ImportPrices", srcFile, srcTab, i, _
                        NzStr(dataWS.Cells(i, colIndex).Value), "", _
                        "FIXED_AND_PROCESSED", "PRICE: [" & CStr(rawPrice) & "] -> [" & normPrice & "] " & priceNote
    End If
NextRow:
Next i

15. What NOT to Do

1. Using Worksheets("name") directly

Worksheets("name") raises Runtime Error 9 if the sheet does not exist. Always use GetSheetIfExists() or GetOrCreateSheet() from LIB2.

' WRONG — crashes if "Config" sheet is missing:
Set wsConfig = ThisWorkbook.Worksheets("Config")

' RIGHT:
Set wsConfig = GetSheetIfExists(ThisWorkbook, "Config")
If wsConfig Is Nothing Then MsgBox "Config sheet missing": GoTo CleanUp

2. Reading cells without NzStr

Excel error values (#N/A, #VALUE!) and Null values crash CStr(). Always wrap cell reads with NzStr().

' WRONG — crashes if cell contains #N/A:
Dim v As String : v = CStr(ws.Cells(i, c).Value)

' RIGHT:
Dim v As String : v = NzStr(ws.Cells(i, c).Value)

3. Pre-computing CleanCompanyName inside the inner loop

CleanCompanyName and TokenSortKey do string replacement work on every call. If you are comparing one query name against 5,000 Oracle rows, call them once for the query name before the loop — not inside it.

' WRONG — computes the same query key 5000 times:
For i = 2 To 5001
    score = ScorePair(CleanCompanyName(queryName), TokenSortKey(CleanCompanyName(queryName)), _
                      oracleClean(i), oracleTok(i))
Next i

' RIGHT — compute once:
Dim aClean As String : aClean = CleanCompanyName(queryName)
Dim aTok   As String : aTok   = TokenSortKey(aClean)
For i = 2 To 5001
    score = ScorePair(aClean, aTok, oracleClean(i), oracleTok(i))
Next i

4. Using GF_LogRowAction with 0-indexed arrays

Worksheet Range().Value arrays are 1-based. Never access index 0 on an array read from a worksheet range.

5. Calling AppLock from a step module

AppLock (the Application.ScreenUpdating / EnableEvents / DisplayAlerts / Calculation toggle) always lives in the Orchestrator module. Step modules (Helpers, Extract, etc.) never call it directly. See CLAUDE.md §9 for the canonical implementation.

6. Forgetting ProgressFinish in early-exit paths

Always call ProgressFinish from the CleanUp: label, not just at the end of the normal execution path. If your macro errors out or the user aborts, the form will stay open and block Excel.

' WRONG — ProgressFinish only on normal path:
ProgressStart "Working...", 100
If someError Then Exit Sub    ' form left open!

' RIGHT — ProgressFinish always reached:
ProgressStart "Working...", 100
If someError Then GoTo CleanUp

CleanUp:
    ProgressFinish