Blog › ICP guides

VBA developer on retainer: Excel automation, Access VBA, and COM automation on monthly retainer

September 25, 2026 · ~20 min read

A finance team at a mid-size distribution company produced a monthly margin report by hand. The process involved opening seven workbooks, copying ranges from each into a master sheet, running a VLOOKUP pass to append product categories, manually deleting duplicate transaction rows identified by scrolling and visual inspection, refreshing four pivot tables, and pasting the pivot output as values into a summary tab before emailing it. Start to finish, the process took three hours each month and was owned by a single analyst who ran it on a Friday afternoon using a documented but error-prone procedure. When the analyst went on leave, her backup took five hours and still produced a version with seventeen duplicate rows that the controller caught the following Monday.

A VBA developer on monthly retainer rebuilt the report as a single macro that completed in twelve minutes without manual intervention. The recorded-macro starting point — a 700-line module generated by the macro recorder — was refactored into a proper Sub/Function architecture: a top-level Sub RunMonthlyMarginReport() orchestrated calls to Function OpenSourceWorkbooks() As Workbook() for file collection, Sub ConsolidateToMaster(wbArray() As Workbook) for range copying, and Function DeduplicateByTransactionID(ws As Worksheet) As Long for deduplication. The deduplication function replaced the manual scroll-and-delete process with a Scripting.Dictionary: iterating the transaction ID column with a For i = lastRow To 2 Step -1 loop, calling dict.Exists(transactionID) on each value, deleting the row if the key was already present and calling dict.Add transactionID, i if it was not. Walking the range backwards ensured that row deletions did not shift unprocessed rows out of the loop index. The macro also wrapped every file operation in a structured On Error GoTo Cleanup block so that a missing source workbook logged the error via Err.Description and exited cleanly rather than leaving six workbooks open and the master sheet in a partially written state. A VBA developer on monthly retainer does this category of work continuously: refactoring recorded macros into maintainable Sub/Function architectures before they become undebuggable, replacing manual deduplication with Scripting.Dictionary lookups before data quality incidents accumulate, and maintaining ADODB connection helpers before SQL string concatenation produces a security incident.

VBA language fundamentals, error handling, and collections

VBA organizes executable code into procedures: Sub for routines that perform actions without returning a value; Function for routines that return a value via the function name; Property Let, Property Get, and Property Set for class module property accessors — Property Let handles value assignment to a property backed by a private variable, Property Get exposes the value, and Property Set handles object reference assignment where Set obj = value semantics are required. Breaking a large recorded macro into named Sub and Function procedures is the first refactor a VBA developer on retainer performs because it enables independent testing, meaningful error location identification, and reuse across multiple workbooks. A Function that returns the last populated row — Function LastRow(ws As Worksheet, col As Long) As Long: LastRow = ws.Cells(ws.Rows.Count, col).End(xlUp).Row: End Function — is called dozens of times across a module and eliminates the recurring pattern of hard-coded row counts that break when source data grows.

The With...End With block eliminates repeated object qualification and reduces execution overhead when accessing multiple properties or methods of the same object: instead of writing ws.Range("A1").Font.Bold = True, ws.Range("A1").Font.Size = 12, ws.Range("A1").Interior.Color = vbYellow three times, a With ws.Range("A1").Font block qualifies the Font object once and accesses .Bold, .Size, and .Color without re-traversing the object hierarchy. In tight loops operating on ranges, this difference is measurable. VBA's error handling model uses On Error GoTo LabelName to redirect execution to a labeled cleanup block on any runtime error; the Err object captures the error state: Err.Number is the VBA error code (e.g., 1004 for application-defined errors, 9 for subscript out of range), Err.Description is the human-readable message, and Err.Source identifies the object or application that raised the error. Calling Err.Clear explicitly resets the Err object state; failing to call it means a subsequent On Error Resume Next block may inspect stale error state from a previous unhandled error rather than the current operation's result. A correctly structured procedure uses On Error GoTo Cleanup at the top, a Cleanup: label near the bottom that closes open workbooks, releases object references, and restores Application.ScreenUpdating and Application.Calculation, followed by Exit Sub before the label and error logging logic after it.

Scripting.Dictionary — from the Microsoft Scripting Runtime, declared as Dim dict As New Scripting.Dictionary with a reference added via Tools > References — provides O(1) key existence checks and value retrieval, replacing nested loops that perform the same operation in O(n) per lookup. The dict.Exists(key) method returns True if the key is present; dict.Add key, value inserts a new entry; dict.Item(key) retrieves a value; dict.Keys() and dict.Items() return arrays of all keys and values respectively. For deduplication, the dictionary key is the unique identifier column value; for frequency counting, the value is a running count incremented on each encounter. ReDim Preserve arr(0 To newSize) grows a dynamic array without clearing its contents — the Preserve keyword is required to retain existing elements; without it, ReDim reinitializes the array to empty. VBA's string functions handle the text parsing that financial data imports frequently require: Split(str, delimiter) returns a zero-based array of substrings; Join(arr, delimiter) reassembles them; InStr(start, str, substr) returns the position of a substring or zero if not found; Mid(str, start, length), Left(str, length), and Right(str, length) extract substrings; Trim(str) strips leading and trailing spaces; Replace(str, find, replacement) substitutes all occurrences. Type inspection before operations that would fail on unexpected types uses IsNumeric(v), IsDate(v), IsNull(v), IsEmpty(v), IsArray(v), IsObject(v), and TypeOf obj Is ClassName — particularly important when processing ranges where cells may contain numbers, dates, text, errors, or empty values in unpredictable combinations.

Excel VBA: workbook and sheet automation, pivot tables, and performance

Excel VBA's object model starts at the Application object and descends through Workbooks, Worksheets, Range, and Cell. Workbooks.Open(Filename) opens a workbook and returns a Workbook reference; storing the reference — Dim wb As Workbook: Set wb = Workbooks.Open(path) — is essential because relying on ActiveWorkbook inside a multi-workbook macro is a common source of data being written to the wrong file. wb.Save saves in place; wb.SaveAs(Filename, FileFormat) saves to a new path with an explicit format constant (e.g., xlOpenXMLWorkbook for .xlsx, xlCSV for comma-separated); wb.Close(SaveChanges:=False) closes without prompting. Addressing cells uses ws.Range("A1:D100") for named addresses, ws.Cells(row, col) for numeric row/column coordinates useful in loops, and ws.UsedRange for the bounding rectangle of all non-empty cells — though UsedRange can extend beyond actual data if cells were previously formatted or populated. The idiom ws.Cells(ws.Rows.Count, 1).End(xlUp).Row finds the last populated row in column A reliably regardless of gaps.

AutoFilter and Sort are the two in-sheet data operations most frequently automated on retainer. ws.Range("A1:F1").AutoFilter Field:=3, Criteria1:="Active" applies a filter on the third column; ws.AutoFilterMode = False removes all filters. The Sort method on a Range takes Key1, Order1, Header, and optional secondary keys: ws.UsedRange.Sort Key1:=ws.Range("C1"), Order1:=xlAscending, Header:=xlYes. Pivot tables accumulate stale cache across monthly report runs; ws.PivotTables("SummaryPivot").RefreshTable forces a cache update from the source data range, and iterating all pivot tables with For Each pt In ws.PivotTables: pt.RefreshTable: Next pt ensures no pivot is left showing last month's figures. Named Ranges — created via wb.Names.Add Name:="MonthlyRates", RefersTo:=ws.Range("C2:C50") — allow macros to reference logical data regions by name rather than by address, surviving column insertions that would break hard-coded Range("C2:C50") references.

Performance in Excel VBA has two levers that together account for most of the difference between a 22-minute macro and a 40-second one. Setting Application.ScreenUpdating = False before a loop suppresses screen redraws; Excel does not repaint the worksheet grid on every cell write, eliminating the visual flicker that slows execution dramatically when writing thousands of cells. Setting Application.Calculation = xlCalculationManual prevents Excel from recalculating formula-dependent cells after each write — critical when the target sheet contains SUMIF or VLOOKUP formulas that would otherwise recalculate 8,000 times during an 8,000-row write pass. Both must be restored at the end of the Sub and in the error cleanup block: Application.ScreenUpdating = True and Application.Calculation = xlCalculationAutomatic. DoEvents yields processor control to the Windows message queue mid-loop, allowing the user to interact with the UI or cancel a long-running macro; it is placed inside loops expected to run longer than 2–3 seconds and is the mechanism behind a progress indicator that actually updates. PasteSpecial xlPasteValues is the correct paste method when writing formula output to a summary tab — pasting with xlPasteAll copies the source formula with relative references, which produces wrong results in the destination; xlPasteValues writes the computed value only, making the output self-contained regardless of where the source data lives.

Access VBA and COM automation with ADODB

Access VBA shares the core language with Excel VBA but operates against a different host object model centered on forms, reports, queries, and the database engine. DoCmd is the primary action dispatcher: DoCmd.OpenForm "CustomerDetails", acNormal, , "CustomerID = " & Me.CustomerID opens a form filtered to the current record; DoCmd.RunSQL "UPDATE Orders SET Status = 'Closed' WHERE OrderDate < #" & Format(cutoff, "mm/dd/yyyy") & "#" executes a DML statement directly; DoCmd.OpenQuery "qryMonthlyRollup", acViewNormal opens a saved query. For programmatic data access without opening UI objects, CurrentDb.Execute sql, dbFailOnError runs DDL and DML with the dbFailOnError flag causing Access to raise a VBA error on failure rather than silently succeeding with partial writes. DAO Recordsets opened via CurrentDb.OpenRecordset("qryActiveCustomers", dbOpenSnapshot) provide forward-only cursor traversal: rs.FindFirst "Region = 'West'" positions to the first matching record; rs.MoveNext advances; rs.EOF is True when the cursor is past the last record; the standard traversal idiom Do While Not rs.EOF: ... rs.MoveNext: Loop iterates all rows. Form event procedures — Private Sub Form_Open(Cancel As Integer) for initialization logic when a form loads, Private Sub Form_Current() for per-record logic that fires each time the current record changes — are where most Access VBA retainer work executes: filtering combo box row sources, enabling and disabling controls based on record state, and computing running totals visible to the user.

COM automation extends VBA beyond its host application to control other Office applications and connect to external data sources. From Access VBA or a standalone VBA host, CreateObject("Excel.Application") starts a new Excel process and returns an Excel Application reference with the full Excel object model available via late binding — no compile-time type checking, but no reference dependency that could break on a different Office version. GetObject(, "Excel.Application") attaches to an already-running Excel instance rather than starting a new one, preventing the proliferation of orphaned Excel processes when automation code runs repeatedly. Early binding — adding a reference to the Microsoft Excel Object Library via Tools > References and declaring Dim xlApp As Excel.Application — provides IntelliSense, compile-time method resolution, and faster execution than late binding's runtime dispatch, at the cost of a version-specific library reference that requires updating when the Office version changes. For automation code that runs in controlled environments, early binding is the correct choice; for code distributed to clients running mixed Office versions, late binding with As Object declarations is safer.

ADODB provides database connectivity independent of the host application. Dim conn As New ADODB.Connection: conn.Open "Provider=SQLOLEDB;Data Source=dbserver;Initial Catalog=FinanceDB;Integrated Security=SSPI" opens a SQL Server connection using Windows authentication. Parameterized queries via ADODB.Command replace string concatenation for all user-supplied values: Dim cmd As New ADODB.Command: Set cmd.ActiveConnection = conn: cmd.CommandText = "SELECT * FROM Orders WHERE CustomerID = ? AND OrderDate >= ?" followed by cmd.Parameters.Append cmd.CreateParameter("@custID", adInteger, adParamInput, , custID) and cmd.Parameters.Append cmd.CreateParameter("@startDate", adDate, adParamInput, , startDate) binds values to positional placeholders, eliminating the apostrophe injection risk present in string-concatenated SQL. Dim rs As ADODB.Recordset: Set rs = cmd.Execute returns the result; column values are accessed via rs.Fields("ColumnName").Value or the ordinal shorthand rs.Fields(0).Value. A disconnected Recordset — populated, then set to rs.ActiveConnection = Nothing — allows the connection to be closed and the data manipulated in memory independently, useful when processing a large result set that must survive a connection drop or be passed between procedures without keeping the database connection open.

How HourTab tracks VBA developer retainer hours

VBA developer retainers — particularly in Excel automation and Access database contexts — produce some of the highest work-to-deliverable ratios of any language retainer. The session that rebuilt the monthly margin report produced twelve new procedures across three modules and a single Scripting.Dictionary deduplication function replacing a manually-operated scroll-and-delete process. The session involved profiling the original recorded macro to identify the three longest-running sections, mapping the 700-line module into logical units that could be independently tested, designing the Sub/Function call graph to minimize shared mutable state, implementing the Dictionary-based deduplication with backward-loop row deletion, wrapping every workbook open and close in On Error GoTo Cleanup blocks that populated an error log range before exiting, adding Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual guards with guaranteed restoration in each cleanup path, and running the macro against six months of historical source files to verify idempotent output. The log entry “rebuilt margin report macro, 8h” gives the client no path from eight hours to the three-hour manual process that now completes in twelve minutes — because nothing in a 12-procedure module communicates the profiling, architecture design, and systematic error handling that turned a fragile recorded macro into a reliable monthly automation.

HourTab gives VBA developers a public retainer-hours URL they share with each client at the start of the engagement. The client opens the URL and sees the current burn-down without logging in. For Excel and Access retainers specifically, the work log format carries the weight: each entry should name the module and procedure changed with the runtime impact (DeduplicateByTransactionID — Scripting.Dictionary replaced CountIf inner loop; 40,000-row deduplication: 18min → 6sec; O(n²) → O(n); backward-iteration row deletion verified against 6-month historical dataset), the performance block added with before/after timing (ScreenUpdating=False + xlCalculationManual block added to ConsolidateToMaster Sub; 8,000-cell write loop: 22min → 40sec; Application.Calculation restored in Cleanup label on all exit paths including error), and the ADODB parameterization change with security context (ADODB.Command Parameters.Append replaced string concatenation in GetOrdersByCustomer; 2 parameters bound — @custID adInteger, @startDate adDate; apostrophe injection risk eliminated; Err.Number 3709 connection check added before cmd.Execute). That entry takes five minutes to write and turns the client’s next check-in from a thirty-minute explanation of what SQL injection means in an Excel macro context into a two-sentence acknowledgment that the report runs in twelve minutes and the security exposure is closed.

The retainer model fits VBA automation engineering because the macro landscape in any established organization evolves continuously as source data formats change, user requirements grow, and new workbooks are added to the monthly report chain. A deduplication routine that runs in six seconds today handles 40,000 rows; it will run in eighteen minutes again when the source system adds a second transaction feed and the row count reaches 180,000. An ADODB connection helper that works reliably against SQL Server 2019 may raise Err.Number 3706 when the database team migrates to a named instance with a different connection string format. An Access form that initializes correctly in Form_Open stops working when a lookup table is renamed and the combo box RowSource query silently returns no records. A project contract closes when the current macro is rebuilt and tested. A VBA retainer stays open for the next recorded macro a non-developer analyst writes and checks in to SharePoint, the next data volume increase that pushes a Dictionary-less loop past the acceptable runtime threshold, and the next Office version upgrade that changes a COM object reference and causes a 438 “object doesn’t support this property or method” error in a macro that ran without issues for four years.

FAQ: VBA developer retainers