A few years ago I posted my code for pasting values. It’s changed a bit since then. This morning, it looked like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub CopyPasteValues() gclsAppEvents.AddLog "^+v", "CopyPasteValues" If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then Selection.PasteSpecial xlPasteValuesAndNumberFormats ElseIf Application.CutCopyMode = xlCut Then If Not ActiveSheet Is Nothing Then ActiveSheet.Paste End If End If End Sub |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Sub CopyPasteValues() gclsAppEvents.AddLog "^+v", "CopyPasteValues" If TypeName(Selection) = "Range" And Application.CutCopyMode = xlCopy Then On Error GoTo ErrHandler Selection.PasteSpecial xlPasteValuesAndNumberFormats ElseIf Application.CutCopyMode = xlCut Then If Not ActiveSheet Is Nothing Then ActiveSheet.Paste End If End If ErrExit: Exit Sub ErrHandler: Select Case Err.Number Case 1004 If IsNull(Selection.MergeCells) Then MsgBox "The range " & Selection.Address & " has merged cells. Can't paste" Else MsgBox Err.Description End If Case Else MsgBox Err.Description End Select Resume ErrExit End Sub |
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
|
1 |
IsNull(Selection.MergeCells) |
. 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.
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
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”?
@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
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:
ErrHandler:
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.