Unknown error on Excel VBA Shell() function

Question

I have the following Excel VBA function to open a log file for viewing and it does everything correctly, except that it always throws an error after I close the file from viewing:

Private Sub ViewLog_Click()
  On Error GoTo LogFileOpenHandler:
    If Shell("notepad.exe " & LOG_FULL_FILENAME, vbMaximizedFocus) <> 0 Then
      ' Log file opened successfully
      MsgBox "log file opened"
    Else
      MsgBox "Oopsies! Could not open log file for viewing."
    End If

LogFileOpenHandler:
  MsgBox "Oops...Unknown error occured. Could not open log file for viewing."
  On Error GoTo 0
End Sub

However, if I insert a Exit Sub as the last statement in If Shell(...) <> 0 Then there are no errors caught.

Could someone please explain to me what the error is and why it's thrown?

Scott Holtzman · Accepted Answer · 2012-10-31 17:28:22Z

2

The reason it says there is an error is because you don't have the Exit Sub (like you alluded to) after the If block. So each time the program is run it runs all the way through and produces the message box under LogFileOpenHandler: because there is nothing to stop the code from executing that script.

If you add the Exit Sub back in, it will run as you want it to:

Private Sub ViewLog_Click()
  On Error GoTo LogFileOpenHandler:
    If Shell("notepad.exe " & LOG_FULL_FILENAME, vbMaximizedFocus) <> 0 Then
      ' Log file opened successfully
      MsgBox "log file opened"
    Else
      MsgBox "Oopsies! Could not open log file for viewing."
    End If

    Exit Sub 

LogFileOpenHandler:
  MsgBox "Oops...Unknown error occured. Could not open log file for viewing."
  On Error GoTo 0
End Sub

edited Oct 31, 2012 at 17:28

answered Oct 31, 2012 at 17:20

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

czchlong Over a year ago

Oh you are right, I completely misunderstood flags. Now I understand.

Collectives™ on Stack Overflow

Unknown error on Excel VBA Shell() function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related