Vba msgbox ошибка

Public Sub ErrorHandler()
'   The code is cleaned up by using ErrorHandler as a function because it has to be written out fewer times, and makes changing ErrorHandler easier

MsgBox("Error detected" & vbNewLine & vbNewLine & "Error " & Err.Number, vbCritical, "Error Handler: Error " & Err.Number)

End Sub

3

MsgBox "Error detected" & vbNewLine & vbNewLine & "Error " & Err.Number, vbCritical, "Error Handler: Error " & Err.Number

response = MsgBox ("Error detected" & vbNewLine & vbNewLine & "Error " & Err.Number & "Do you want to retry", vbYesNo, "Error Handler: Error " & Err.Number)

4

Answers

• If you merely want to replace

  with

  MsgBox Err.Description, vbCritical
  

  or similar, you can do that in one fell swoop in the Edit | Replace dialog — set the Search scope to Current Project and click Replace All.

  If you want to add the name of the procedure and module, you’d have to use the Microsoft Visual Basic for Applications Extensibility 5.3 library. Personally, I don’t think it’s worth the trouble — during development, I can keep track of where errors occur
  myself, and I don’t think it’s useful for end users to see the procedure and module names.

  ---

  Regards, Hans Vogelaar

  • Marked as answer by

    Thursday, February 23, 2012 9:20 AM

• Its a nice idea but you may need to think it through further.  Error handling is a bit of an art and there are few hard and fast rules.

  Your colleague probably left Error$ there for a reason (like he knew the module name by asking the user which form or report was in use when the error was encountered,so why would he want to spend hours putting in the procedure name????)

  Probably the more important thing is what the code should do after the error message has been displayed — or whether the user should be given a more user friendly explanation.

  For example:

  • Once the error has occured, you could terminate the procedure by exitting out of it …. if the code has left database or recordset objects open, you may be better off to close them.  Otherwise the user may find he cannot close the application, it
    runs out of memory etc, etc
  • You could RESUME NEXT meaning the code goes back to the point where the erro occurred.  Now this could generate even more error messages because of the initial error and look pretty bad …. but it would serve to ensure any instantiated objects are
    likely to be closed
  • RESUME at some specific label in the code ….
  • Or may be replce some error messages like:
  • If err.number = 3000 then
  • Msgbox «You must enter a valid value !!!», vbOKonly
  • endif
  • Resume  Next

  It is probable the application may need some cleaning up to handle errors in a more user friendly manner — and it may be you need to do more than simply add a procedure name.  You could create a procedure to record errors and login data to a table then
  decide which messages to analyse further with a view to cleaning up the application.  

  ---

  PG A bit of experimentation by trial and error often helps.

  • Marked as answer by
    Bruce Song
    Thursday, February 23, 2012 9:20 AM

• Further to what Hans V has recommended (which I agree with), I would suggest taking a closer look at «Debug.Print«, «Debug.Assert» and the Immediate (debugging) window in the VBA Code Editor, which you can show by holding
  CTRL+G.

  ---

  Matthew Slyman M.A. (Camb.)

  • Marked as answer by
    Bruce Song
    Thursday, February 23, 2012 9:20 AM