Runtime Error 16: “Expression too complex,” explained and fixed

Error 16, “Expression too complex”, means one statement asked VBA's expression evaluator to juggle more than it can hold: deeply nested function calls, a formula with dozens of terms, or parentheses stacked far beyond readability. The math is usually fine; the shape of the expression is the problem. This is purely a code bug, and the fix is always to break the expression into named steps.

For people who just run the program

  1. Expect this in generated or recorded code. Macros assembled by other tools, or formulas translated mechanically from a spreadsheet, are where monster expressions live.
  2. Note which macro and step. The error appears when that one statement runs, so the failing action identifies the statement.
  3. Do not change the data. Smaller data will not help; the expression is too complex before any values are even plugged in.
  4. Pass the error to whoever maintains the macro. It needs restructuring, and no setting or reinstall changes it.

For people who write the code

  1. Split the expression into intermediate variables. Each named step is compiled on its own, and the final line combines a handful of values instead of fifty operations.
    2. ' Too complex as one statement:
' total = (a + b) * (c - d) / (e + f) * (g - h) / (i + j) * ...
Dim leftPart As Double, rightPart As Double
leftPart = (a + b) * (c - d) / (e + f)
rightPart = (g - h) / (i + j)
total = leftPart * rightPart
  2. Move repeated sub-expressions into a helper function. A pattern like IIf(...) nested inside itself ten deep is a function waiting to be written.
  3. Convert giant literal formulas into loops over data. A sum written out as 200 cell references in one line should be a loop or a SUM over a range; both are simpler for the evaluator and for the next reader.
Adding parentheses to “clarify” evaluation order makes error 16 worse, not better. Every nesting level costs evaluator capacity. Flatten the expression instead of armoring it.

If it still happens

If a modest expression triggers error 16, suspect a recursive user-defined function being evaluated inside it: the expression looks shallow, but the evaluator is also holding the recursion. Test the same formula with the UDF replaced by a literal value to confirm.

Common questions

Is there a documented complexity limit? Microsoft does not publish an exact count. The limit is reached through combined nesting of operations, calls, and parentheses, which is why splitting the statement into steps reliably clears it.

Why did the expression work in the spreadsheet but not in VBA? The worksheet formula engine and the VBA evaluator are different code with different limits. A formula Excel accepts can still be too complex when pasted into a VBA statement.

Related errors

Runtime Error 28 · Runtime Error 14 · Runtime Error 6