Excel macros: error handling and logging

A macro that stops with an error dialog is inconvenient. A macro that stops after writing half its output, with screen updating switched off and alerts suppressed, is dangerous. The user sees a frozen screen or a partial report and cannot tell which records were processed.

Error handling is the part of the macro that decides what happens after something goes wrong. Logging is the part that leaves evidence. Together they turn an opaque failure into a message, a record, and a recoverable state.

Give each procedure one job and a predictable shape: validate inputs, switch settings, do the work, restore settings, then exit. Put the error handler at the end and jump over it on success. VBA has no finally block, so the cleanup steps need their own label and must run from both paths.

Use On Error GoTo rather than On Error Resume Next for the main flow. Resume Next is appropriate for a specific, expected failure, such as deleting a file that may not exist, and should cover one statement rather than the whole procedure.

Restore application settings in the cleanup path, not at the end of the happy path. If the routine disables events, alerts, or screen updating, a failure before the last line would leave the application in that state for the next run.

An error handler should capture the number, description, and source, then decide whether to retry, skip, or stop. A missing optional file may be skippable. A missing required sheet is not. If the procedure cannot continue, report the failure and leave the workbook in a known state.

Avoid empty handlers that swallow every error. They convert a bug into a silent wrong answer. When you re-raise an error, preserve the original details so the next handler or the log can show what actually happened.

A log entry needs enough context to reconstruct the run without the user's memory. Record the time, the workbook, the procedure, the severity, the error number, the description, the record or file being processed, and the outcome. Add a run identifier when one execution processes many records.

Write to an append-only table on a dedicated sheet, or to a text file when the workbook may be closed or corrupt. Do not overwrite the previous entry. Logging can fail too, so wrap the write in its own handler and fall back to a message rather than taking down the main job.

Keep the log bounded. Append rows to a table, archive or clear old entries on a schedule, and batch writes rather than writing one cell at a time inside a long loop. Never store credentials in the log.

Force the errors you expect: rename a source file, lock the output, remove a sheet, pass an empty range, and use a read-only workbook. Confirm that the message is accurate, the log entry exists, and the cleanup restored the application settings.

Then test the recovery. Can the user re-run the macro without duplicating records? Is there a staging area or a backup? Document the restart point, the owner, and the meaning of each severity. A macro is easier to support when failure is a designed path rather than an exception.