How to handle errors with a generic error-handling routine in Microsoft VBA procedures – TechRepublic

How to handle errors with a generic error-handling routine in Microsoft VBA procedures – TechRepublic

How to handle errors with a generic error-handling routine in Microsoft VBA procedures – TechRepublic 0 0 Alan Dickson

Register for your free TechRepublic membership or if you are already a member, sign in using your preferred method below.
We recently updated our Terms and Conditions for TechRepublic Premium. By clicking continue, you agree to these updated terms.
Invalid email/username and password combination supplied.
An email has been sent to you with instructions on how to reset your password.
By registering, you agree to the Terms of Use and acknowledge the data practices outlined in the Privacy Policy.
You will also receive a complimentary subscription to TechRepublic’s News and Special Offers newsletter and the Top Story of the Day newsletter. You may unsubscribe from these newsletters at any time.
All fields are required. Username must be unique. Password must be a minimum of 6 characters and have any 3 of the 4 items: a number (0 through 9), a special character (such as !, $, #, %), an uppercase character (A through Z) or a lowercase (a through z) character (no spaces).
How to handle errors with a generic error-handling routine in Microsoft VBA procedures
Your email has been sent
Use this VBA error-handling routine to make sure no errors disrupt the code.
Microsoft Visual Basic for Applications is the underlying development language for Microsoft Office apps. Each of these apps has a unique object model but they share some logic. For instance, all the models offer loops, ifs, error-handling and more.
When using VBA, regardless of the app, consider adding a generic error-handling routine to every procedure when that procedure doesn’t require more specific error handling. If an unexpected error shows up, it won’t break your procedure. Instead, it will display an informative message that you can then use to troubleshoot and handle the error more appropriately.
SEE: Hiring kit: Python developer (TechRepublic Premium)
In this tutorial, I’ll show you a block of VBA code that you can insert into VBA procedures to catch unexpected errors. I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use older versions of Microsoft Office.
VBA has many debugging tools to help you find errors when troubleshooting. The On Error statement does two things: It responds to an error and then specifies where the flow goes to correct that error. A runtime error occurs when there’s an error in the code. This happens for several reasons, but it mostly occurs due to syntax errors and code requests that VBA can’t fulfill.
The On Error statement uses the following syntax:
We’ll work with the first syntax, which directs the flow to a special error-handling routine, where line identifies that routine, within the procedure. A generic line follows:
On Error GoTo errHandler
You might see variations of errHandler, but you should readily recognize line when reviewing code. In this case, flow moves to errHandler when an error occurs. If you don’t use an On Error statement, any run-time error that occurs is fatal because execution stops.
Now that you know about the On Error statement, it’s time to use it.
A generic error-handling routine does nothing to prevent an error. Its job is to expose the error so you can fix the code that generated it. Instead of the error halting the code, which will provide little information on why the error occurred, a generic error-handling routine will display the information you need to fix the error. Even if you believe your code to be error-free, I recommend that you add a generic error-handling routine, such as the one in Listing A.
Listing A
Private | Public Function | Sub | procedurename()
On Error GoTo errHandler
….procedure code…
'object = Nothing
Exit | Function | Sub
errHandler: 'The colon character is required.
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
'object = Nothing
End Sub
Insert the On Error GoTo line statement before the procedure’s code. After the procedure code, include an Exit statement. Otherwise, VBA will execute the errHandler: routine, which you won’t want.
If the code creates objects, be sure to destroy them, adding the necessary object = Nothing statements before the Exit and End statements. Listing A includes one in each spot, commented out as a reminder, but you might need several.
SEE: Hiring kit: Back-end Developer (TechRepublic Premium)
The errHandler code displays a message that contains the error number, a description and the name of the module. I add it to every VBA procedure and comment out the routine if it becomes a nuisance during development.
Your code will dictate specific handling needs, which you will write into your code. The more you test the code, the more specific handling you will add, usually. But there’s always that chance that an unexpected error will halt the code. For this reason, every procedure should have a generic error-handling routine. This routine won’t be able to correct the error and continue, but it will display helpful information so that you can fix the problem at the source.
From the hottest programming languages to the jobs with the highest salaries, get the developer news and tips you need to know.
How to handle errors with a generic error-handling routine in Microsoft VBA procedures
Your email has been sent
Your message has been sent
TechRepublic Premium content helps you solve your toughest IT issues and jump-start your career or next project.
The technologies could enable immersive experiences, accelerated AI automation and optimized technologist delivery in the next two to 10 years, according to the firm.
Are you an IT manager or executive trying to make the case for a new ERP vendor? Compare the top ERP software solutions with our list today.
Learn about the new features available with macOS 13 and how to download and install the latest version of Apple’s flagship operating system.
Get great deals on developer and Linux training courses, Microsoft Office licenses and more through these TechRepublic Academy offerings.
This comprehensive guide covers the use of services from multiple cloud vendors, including the benefits businesses gain and the challenges IT teams face when using multicloud.
Recruiting a Scrum Master with the right combination of technical expertise and experience will require a comprehensive screening process. This hiring kit provides a customizable framework your business can use to find, recruit and ultimately hire the right person for the job. This hiring kit from TechRepublic Premium includes a job description, sample interview questions …
Knowing the terminology associated with Web 3.0 is going to be vital to every IT administrator, developer, network engineer, manager and decision maker in business. This quick glossary will introduce and explain concepts and terms vital to understanding Web 3.0 and the technology that drives and supports it.
While the perfect color palette or the most sublime button shading or myriad of other design features play an important role in any product’s success, user interface design is not enough. Customer engagement and retention requires a strategic plan that attempts to measure, quantify and ultimately create a complete satisfying user experience on both an …
IIoT software assists manufacturers and other industrial operations with configuring, managing and monitoring connected devices. A good IoT solution requires capabilities ranging from designing and delivering connected products to collecting and analyzing system data once in the field. Each IIoT use case has its own diverse set of requirements, but there are key capabilities and …

source

    Would you like to receive notifications on latest updates? No Yes