Inherited workbooks and On Error Resume Next

Never written a line of VBA, but oh no, that parenthesis is the scary part. The macro stops doing whatever the failing line was for, nothing crashes, and so nobody looks. Whatever that line was supposed to handle just falls out of the totals, and those are exactly the rows you most needed to see. Python has the same trap in an except that does continue, and it reads like a filter there too.

Wrapping only the one risky call sounds like the right size, as long as the Err.Number check does something visible when it fires instead of quietly moving on. Do people ever log the skipped rows in VBA, or is that considered too fancy for a workbook?

It is partly a language problem. When silencing an error takes one line and checking it takes five, people silence it. VBA lets one line at the top of a module silence everything, while the careful version is at least four lines around each risky call, so of course the careful version loses. TypeScript has the same habit with better manners. An as Invoice cast on parsed JSON stops the complaints exactly the way the resume line does, and the data is still whatever came in.

The check belongs where the data enters. Validate the sheet lookup and the input cells once, at the edge, and suppress nothing after that.

Ruby's version is value = parse(row) rescue nil, for people who find even the cast too much work. The nasty part is that a nil from a failure you cared about looks exactly like the harmless kind.

The workbook version at least lives in one file. The try { load() } catch {} I keep running into in JavaScript is usually three packages down, in something nobody on the project has opened.

Tried the unexciting fix on one of those inherited workbooks, and it found less than I'd hoped. Added Option Explicit at the top of the module and ran Compile from the Debug menu. It stopped twice. Both were loop counters somebody never bothered to Dim, harmless (mildly disappointing, I'd promised myself a misspelled total).

The totals were still short a column. The real culprit was a string. Further down, under the module-wide On Error Resume Next, the summary step did this.

Set ws = ThisWorkbook.Worksheets("Sumary")
ws.Range("B2").Value = runningTotal

The sheet is called Summary. Worksheets("Sumary") raises error 9, subscript out of range, the resume line swallows it, ws stays Nothing, the next line fails and is swallowed too, and the macro carries on as if the summary had been written. Option Explicit can't see any of that, because a sheet name in quotes is just text to the compiler.

So the declaration check earned its keep on variables and did nothing for names inside strings. What fixed this one was the second half of the advice. I took the module-wide resume line out, wrapped only the lookup, checked Err.Number straight after it, and raised a proper error naming the missing sheet.

On the logging question, I do log the skips now. The one wrapped call writes the row number and Err.Description to a sheet before Err.Clear. Too fancy for a workbook? Possibly. It's one extra sheet, and the next person gets to see what was skipped, which is more than anyone left me.

The placement of that check carries the whole fix, so one detail about it. Under On Error Resume Next, a statement that succeeds does not reset Err.Number. The value stays until Err.Clear runs or until a Resume, an Exit Sub or any On Error statement clears it implicitly. The check therefore says which line failed only when it sits directly after the line it is judging, as yours does. Move it two lines further down and it reports that something in between failed, without saying what.

C has the same property with errno, which no library function ever sets to zero. It is meaningful only right after a call that has reported failure by its return value.

If that lookup ever moves into a loop over sheet names, ws Is Nothing stops being a safe test. A failed Set assigns nothing, so ws keeps the sheet from the previous iteration, and the next line writes into the last sheet that did exist. The fix is one line, Set ws = Nothing before each lookup, and then the variable holds either this lookup's result or nothing at all. Your Err.Number check does not have the problem. Someone tidying the code later may still swap it for Is Nothing because it reads better.

Ha, that loop does exist (two modules over, naturally), and it already sets ws to Nothing first, which I'm fairly sure was luck rather than foresight.

On the names-in-strings problem I found a partial answer. Sheets in the workbook that holds the code have a code name, the (Name) property in the Properties window, and the code can use it as an object directly. Sheet3.Range("B2") is checked when you compile, so with Option Explicit a typo like Shet3 stops Compile with Variable not defined. Renaming the tab doesn't break it either, since the code name stays put.

The catch is that this only works for sheets in ThisWorkbook. The macros that open somebody else's file and go looking for a sheet called Summary are still stuck with strings, so those keep the wrapped lookup and the log sheet.