VBA MsgBox causes an erro

Question

In my VBA project I have the occasional MsgBox pop up to notify the user something has 'Completed' or 'Updated' after a subroutine has run. It seems to run okay without the MsgBox, but inserting one seems to give me an error. Not sure if it's necessary to display the entire code here as it's quite big but at the end of a subroutine I simply want ...

MsgBox ("Completed")

which is followed by the End Sub

However when I run this and then click on OK on the Msgbox, I get a runtime error which on clicking DeBug, it highlights the End Sub.

Is there any reason why having this would throw up such an error? Am I missing something from it? Many thanks

Some of the code here

'Add unique data to new location
For i = 1 To UnqArray1.Count
 rCell(i, 1) = UnqArray1(i)
Next

'Move Split Array into a new array
Set rTable2 = rCell
rng2() = rTable2.Value

'Filter into unique items
On Error Resume Next
For Each b In rng2
UnqArray2.Add b, b
Next

'Clear location
rCell.Clear

'Add new array to location
For i = 1 To UnqArray2.Count
rCell(i, 1) = UnqArray2(i)
Next

'Find the end of the category list
lastrow = Worksheets("CatMatch").Range("Q100000").End(xlUp).Row

'Sort alphabetically
Worksheets("CatMatch").Range("Q1:Q" & lastrow).Sort key1:=Range("Q1"), order1:=xlAscending, Header:=xlNo

'Copy it to CatMatch
Worksheets("CatMatch").Range("Q1:Q" & lastrow).Copy Destination:=Worksheets("CatMatch").Range("B15")

MsgBox "Completed"

End Sub

It depends where in the code the message box is. Perhaps post a bit more code so we can see what else is happening! Note: Post the code in your original question rather than in a comment. Perhaps the last 10 - 20 lines of code might be enough?? — MiguelH
It should be MsgBox "Completed" (with no parenthesis) since you aren't using the return value of MsgBox -- though that doesn't explain why your code is throwing an error. — John Coleman
I've tried without parenthesis. The error is "Code execution has been interrupted". When I click on Debug it highlights the End Sub. I've added some of the code in the original question. Thanks all for the comments. — user3683463
Try it without On Error Resume Next. That line should seldom be used. It makes debugging very difficult. — John Coleman

John Coleman John Coleman · Accepted Answer · 2016-01-13T13:37:41

I can't reproduce your error, but you are almost certainly incorrect that it runs okay without the MsgBox. The problem is that the problem with your code is being hidden by On Error Resume Next in the fragment:

'Filter into unique items
On Error Resume Next
For Each b In rng2
UnqArray2.Add b, b
Next

Two comments:

1) Why not use the RemoveDuplicates method if that is what you are trying to do?

2) Your code is using the fact that a collection throws an error if you try to add a duplicate key. This is a valid use of On Error Resume Next -- but only if you turn it off when you are done adding keys to the collection. Something like:

On Error Resume Next
    For Each b In rng2
        UnqArray2.Add b, b
    Next
On Error GoTo 0

A good habit to get into is to consider On Error Resume Next and On Error GoTo 0 as defining a block of code, perhaps even indenting the code inside the block as I did above. An even better habit is to not assume that only 1 type of error can happen. The above code is expecting that error 457 might arise (this is the error number corresponding to trying to add a duplicate key -- you need to search documentation to find it, or just run your code without the error handling and see how it crashes). Anything else indicates some other problem. To be maximally safe you can do something like:

On Error Resume Next
    For Each b In rng2
        UnqArray2.Add b, b
        If Err.Number > 0 And Err.Number <> 457 Then
            MsgBox "Unhandled error: " & Err.Number
            Exit Sub
        End If
    Next
On Error GoTo 0

Doing this won't solve your problem, but should make your actual problem more apparent.

VBA MsgBox causes an erro

1 Answers