Debugging Excel VBA Code. Introduction If there's one part of the Excel environment that has remained fairly stable over the years it's the Visual Basic Editor (VBE) and associated development environment.
While Excel 2007 has seen a monumental change, look under the covers and you'll find the same old VBE that has been there since Excel 97. For those who've never delved too deeply, this article gives a quick run down of the main features of the VBE and shows how it can be used to debug macros and VBA code. The examples in this article have been tested with Excel 2000 and Excel 2007. Debugging VBA. Once you're written your VBA procedures and achieved clean compiles without errors, you are ready to undertake the most important step in programming: testing and debugging.
FMS Technical Papers - 17 Steps to Better VBA Code. By Dan Haught, Executive Vice President of Product Development September 1997 Introduction Writing code is easy. Writing good code is hard. So what is good code anyway? Generally, we think of the following attributes: Visual Basic Error Codes (halFILE.com) The information in this article applies to: - Standard, Professional and Enterprise Editions of Microsoft Visual Basic, 16-bit and 32-bit, for Windows, version 4.0 10830 North Central Expressway, Suite 460 Dallas, TX 75231. EducationOnlineForComputers.com Free Software Training Blogs. Office VBA—Basic Debugging Techniques. Through involvement in several VBA oriented mailing lists, I've observed that a great deal of time is spent by members trying to see what has happened to variable values, etc. in their code. And most of their questions wouldn't be asked if they had been instructed on how to use the built in debugging tools available in the Visual Basic Editor (VBE).
To get started, let's set some ground rules about the first things we SHOULDN'T do to check our code: DON 'T set watch statements. While I don't expect anyone to know what a watch statement is, I can tell you they can be complicated, capable of introducing new bugs and not that informative unless you understand how to create a watch statement. They are not the first choice in your debugging toolbox. That's all for the rules so this should be pretty simple, eh? Error Handling and Debugging Tips and Techniques for Microsoft Access, VBA, and Visual Basic (VB6) Experienced developers use a variety of techniques to simplify their coding and maintenance efforts.
Some of the tricks are general programming styles and conventions, while others are specific to the characteristics of Visual Basic (VB6) and Microsoft Office/Access VBA. Hopefully, by adopting such "best practices" techniques, you'll be able to write code that's easier to write, debug, and understand. Not only can you reduce bugs during development, you can also significantly reduce the effort required to replicate and fix bugs your users encounter. A consistent coding style is critical for efficient application development in multi-developer environments. It also increases the chance that future developers can understand your work to fix or enhance it. Introduction Debugging is one of the most important skills for a developer. Debugging doesn’t end when the application is shipped.
Run-Time Tools. The topic you requested is included in another documentation set.
For convenience, it's displayed below. Choose Switch to see the topic in its original location. The Microsoft® Visual Basic® for Applications (VBA) run-time debugging tools are designed to give you a snapshot of what is happening in your code at any point. You use these tools together to let you take a closer look at what your code is doing at any point in time. The following table contains a list of all the tools available to you and a short description of when they are useful. For more information about any of the debugging tools, search the Visual Basic for Applications Reference Help index for the name of the specific tool. See Also. Using the Immediate Window. While the title of last week’s column (see ) was “Very Hidden Excel Worksheets,” my primary objective was to introduce two of the Visual Basic Editor’s (VBE) windows.
I’ll continue on this tack this week by discussing the Immediate window, a handy window with several uses. The Immediate window is an essential window that I use constantly while writing VBA code. Figure 1 shows the window configuration I always use when writing VBA. If the Immediate window isn’t visible in your VBE, you can display it by pressing Ctrl + G or selecting View/Immediate Window from the main menu. I’ve changed the background color of the window for reasons I’ll save for a future tip. Figure 1: The VBE with the Project, Properties, and Immediate windows open. One use of the Immediate window is to evaluate individual VBA statements.
In the Immediate window and press Enter. Intellisense is a feature that displays a drop-down list of applicable properties and methods given a VBA object. Debugging VBA. Once you're written your VBA procedures and achieved clean compiles without errors, you are ready to undertake the most important step in programming: testing and debugging.
Too many programmers become complacent once the code is written and they get a clean compile. These are only the first steps.