Blog › ICP guides

Visual Basic developer on retainer: VBA function scope, On Error Resume Next, integer overflow, and VB6 on monthly retainer

October 2, 2026 · ~13 min read

A VBA developer was writing an Excel automation macro for a mid-size manufacturing company to calculate monthly production bonuses. The macro iterated over a worksheet, reading each production worker’s unit count and applying a tiered bonus formula. For small production runs in testing, the output was correct. When run against the full 247-row dataset for a month where several workers had high unit counts, 3 of the bonus calculations produced wrong values — one of them negative.

The loop was structured as:

For Each cell In ws.Range("B2:B248")
  Dim units As Integer
  Dim bonus As Integer
  units = cell.Value
  If units >= 500 Then
    bonus = units * 3
  ElseIf units >= 250 Then
    bonus = units * 2
  Else
    bonus = units
  End If
  ws.Cells(cell.Row, 5).Value = bonus
Next cell

There were two independent bugs. First: Dim bonus As Integer inside the loop body. In VBA, all Dim declarations are function-scoped regardless of where they appear in the function body — inside a loop, inside an If block, at the top of the function; it makes no difference. The Dim allocates storage once at the function call, not once per loop iteration. bonus was the same variable across all 247 iterations. It retained its value from the previous iteration because there was no explicit reset. The variable was never reset to zero at the top of the loop body. However, for most rows, the If block assigned a new value to bonus anyway — overwriting the previous value — so this bug only manifested if an iteration somehow produced a wrong value that was not overwritten. The first bug alone was masked.

Second: bonus As Integer. VBA’s Integer type is 16 bits, with a maximum value of 32,767. Three production workers had unit counts above 10,900. For a worker with 10,950 units at the tier-3 rate of 3×, the correct bonus is 32,850 — which exceeds the 16-bit Integer maximum of 32,767. VBA overflows silently: the value wraps to a negative number. The wrong bonus for the highest-unit worker was −32,686. Wrong bonus calculations: 3. Changing bonus As Integer to bonus As Long fixed the overflow; adding bonus = 0 before the If block fixed the accumulation. Wrong bonus calculations: 3 → 0. The work log said “fixed bonus calculation macro for high-unit workers, 5h.” What is invisible is that VBA’s Integer is 16-bit (a legacy from the 16-bit Windows era when Integer matched the processor register width), that Dim inside a loop body is not loop-scoped in VBA, and that both behaviors produce wrong values without any runtime error.

Visual Basic overview: from VB1 in 1991 to VB6 in 1998 and beyond

Visual Basic was introduced by Microsoft in 1991 as a rapid application development tool for Windows. Its drag-and-drop form designer and event-driven programming model allowed developers to build Windows GUI applications without writing Windows API code directly. VB3 (1993) added data access controls. VB4 (1995) introduced 32-bit compilation and classes. VB5 (1997) added compiled native code output. VB6 (1998) is the last version before Microsoft replaced the language with VB.NET (2002), which was a substantially different language with a different runtime. VB6 is still in production at thousands of enterprises that cannot or have not migrated.

VBA (Visual Basic for Applications) is the macro language embedded in Microsoft Office since Office 97. VBA shares VB6’s syntax and runtime but runs inside the Office host application rather than as a standalone executable. Excel VBA automates spreadsheet manipulation; Access VBA automates database operations; Word VBA automates document processing; Outlook VBA automates email and calendar operations. VBA macros in production today range from simple cell-formula automation scripts written in the 1990s to complex enterprise reporting pipelines with tens of thousands of lines that no one has touched in a decade.

VB6 and VBA retainer work today covers: maintaining VB6 Windows applications on Windows 10 and 11 (COM registration, 32-bit/64-bit compatibility, Windows API Declare statement migration); Excel VBA automation maintenance (calculation macros, report generators, data import pipelines); Access VBA database automation (query automation, form event handling, report generation); and Outlook VBA integration (automated email processing, calendar event creation, attachment handling). The organizations running VB6 applications are typically mid-size businesses in manufacturing, finance, insurance, and distribution that built their operational software in the 1990s and have not had the budget or urgency to migrate.

VBA function scope: all Dim declarations are hoisted

VBA’s scoping model has one rule that surprises almost every developer from a modern language background: all Dim declarations in a function or sub are function-scoped, regardless of where in the body they appear. Writing Dim x As Integer inside a For loop, inside an If block, or at the bottom of a function body produces the same result as writing it at the top: one variable allocation for the duration of the function call. There is no block scope in VBA.

This means a variable declared inside a loop is not reset to its default value (zero for numeric types, empty string for String) on each iteration. If the loop body does not explicitly assign a value to the variable on every iteration — for example, if the assignment is inside a conditional branch that may not execute — the variable retains the value from the previous iteration. The classic pattern: a conditional computation that sets a variable in some branches but not others produces accumulation bugs where the variable carries forward the value from the last iteration that set it.

The fix is always the same: move Dim declarations to the top of the function (where they always belong semantically, since VBA ignores their physical location anyway) and add explicit initialization at the top of the loop body or in every branch that uses the variable. The Option Explicit statement at the top of a module forces declaration of all variables and prevents undeclared variable use, but it does not change the scoping model — all Dims remain function-scoped.

The diagnostic: add Debug.Print variableName (output to the Immediate window in the VBA editor) at the top of the loop body, before any assignment. If the variable holds a value from the previous iteration when it should be fresh, the accumulation is confirmed. Check whether every code path through the loop body assigns a value to the variable. If not, add an explicit reset.

VBA Integer overflow and the 16-bit type system

VBA inherits its numeric type sizes from the 16-bit Windows era: Integer is 16 bits (range −32,768 to 32,767); Long is 32 bits (range −2,147,483,648 to 2,147,483,647); Currency is a 64-bit fixed-decimal type designed for financial calculations (range −922,337,203,685,477.5808 to 922,337,203,685,477.5807 with 4 decimal places); Double is a 64-bit IEEE floating-point number.

The practical implication: any calculation involving unit counts or prices that might exceed 32,767 must use Long or Currency, not Integer. A unit price of 400 multiplied by a quantity of 100 is 40,000 — which overflows a 16-bit Integer silently in VBA. The overflow wraps to a wrong value without raising an error. For financial applications, the correct types are: Long for counts and integer IDs; Currency for monetary amounts (avoids floating-point rounding errors that affect Double calculations); Double for rates and percentages.

VB6 and early VBA code written in the 1990s often used Integer everywhere because 16-bit integers were the natural unit-of-work on 16-bit Windows. This code worked correctly when processing small datasets but fails when run against modern data volumes. A manufacturing application that processed order quantities up to 200 units in 1998 may now process quantities above 50,000 units, silently overflowing every calculation. The fix is changing type declarations from Integer to Long. The diagnosis requires running the calculation at boundary values and comparing the output to the expected result.

On Error Resume Next, early vs late binding, and VB6 COM compatibility

On Error Resume Next is a VBA error handling mode that causes execution to continue at the statement immediately following any statement that raises an error. It is the most common source of silent failures in VBA code. A developer who writes On Error Resume Next at the top of a macro and never adds explicit error checking has written a macro that will silently proceed through any failure — a failed file open, a failed database query, a failed COM object creation — and continue executing on wrong or uninitialized state.

The correct use of On Error Resume Next is scoped to the specific statement that is expected to fail under known conditions, with an immediate check of Err.Number afterward: On Error Resume Next / Set wb = Workbooks.Open(path) / If Err.Number <> 0 Then /   ' handle error / End If / On Error GoTo 0. The On Error GoTo 0 restores normal error handling after the scoped block. Using On Error Resume Next at function scope without On Error GoTo 0 suppresses all errors for the entire function.

Early binding (e.g., Dim xl As Excel.Application) requires a reference to the type library set in the VBA editor’s References dialog. The variable has IntelliSense support, compile-time type checking, and faster execution. Late binding (e.g., Dim xl As Object) requires no reference; the type is resolved at runtime. Late binding is used for automation code that must work across multiple Office versions where the type library version differs. Late binding defers all type errors to runtime; early binding catches them at compile time. A macro that works in one environment and fails with “Method or data member not found” in another often has a type library version mismatch fixable by switching between early and late binding.

VB6 COM compatibility on modern Windows: VB6 applications use COM for UI components (forms, controls), data access (ADO), and inter-application communication. COM components are registered in the Windows registry; each registration is version-specific. A VB6 application that ran on Windows 7 may fail to start on Windows 10 or 11 if a COM component is not registered, or if the component’s version does not match what the application expects. Registration is performed with regsvr32.exe for in-process DLL components; for out-of-process EXE servers, the server registers itself on first launch. Common failure: a Windows update unregisters or overwrites an ActiveX control that the VB6 application depends on. Diagnosis: run the VB6 application, note the component name from the error, and re-register it.

Typical VB6 and VBA retainer work and what it looks like in a work log

Function-scope Dim accumulation is the largest category of VBA retainer work that produces no visible artifact at small data sizes. A macro that processes rows individually, assigning to a Dim-in-loop variable in every branch for the small test datasets, produces correct output until the data reaches a size where a branch is skipped in some iteration, leaving the variable with its prior value. The work log says “fixed bonus calculation for high-unit workers, 5h.” What is invisible is the VBA function-scope hoisting rule, the Integer overflow at values above 32,767, and the interaction between the two bugs. Work log entry: “CalculateMonthlyBonuses: Dim bonus As Integer inside loop body accumulated across iterations (VBA hoists to function scope); Integer overflowed at unit counts above 10,900 (3× = 32,850 > 32,767 max Integer; wrapped to negative); wrong bonuses: 3; fix: moved Dim outside loop; added bonus = 0 reset; changed type to Long; wrong calculations before: 3; after: 0; 5h.”

On Error Resume Next diagnosis is the second category. A macro that processes 50 rows silently fails on rows 12 and 31 because a Workbook.Open call fails for locked files; On Error Resume Next swallows the error; the macro writes wrong placeholder values to those rows. The output report has wrong data for those rows but no error message. Work log entry: “Monthly import macro: rows 12 and 31 showed wrong values; On Error Resume Next at function scope swallowed Workbooks.Open failure for locked files; macro continued with wb = Nothing; downstream range access returned empty values; fix: scoped On Error Resume Next to Workbooks.Open call; added If Err.Number <> 0 Then check with skip-and-log; added On Error GoTo 0 after; wrong rows before: 2; after: 0; 4h.”

How HourTab tracks Visual Basic developer retainer hours

VBA retainer work is invisible by the same mechanism that makes VBA approachable: the language does not surface scope distinctions between loop-body Dim and function-top Dim in its syntax, and it does not surface numeric overflow in its output (the overflowed value is a plausible-looking number, not an error). The wrong output can persist for years before data volumes grow large enough to trigger the overflow. The fix is changing one type keyword and adding one line. The diagnosis is 5 hours.

The work log needs to name the mechanism: which Dim was misplaced, what the type maximum was, how the overflow manifested, and the concrete before-and-after wrong-calculation count. A log entry that says “fixed bonus macro for large datasets, 5h” is not auditable. A log entry that names the hoisting rule, the Integer maximum, the overflow value, and the row counts is auditable.

HourTab gives Visual Basic developers a public retainer-hours URL they send to clients — manufacturing companies with VB6-based production tracking software, financial services firms with Excel VBA reporting pipelines, distributors with Access VBA order management macros, and insurance companies with VB6 claims processing applications. For VB6 and VBA retainers, each work log entry should name the VBA mechanism: which Dim was hoisted, which type overflowed at which value, which On Error scope swallowed which error. Comparative context: VB6 and VBA retainer work has conceptual overlap with other BASIC-lineage languages — HyperTalk (handler-scope variable model with explicit global declarations); Delphi/Object Pascal (similar enterprise Windows application maintenance retainer patterns); and PowerShell (which handles Windows enterprise automation with a modern language but requires similar function-scope variable management discipline). VB6 is uniquely positioned as the environment for the specific class of 1990s Windows desktop applications that are in production and cannot be easily replaced.

FAQ: Visual Basic developer retainers