Microsoft Office Forums - View Single Post - Error Handler If User Does Not Want to Overwrite File

BobBridges · 04-03-2020, 08:31 PM

Ah, you're new to VBA, I see

. Don't sweat it; everyone has to start out as a noob, and this one is easy.

Take a look again at the code as you wrote it. It differs in a few ways from what I suggested, and some of the differences are important:

Code:

ErrHandle:
    If Err.Number = 1004 Then Exit Sub
    MsgBox "Exiting Sub"
    Else
    Resume
    End If

Let's look at the important differences first:

1) The way I wrote it went like this:

Code:

If Err.Number = 1004 Then
  <do one thing>
Else
  <do a different thing>
  End if

Your If statement says simply "If Err.Number = 1004 Then <do something>. The difference is that you have the first thing you want to do on the same line as the If-Then part. That may seems like a small, picky difference, but in VBA it makes all the difference. If you look up the language documentation, you'll see it offers two formats; when you put it all on one line, that one line is all you get to say about it.

So that explains the actual error message: You said "if <something is true> then <do something>", and that was the end of the If statement—so when it later saw an End If, it didn't understand why. Let's fix that one problem:

Code:

If Err.Number = 1004 Then
  Exit Sub
  MsgBox "Exiting Sub"
  Else
  Resume
  End If

2) Another important error: You told to Exit Sub, and then you told it in the following statement to do the MsgBox message. But by that time it would already have ended the program. If you want it to display the message, naturally you want it to do so before it exits the program. Let's move Exit Sub:

Code:

If Err.Number = 1004 Then
  MsgBox "Exiting Sub"
  Exit Sub
  Else
  Resume
  End If

That doesn't do what you want, yet. What you want is to ask the user what to do in case of a 1004 error, and all this does is display a message saying "Exiting Sub" and then quitting. But at least it'll do that much. Before we fix that, let's talk about an unimportant (well, less important) problem. VBA doesn't care about indentation, but your eyes do. I think most experienced programmers will tell you this too: There are several decent ways to indent your code so that you can easily tell which statements are "subordinate" to others. You can indent two spaces, or four; you can indent the End along with the other statements in a block, or with the If and Else. You can pick any of a number of styles. But pick one! This is how I do it:

Code:

If Err.Number = 1004 Then
  MsgBox "Exiting Sub"
  Exit Sub
Else
  Resume
  End If

This is getting kind of long, so we'll tackle the next part in a separate post. With me so far?