# Vba-source

The `vba-source` module is a reconciliation pipeline for batch-importing financial transaction data, matching it against bank statements, and exporting the results as an Excel report. It lives in an Access VBA backend and is typically driven by a user form (`frmImport`), exposing three high-level operations: **Import**, **Reconcile**, and **Export**.

## Overview

This module handles the full lifecycle of a reconciliation batch:

1. **Import** — The user picks an Excel file (either a bank statement or internal transactions). The module reads each row, validates it using `clsTransaction`, and inserts valid rows into the appropriate table (`tblTransactions` or `tblBankStatement`), keyed by a generated batch ID.
2. **Reconcile** — Each imported transaction is compared against bank statements. Matching uses three tiers: exact `RefNo` match, fuzzy date+amount matching (within +-1 day), and finally an "unmatched" flag for anything that doesn't fit. Results land in `tblReconcileResult`.
3. **Export** — The reconcile results are written to a fresh Excel workbook with color-coded variance rows and saved to the project directory.

A shared utility module (`modUtil`) provides batch numbering, SQL-safe formatting helpers, and error logging across all phases.

## Key Classes and Modules

### `clsTransaction`

[clsTransaction.cls](vba-source/clsTransaction.cls) is the only class module in this package and serves as the single data model for a financial transaction during import. It exposes five public fields:

| Field | Type | Purpose |
|---|---|---|
| `TransID` | `Long` | Database primary key (zero during import, filled after insert) |
| `Branch` | `String` | The originating branch code |
| `TxnDate` | `Date` | Transaction date |
| `Amount` | `Currency` | Transaction amount |
| `RefNo` | `String` | A unique reference number used for reconciliation matching |

#### `IsValid() As Boolean`

Validates a transaction record before it is persisted. A record is valid only if all four conditions hold:

- `Amount` is strictly positive
- `RefNo` is not empty or whitespace-only
- `Branch` is not empty
- `TxnDate` is on or after January 1, 2000

```vba
Public Function IsValid() As Boolean
    IsValid = (Amount > 0) And (Len(Trim(RefNo)) > 0) And (Branch <> "") _
              And (TxnDate > DateSerial(2000, 1, 1))
End Function
```

Rows that fail validation are counted and logged via `modUtil.LogError`; they are not inserted into the database.

### `modImport`

[modImport.bas](vba-source/modImport.bas) orchestrates the Excel file import. It contains two public members:

#### `RunImport() As Long`

The main import function. Steps:

1. Reads the file path from `Forms!frmImport!txtFilePath.Value`.
2. Determines whether the file is a bank statement by checking if the filename contains "BankStatement" (case-insensitive).
3. Requests the next batch number from `modUtil.GetNextBatchNumber()`.
4. Opens the Excel file with a retry loop (up to 3 attempts) to handle file-lock contention — if error 70 ("Permission Denied") occurs, it sleeps for 2 seconds and retries.
5. Scans the Excel sheet from row 2 (header in row 1) down to the last used row in column B (`TxnDate`), building a `clsTransaction` object for each row.
6. For each transaction:
   - If it's a bank statement file (`isBankFile` = true), inserts into `tblBankStatement` with `Branch` hardcoded to `"BANK"`.
   - Otherwise, inserts into `tblTransactions` with the branch value from column A.
   - SQL strings are built inline using `modUtil.FormatDateForSql` and `modUtil.FormatNumberForSql` for locale-safe formatting. Single quotes in `RefNo` and `FileName` are doubled for SQL escaping.
7. After the loop, inserts a summary record into `tblImportBatch` and updates `modUtil.gCurrentBatchID` for downstream phases.
8. Returns the batch ID.

If the file cannot be opened after 3 attempts, an error is logged and the batch ID is returned anyway so the user can inspect `tblErrorLog`.

#### `BrowseForFile()`

Opens an Office file-picker dialog filtered to `*.xlsx` files. The selected path is written back to `Forms!frmImport!txtFilePath.Value`.

### `modReconcile`

[modReconcile.bas](vba-source/modReconcile.bas) performs the matching logic between imported transactions and bank statements.

#### `RunReconcile(Optional batchID As Long = -1) As Long`

Matches transactions to bank statements and records results. Steps:

1. Resolves the effective batch ID (uses `modUtil.gCurrentBatchID` if not specified).
2. Deletes stale results from `tblReconcileResult` for the current batch.
3. Iterates through `tblTransactions` for this batch:
   - **Duplicate check** — If the same `RefNo` appears more than once in the batch, all occurrences are flagged as `"Duplicate"` with zero variance.
   - **Exact match** — Looks for a bank statement row with an identical `BankRefNo`. If found, inserts a result with status `"Matched"` and variance = `transaction amount - bank amount`.
   - **Fuzzy fallback** — If no exact match, looks for a bank statement with the same `Amount` and a `TxnDate` within +-1 day. If found, status is `"Matched (Fuzzy)"`.
   - **Unmatched** — If neither match succeeds, status is `"Unmatched"` and variance equals the full transaction amount.
4. Returns the count of matched transactions (exact + fuzzy).

#### `InsertResult(db, transID, stmtID, status, variance)`

A private helper that builds and executes an `INSERT` into `tblReconcileResult`. It records who reconciled the transaction (`Environ("USERNAME")`) and when (`Now()`). Uses `IIf(IsNull(...), "NULL", ...)` for safe NULL propagation.

### `modUtil`

[modUtil.bas](vba-source/modUtil.bas) is the shared utility layer used by all three phases.

#### `gCurrentBatchID As Long`

Module-level public variable that tracks the most recent batch ID. Set at the end of `RunImport` so that `RunReconcile` and `RunExport` can find the right batch without explicit parameters.

#### `GetNextBatchNumber() As Long`

Queries the maximum `BatchID` in `tblImportBatch` and returns `MaxID + 1` (starting at 1 if the table is empty). This is the sole source of batch IDs.

#### `LogError(batchID, rowNo, msg)`

Inserts a row into `tblErrorLog` with the current timestamp. Called when a transaction row fails validation or when a file cannot be opened.

#### `FormatDateForSql(d As Date) As String`

Returns a date literal in Access SQL format: `#mm/dd/yyyy#`.

#### `FormatNumberForSql(n As Currency) As String`

Converts a `Currency` value to a string safe for SQL insertion. Uses `Str()` to avoid locale issues (e.g., Vietnamese Windows uses `,` as the decimal separator, which would corrupt the SQL string) and trims the leading space that `Str()` adds for non-negative numbers.

### `modExport`

[modExport.bas](vba-source/modExport.bas) generates the final reconciliation report.

#### `RunExport(Optional batchID As Long = -1) As String`

1. Queries a joined result set from `tblReconcileResult` and `tblTransactions` for the given batch.
2. Launches a hidden Excel instance, creates a new workbook, and writes column headers: `RefNo`, `Branch`, `TxnDate`, `Amount`, `MatchStatus`, `Variance`.
3. Fills in data rows. Any row where `Variance <> 0` is highlighted in light red (`RGB(255, 199, 199)`).
4. Auto-fits columns, saves the workbook to `ReconcileReport_yyyymm.xlsx` in the Access project directory, and closes Excel cleanly.
5. Returns the output file path.

## How It Works

The module implements a classic three-phase batch reconciliation pipeline:

```mermaid
flowchart LR
    subgraph importPhase["Import Phase"]
        BrowseForFile["BrowseForFile - User selects file"]
        RunImport["RunImport - Reads Excel, validates, inserts rows"]
    end

    subgraph reconcilePhase["Reconcile Phase"]
        RunReconcile["RunReconcile - Matches transactions against bank statements"]
        InsertResult["InsertResult - Writes match results to tblReconcileResult"]
    end

    subgraph exportPhase["Export Phase"]
        RunExport["RunExport - Generates Excel report"]
    end

    BrowseForFile --> RunImport
    RunImport --> RunReconcile
    RunReconcile --> RunExport
    modUtil --> RunImport
    modUtil --> RunReconcile
    modUtil --> RunExport
    modUtil --> InsertResult
    clsTransaction["clsTransaction - Validation"] --> RunImport
```

A typical operation flow:

1. **User opens** the import form and clicks "Browse" → `BrowseForFile` populates the file path.
2. **User triggers import** → `RunImport` opens the Excel file, iterates rows, validates each with `clsTransaction.IsValid()`, and inserts valid rows into the appropriate table. Invalid rows are logged to `tblErrorLog`. A batch ID is assigned and stored in `modUtil.gCurrentBatchID`.
3. **User triggers reconcile** → `RunReconcile` reads transactions for the batch and attempts three-match-tier matching against bank statements. Results are written to `tblReconcileResult`.
4. **User triggers export** → `RunExport` reads from `tblReconcileResult` joined with `tblTransactions`, writes an Excel report with variance highlighting, and saves it to disk.

The `modUtil.gCurrentBatchID` variable acts as implicit state passing between phases — this is a simple pattern but means the phases must be executed in order (import first, then reconcile, then export) for the same data.

## Data Model

The module interacts with five database tables. Here are the relevant schemas inferred from the code:

| Table | Key Columns | Purpose |
|---|---|---|
| `tblImportBatch` | `BatchID`, `FileName`, `ImportDate`, `RowCount`, `Status` | Tracks each import run |
| `tblTransactions` | `TransID` (PK), `Branch`, `TxnDate`, `Amount`, `RefNo`, `ImportBatchID` (FK) | Imported internal transactions |
| `tblBankStatement` | `StmtID` (PK), `TxnDate`, `Amount`, `BankRefNo`, `ImportBatchID` (FK) | Imported bank statement rows |
| `tblReconcileResult` | `TransID`, `StmtID`, `MatchStatus`, `Variance`, `ReconciledBy`, `ReconciledDate` | Reconciliation outcomes |
| `tblErrorLog` | `BatchID`, `RowNo`, `ErrorMsg`, `Timestamp` | Validation and import errors |

Relationships:

```mermaid
flowchart LR
    subgraph DataLayer["Database Tables"]
        tblTransactions["tblTransactions"]
        tblBankStatement["tblBankStatement"]
        tblImportBatch["tblImportBatch"]
        tblReconcileResult["tblReconcileResult"]
        tblErrorLog["tblErrorLog"]
    end

    subgraph LogicLayer["VBA Logic"]
        modImport["modImport"]
        modReconcile["modReconcile"]
        modExport["modExport"]
        modUtil["modUtil"]
    end

    subgraph ModelLayer["Data Model"]
        clsTransaction["clsTransaction"]
    end

    modImport --> modUtil
    modImport --> clsTransaction
    modImport --> tblTransactions
    modImport --> tblBankStatement
    modImport --> tblImportBatch
    modImport --> tblErrorLog

    modReconcile --> modUtil
    modReconcile --> tblTransactions
    modReconcile --> tblBankStatement
    modReconcile --> tblReconcileResult

    modExport --> modUtil
    modExport --> tblTransactions
    modExport --> tblReconcileResult
```

- `tblTransactions.ImportBatchID` and `tblBankStatement.ImportBatchID` link rows to a specific import batch.
- `tblReconcileResult.TransID` and `tblReconcileResult.StmtID` form foreign-key-like links to transactions and bank statements.
- `tblErrorLog.BatchID` associates errors with an import batch.

## Dependencies and Integration

### External Dependencies

- **Microsoft Excel** — All Excel interaction uses late binding via `CreateObject("Excel.Application")`, so no VBA reference to the Excel library is required. The module works with `.xlsx` files.
- **DAO** — All database access uses `DAO.Database` and `DAO.Recordset` objects through `CurrentDb()`.
- **Form: `frmImport`** — The import module reads and writes controls on this form (`txtFilePath`, `lblStatus`). The module cannot run outside of the Access host application.

### Internal Module Relationships

- `modImport` depends on `clsTransaction` (validation) and `modUtil` (batch numbering, SQL formatting, error logging).
- `modReconcile` depends on `modUtil` (SQL formatting) and reads the data populated by `modImport`.
- `modExport` depends on `modUtil` (SQL formatting) and reads data populated by both `modImport` and `modReconcile`.
- `modUtil` is the leaf dependency — it has no internal VBA dependencies.

## Notes for Developers

### Implicit state via `gCurrentBatchID`

The `modUtil.gCurrentBatchID` public variable is set at the end of `RunImport` and read by `RunReconcile` and `RunExport` when no explicit batch ID is passed. This means:

- The pipeline phases must run in order for the same data set.
- If two users run imports simultaneously, the variable is overwritten — there is no locking or session isolation. This module is designed for single-user use within a shared Access backend.

### SQL injection risk

All SQL is constructed via string concatenation. While `modUtil` provides `FormatDateForSql` and `FormatNumberForSql` for locale-safe formatting, and single quotes are doubled for string escaping, the module is vulnerable to SQL injection if `RefNo` or `FileName` values contain crafted SQL payloads. For production hardening, consider switching to DAO parameter queries or an ADO command object with parameters.

### Locale-safe number formatting

`modUtil.FormatNumberForSql` is a critical helper. Access's `Str()` function always uses `.` as the decimal separator regardless of the Windows regional settings. This matters because `&` concatenation and `CStr` use the locale's decimal separator, which would break SQL on machines with non-US locales. Do not replace `Str()` with `CStr` or `Format(n, "Fixed")` without re-testing this behavior.

### File-lock retry logic

`RunImport` retries Excel opening up to 3 times with a 2-second sleep on error 70 (file locked by another process). This handles the common case where the file is open in another Excel instance. The `Sleep` API is declared with `PtrSafe` for 64-bit compatibility.

### Bank statement branch handling

When importing bank statement files, the `Branch` column (column A) is intentionally blank. The code detects this by checking the filename for "BankStatement" and hardcodes `Branch = "BANK"` for all such rows. It also deliberately reads the last-row indicator from column B (`TxnDate`) instead of column A to avoid `lastRow` being 1.

### Export report naming

Exported reports are saved as `ReconcileReport_yyyymm.xlsx` in the Access project directory (`CurrentProject.Path`). If multiple batches are exported in the same month, later exports overwrite earlier ones — there is no day-level granularity in the filename.

### Duplicate handling

If a `RefNo` appears multiple times within the same batch, all occurrences are flagged as `"Duplicate"` without attempting any matching. Only one unique transaction per `RefNo` can be matched in a given batch.

### Date range for validity

`clsTransaction.IsValid()` rejects any transaction dated before January 1, 2000. This hard-coded cutoff may need adjustment if historical data import becomes necessary.