On Error Message Box Vba Excel
Contents |
Forums Excel Questions vba: if error show msgbox Results 1 to 6 of 6 vba: if error show msgboxThis is a discussion on vba: on error goto vba if error show msgbox within the Excel Questions forums, part of the vba error handling best practices Question Forums category; I have a subroutine that opens a file based on some input from the user.... excel vba try catch generally the file it looks ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version vba on error exit sub Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Oct 17th, 2006,08:17 PM #1 cmhoz Board Regular Join Date Aug 2006 Location Australia Posts 268 vba: if error show msgbox I have a subroutine that opens a file based on some input from the user.... generally the file it looks for has a standard naming format used. However
On Error Goto Line
sometimes the person responsible for this will use some other name, thus screwing up my macro. Rather than excel showing the generic error box, confusing the user completely, I want it to show MY msgbox telling the user to go in and find the file manually... I'm sure I've done this before, but I'm drawing a blank on how I managed it. Any help out there?? Sub openphiac() Dim strfolder as string Dim strphiacfile as string strfolder = Range("folder") strphiacfile = Range("phiacfile") Workbooks.Open Filename:="O:\Phiac Data\PhiacTables\" & strfolder & "\" & strphiacfile & ".xls" End Sub Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Oct 17th, 2006,08:25 PM #2 HalfAce MrExcel MVP Join Date Apr 2003 Location Alaska Posts 9,444 Hi cmhoz Something like this ought to do it. Code: Sub openphiac() Dim strfolder As String Dim strphiacfile As String strfolder = Range("folder") strphiacfile = Range("phiacfile") On Error GoTo ErrMsg Workbooks.Open Filename:="O:\Phiac Data\PhiacTables\" & strfolder & "\" & strphiacfile & ".xls" Exit Sub ErrMsg: MsgBox ("Type in your message here."), , "MESSAGE TITLE" End Sub
generally put more focus on the coding part and getting the desired result but during this process we forget an important thing i.e. Error handling. Error handling is an important part of
Vba On Error Goto 0
every code and VBA On Error Statement is an easy way for handling unexpected vba error handling in loop exceptions in Excel Macros. A well written macro is one that includes proper exception handling routines to catch and tackle every err.number vba possible error. Error handling is important because in case of any unexpected exceptions your code doesn’t break. Even if any fatal unexpected error occurs in the code then also you should ensure that the code http://www.mrexcel.com/forum/excel-questions/229230-visual-basic-applications-if-error-show-msgbox.html should terminate gracefully. Definition of VBA On Error Statement: On Error statement instructs VBA Compiler, what to do in case any runtime exception are thrown. Syntax of On Error Statement: Basically there are three types of On Error statement: On Error Goto 0 On Error Resume Next On Error Goto
three flavors: compiler errors such as undeclared variables that prevent your code from compiling; user data entry error such as a user entering a negative value where only http://www.cpearson.com/excel/errorhandling.htm a positive number is acceptable; and run time errors, that occur when VBA cannot correctly execute a program statement. We will concern ourselves here only with run time errors. Typical run time errors include attempting to access a non-existent worksheet or workbook, or attempting to divide by zero. The example code in this article will use the division by zero error (Error 11) on error when we want to deliberately raise an error. Your application should make as many checks as possible during initialization to ensure that run time errors do not occur later. In Excel, this includes ensuring that required workbooks and worksheets are present and that required names are defined. The more checking you do before the real work of your application begins, the more stable your on error goto application will be. It is far better to detect potential error situations when your application starts up before data is change than to wait until later to encounter an error situation. If you have no error handling code and a run time error occurs, VBA will display its standard run time error dialog box. While this may be acceptable, even desirable, in a development environment, it is not acceptable to the end user in a production environment. The goal of well designed error handling code is to anticipate potential errors, and correct them at run time or to terminate code execution in a controlled, graceful method. Your goal should be to prevent unhandled errors from arising. A note on terminology: Throughout this article, the term procedure should be taken to mean a Sub, Function, or Property procedure, and the term exit statement should be taken to mean Exit Sub, Exit Function, or Exit Property. The term end statement should be taken to mean End Sub , End Function, End Property, or just End. The On Error Statement The heart of error handling in VBA is the On Error statement