Error handling in VBA is a means to trap errors and effectively correct the problem – or stating that the problem exists and moving on in a safe manner. Many people writing VBA code (including myself in the earlier years) think that using the statement “On Error Resume Next” is a fix. It is not, and it can do harm to any subsequent code.
The use of this “fix” is frequent when uninformed programmers run into VBA’s ubiquitous error 1004. This error code can stem from many problems and takes some detective work to fix. Lazy coders, after hours of searching for the problem, often insert the “On Error Resume Next” statement. I know that I have. They think that if they cannot find any resulting problems caused by this error it can simply be ignored. However it leaves some (or all) of the problem code uninitiated and therefore worthless – variables and objects are not set or commands not issued. Sometimes all seems well until that one circumstance, and it breaks things.
The following code caused me a few hours of work troubleshooting. Actually it took over several days, since programming is not currently my full-time job. The VBA runtime execution stopped at line one, so there I thought might be the problem. As far as the VBA compiler was concerned it was fine. So that meant all the variables were defined and the syntax was acceptable to the compiler at the least. Still, I looked for misspellings, bad syntax or issues with the variable’s content. I tried using the full object names for the heck of it. I retraced the code from where it was first called. Finally, I thought that it might be the worksheet protection. I unprotected the worksheet and the error disappeared. So to run this bit of code, which is part of a larger sub procedure, I have to unprotect the worksheet and then reprotect it when the sub is finished. That was the solution.
If .Range("J" & currRow).Formula <> "=SUM(F" & currRow & "+I" & currRow & "+G" & currRow & ")" Then If MsgBox("The 'Cost' cell (line item total cost), J" & currRow & ", contains an Incorrect or Missing formula. " _ & "Until this problem is fixed the estimate will not be accurate! Do you want to correct this error now?", _ vbYesNo + vbCritical, "Incorrect or Missing formula...") = vbYes Then Call RestoreValues Else MsgBox "You can use the 'Restore Values' option on this form, if available, or the 'Repair/Resync' tool from the " _ & "Estimator's ribbon menu to correct this error." End If End If
I could have used “On Error Resume Next” and let it go as a meaningless error. But the checking which this code performs would have gone undone, and more errors might have contaminated the data. Besides, anytime VBA warns you of a problem, it must be addressed. It is simply good coding; it is smart coding. VBA, as quirky as it is, is still a very straightforward language with good builtin error checking. Some additional good tips on VBA error checking can be found here.