Handling Specific Errors

A few years ago I posted my code for pasting values. It’s changed a bit since then. This morning, it looked like this:

If I’m copying, then I paste both the values and the number formats (but not other formats, comments, data validation, etc). I found that this was my most common need. When I only want values, I use Alt+E+S like in the old days. If I’m cutting, I can’t PasteSpecial, so I just Paste.

Usually I select the first cell where I want to paste and press Ctrl+Shift+V and the selection expands to fit the data. Sometimes, however, that expanded selection contains merged cells which causes an error. I’ve just ignored the error in the past, but I figured it was time to fix it. Now my code looks like this:

Above the PasteSpecial line, I put an On Error statement to direct the program flow to an error handler. I didn’t apply that to the Cut portion of the code because cutting and pasting prompts me to unmerge cells and that’s the behavior I want.

In the error handling block, I check for the specific error 1004. I didn’t want to gloss over any old error, just this one in particular. If the error is 1004 (PasteSpecial method of Range class failed, or something like that), I then check to see if the new, expanded selection has any merged cells. While this is the only scenario that I’ve experienced that produces that error, it’s a pretty generic error and I’m sure there’s more. So I wanted to see the error description for any other errors.

To check for merged cells, I use

. When the selection is more than one cell, the MergeCells property returns True if all the cells are merged, False, if none of the cells are merged, and Null if only some of the cells are merged. I don’t try to fix the situation, just display a somewhat meaningful error message. I don’t use merged cells all that often (it’s usually someone else’s spreadsheet, but not always). When I encounter this error, it’s usually because I copied more cells than I thought – hidden cells in the copy range. So it’s best to go back and start over.

At then end of the error block, it Resumes to ErrExit where it simply exits the sub. If there are no errors, the Exit Sub is executed before the ErrHandler label is reached.

Posted in VBA

4 thoughts on “Handling Specific Errors

  1. Although I am not able to create any error with you code, I’d suggest to write it differetnly:

    Sub M_snb()
    On Error Resume Next

    Select Case Application.CutCopyMode
    Case 1
    If TypeName(Selection) = "Range" Then Selection.PasteSpecial 12
    Case 2
    If Not ActiveSheet Is Nothing Then ActiveSheet.Paste
    End Select

    If Err.Number <> 0 Then MsgBox IIf(Err.Number = 1004, "The range " & Selection.Address & " has merged cells. Can't paste", Err.Description)
    End Sub

  2. You should really make the error number a named constant. You’ll come back to that in 6 months and ask, “what the hell is Error 1004”?

  3. @Danny, 1004 is an application (Excel) error code, not a user raised one. The code shows what it means by the custom MsgBox error:
    MsgBox "The range " & Selection.Address & " has merged cells. Can't paste"

    If you were to make these error codes 'constants' then it would be far better to use custom enums. Eg
    public Enum myErr
    PasteSpecialRangeFailed = 1004
    OtherError = 9999
    End Enum

  4. Whatever happened to the Help Context ID and Help File parameters of an error?

    If you raise an error, trap it in an error handler, and pass those parameters back into a message box with the vbMsgBoxHelpButton, all you get is the generic Excel ‘Help’ index:


    Dim sMsg Ss String
    Dim iDlg As vbMsgBoxResult
    Sim iBtn As vbMsgBoxStyle

    ‘ These constants should be elsewhere in your application and the parent function…
    Const APP_NAME As String = “Catfood Calculator Application”
    Const FUNC_NAME As String = “Check Feed Hopper”

    strMsg = “The ” & FUNC_NAME & ” function raised error ” & Err.Number & “: ”
    strMsg = strMsg & vbCrLf & vbCrLf
    strMsg = strMsg & Err.Description
    strMsg = strMsg & vbCrLf & vbCrLf
    strMsg = “Do you want to continue, retry the ” & NAME & ” operation, or stop the application?”

    iBtn = vbYesNoCancel + vbMsgHelpBoxHelpButton

    iDlg = MsgBox(sMsg, iBtn, APP_NAME & ” Error…”, Err.Helpfile, Err.HelpContext

    ‘ **** CASE statement for iDlg response ****

    This is a useful piece of user interaction, because most user actions that raise an error – or could be handled by Err.Raise to a custom error message defined in your applications – have a perfectly usable helpfile page in the existing.

    This ‘Empowers the Users’ – that is to say: they get what they need to sort things out for themselves, rather than making repeated calls to the developer (you).

    But… What’s happening nowadays is that the Err object is still passing “XLMAIN11.chm” as the help file in Excel 2010 and Excel 2013, even though this is no longer the target for context-based error dialogues.

    I’m tempted to put old copies of XLMAIN11.chm in my users’ local application folders, just to bring back the functionality, even though some of the information will point to actions and dialogues that no longer exist in Excel 2010 and 2013. But it would be better to find the ‘live’ target.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.