How to Solve Tough Problems – Part 3 – Fixing Code Bugs

Introduction: The Gordian Knot

In the ancient capital of Persia, the Oracle of Telmissus, declared that the man who could untie the famed Gordian knot would become the ruler of all Asia. This knot, comprised of several interlocking knots, was so tightly entangled that it was thought nearly impossible to untie.

Although many attempted the task none succeeded, until the man, who later became known as Alexander the Great solved the problem. There are dueling stories as to his solution. The first is that he cut the knot with his sword. The second is that he found the linchpin that led to the unravelling.

I have to say that this apocryphal story seems to reach into the land of fantasy. Cutting the knot with a sword would certainly solve the problem, but come on! As far as the linchpin is concerned, certainly that would have been seen by others.

However, the point of the story is that even difficult problems may be solved. With this Access Wizard, I will show you techniques to solve help solve problems in your VBA code.

Prerequisites

For truly powerful Access Applications, you will be using code. If you haven’t done much coding, you have probably relied on the various wizards in Access. When you use one of these, one of the frequent outputs is behind-the-scenes VBA code. Looking at this code will give you some indication of what the code is doing.

For a starter, check out the December 2006 Wizard. That issue gives some pros and cons of macros, and will help you to learn how to convert macros into VBA code.

You can also google “Access VBA tutorial.” When I do that, I get over 4.6 million hits. I’m sure you can find one to help you get down the learning curve.
 
One of the ways to best solve problems, of course, is to never let them happen.

Require Variable Declaration

Variables are virtual buckets in which you can store things. Some computer languages, including VBA, allow you to create variables on the fly. This is easy to do, but leads to spelling errors that lie in wait to come and bite you. By requiring variable declaration, you are telling the program not to allow any variable to be used unless you have first “declared” that you will be using it.

You do this by including “Option Explicit” in the top part of every code page. You can make this happen automatically by checking Require Variable Declaration in the Code Editor Option dialog box. To do this go to any code module, then click on Tools | Options then select Require Variable Declaration in the Cold Settings section.



Check out this Months’ trap for more information.
​

Trace the Data

In an Access database, you are almost always reading, writing, editing, or manipulating data. Interrupt your process at critical points in the program and either inspect the data to determine at what point the data is not what you expect it to be. When you reach that spot, look to the preceding code for the source of the error.

 

Hard Breakpoints and Stepping Through the Code

Most code debugging is tracing code line by line. To do that, you must stop the code and then watch it execute each line in turn. To stop the code at any point in the process, you create a Breakpoint. To do this in the code window, you click on the grey bar on the left to create the break point. That will turn the line red indicating that, when the code hits that point, it will stop and wait for you to continue.



At this point, you have several options. If you click in the Debug menu at the top of the screen, you will see the following:



The most commonly used tools are:

F8 - Execute the current line of code and “step” into the next line. If the line of code calls a function or calls another sub routine, it will take you to that chunk of code.

Shift F8 – steps “over” the code. This is relevant only if the line of code calls a subroutine or function.

F5 - Continue the code without stepping. Although this option is not available in the Debug dropdown, it is always available during code debugging. When you do this, the code will continue until it is complete, hits another break point, or stops on an error in the program.

As you are stepping through the code, if there is a bug that throws an error code, you will be able to see exactly the line causing the problem, which will be starting place to begin detecting the source of the problem.
 

Variable Inspection

As you are stepping through the code, you can inspect any of the variables at that point in time. The easiest way to do this is to hover of the variable and its value will appear in a popup screen.

If the variable you are interested in is not in the routine that you are currently in, you can see the value by going to the immediate window at the bottom of the screen (if you don’t see the immediate window, hit Control G to make the immediately window appear) and type  ?  Name of your variable, which will give you the value of your variable.
 

Conclusion and Coming Attractions

In this Wizard, we have started the process to debug VBA code. In the next issue, we will continue to build your ability to debug code, which will help you to become a more powerful problem solver.