CODEWITHSUNDEEP
excelvba
czchlong
czchlong

Reputation: 2584

Unknown error on Excel VBA Shell() function

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?

Upvotes: 1

Views: 1080

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27249

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

Upvotes: 2

Related Questions