Error 5 — “Invalid procedure call or argument” — means the code called a function with a value that function cannot accept: the square root of a negative number, a string operation starting before the string does, a file path the Shell can't parse. The call was well-formed; one of its inputs was impossible.
Mid(text, start) with start ≤ 0, or Left/Right with a negative length, often because a Len() or InStr() returned 0 for text that didn't contain what was expected.Sqr(-1), Log of zero or a negative, division results fed onward unchecked.InStr returns 0 when the substring isn't there; arithmetic built on 0 produces invalid positions downstream. This is the leading cause in string-parsing macros.DateSerial or date arithmetic pushed outside VBA's valid date span by bad input.pos = InStr(fullText, ",") If pos > 0 Then surname = Left(fullText, pos - 1)Never let an
InStr result flow into Mid/Left unchecked.Len(), sign, and range on values coming from cells, files, or users before the call — If x >= 0 Then root = Sqr(x).Debug.Print).If the line looks innocent, the bad value arrived from further away — a function upstream returned 0, -1, or an empty string and nobody checked. Put a breakpoint where the value is created and watch it flow forward. In legacy compiled apps hit by error 5, the equivalent story is bad input data or settings; a clean settings reset or the specific problem record is the fix, and the vendor can use the exact steps that trigger it.
What's the difference between error 5 and error 13? 13 is the wrong kind of data (text as a number). 5 is the right kind of data with an impossible value (a number, but negative where negatives can't work).
Why does it only fail on some records? Because only some records produce the impossible argument — the entry without the separator, the refund that makes a total negative, the empty field. Data-dependent errors are input validation gaps.