Dealing with Logical Errors

Even if your code compiles and runs without generating an error message, the code isn't necessarily perfect. Your code can also contain logical errors. Unlike a compile error, which is an error in syntax or a typographical error, a logical error is an error in your thinking (logic). The computer will always do exactly what the code tells it to do, even if you tell it to do the wrong thing.

For example, say you meant to write a line of code to open some form, but you accidentally wrote the code to close the form. When you run the code, the computer will (of course) close the form — not open it. The computer would never look at your code and think to itself, "Hmmmm. I bet she meant to open a form here, so I'll do that instead." Computers just don't work that way. The computer always does exactly what the code tells it to do.

Pinpointing logical errors in your code is often difficult mainly because when you run a procedure, everything happens in less time than it takes to blink your eyes. Often it helps to take a look at what's going on behind the scenes while the code is running. The VBA editor provides a few tools that allow you to see what's going on behind the scenes.

Checking on variables with Debug.Print

In earlier chapters, you can see examples of using the VBA editor Immediate window to test procedures and try out expressions. For example, typing a simple expression like ? 1+1 (What is one plus one?) results in 2, which is the sum of one plus one. Typing the expression ? CurrentProject.AllForms. Count displays the number of forms in the current database.

You can also force your code to display information in the Immediate window. However, in code, you use a Debug.Print expression rather than a ? expression to make the code print to the Immediate window. This is an easy way to watch what's happening to variables behind the scenes while your code is running.

The real beauty of Debug.Print is that it allows you to write a little code, test it to see what's going on, and make sure that all is well before writing more code. For example, in Chapter 11, I show you an example of a function named PCase() that can convert any text to proper noun case (the first letter of each word in uppercase).

When you look at a completed procedure like that, you might think that the programmer just typed it out like typing a note, and the thing just ran perfectly right off the bat. That's not even close to how programmers really work. A programmer knows that every line of code is just one step in the overall procedure. For the procedure as a whole to work, you need to make sure that each individual piece is doing exactly what you think it's doing.

To write the PCase() function, I actually started out by just writing this:

Public Function PCase(anyText) As String PCase = StrConv(anyText, vbProperCase) Debug.Print "PCase = " & PCase End Function

That was the entire function, at first. To test it, I typed ? PCase("MARVIN DODoskY") into the Immediate window and pressed Enter. When I did, the Debug.Print statement in the code displayed the following in the Immediate window:

PCase=Marvin Dodosky

To test it again, I typed ? PCase("123 OAK TREE LANE") into the Immediate window and got back PCase = 123 Oak Tree Lane. At this point, I knew that the basic problem — converting the first letter of each word to uppercase and lowercasing all other letters — was solved.

Granted, having the procedure show a small result like that in the Immediate window is of no value to a potential user of the function. But to me — as the programmer — it told me that after the statement PCase = StrConv(anyText, vbProperCase) executes, the PCase variable contains the passed text with the first letter of each word capitalized. At that point, I knew that the basic problem of capitalizing the first letter of each word was solved, so I could then move onto writing code to solve the next problem.

I decided to tackle the Mc problem next. First, I had to figure out how to tell the procedure that if the first two letters were mc, change PCase so that the first and third letters are uppercase. I already knew that I could use the Mid() function to grab any portion of any string, and that I could use the UCase() function to convert any letter to uppercase.

So I typed out the If...Else...End If block of code to handle any string that starts with the letters Mc, shown as follows, and moved the Debug. Print "PCase = " & PCase statement below that, as follows:

Public Function PCase(anyText) As String PCase = StrConv(anyText, vbProperCase)

PCase = "Mc" & UCase(Mid(PCase, 3, 1)) & Mid(PCase, 3) End If

Debug.Print "PCase = " & PCase End Function

To test my progress, I typed into the Immediate window ? PCase("MCDONALD") and pressed Enter. The Immediate window showed PCase = McDdonald. Oops, that should have been McDonald — not McDdonald (with two d's). This is a logical error in the sense that the code ran without generating any error messages. The problem is with the logic of how I handled the problem.

After studying the code more closely, I realized that the last Mid statement —

& Mid(PCase,3) — was wrong. That should be Mid(PCase,4). So I changed the code, as follows:

Public Function PCase(anyText) As String PCase = StrConv(anyText, vbProperCase)

PCase = "Mc" & UCase(Mid(PCase, 3, 1)) & Mid(PCase, 4) End If

Debug.Print "PCase = " & PCase End Function

Once again, I tested the procedure by entering ? PCase("MCDONALD") into the Immediate window. I got back the following:

PCase = McDonald in the Immediate window. Now I knew that the Mc problem was solved. Onto the next problem — dealing with the Mac last names. From there on out, it was more of the same. I'd write a little code, test my progress so far, and fix any problems that I discovered. By making sure that each piece of the puzzle worked at each step in the process, I was able to finally create a custom function that did what I wanted it to.

The much larger NumWord() procedure from Chapter 11 was harder to write, of course. Again, it was all a matter of doing a little at a time, testing my progress, fixing all compile and logical errors, and then moving on to the next problem. Basically, I started out by declaring variables, setting up the array, and typing the first statement, followed by a couple of Debug.Print statements. Here's the basic idea; to save space, I put an ellipsis (...) in place of most of the array element definitions:

Function NumWord(AmountPassed As Currency) As String 'Declare all variables and arrays. Dim English As String, strNum As String Dim Chunk As String, Pennies As String Dim Hundreds As Integer, Tens As Integer Dim Ones As Integer, LoopCount As Integer Dim StartVal As Integer, TensDone As Boolean Dim EngNum(90) As String EngNum(0) = "" EngNum(1) = "One" EngNum(2) = "Two" EngNum(3) = "Three"

EngNum(80) = "Eighty" EngNum(90) = "Ninety"

strNum = Format(AmountPassed, "000000000.00") 'strNum is original number converted to string

Debug.Print "AmountPassed = " & AmountPassed

Debug.Print "strNum = " & strNum End Function

To test my progress at this point, I typed ? NumWord(1234. 56) into the Immediate window and then pressed Enter. The Immediate window returned this:

AmountPassed = 1234.56 strNum = 000001234.56

Now I could see what I really had to work with before writing more code. Also, I knew that because strNum is a string, I could use the built-in Mid() function to isolate portions of the string and still use AmountPassed to check for other things, like how large a number was being translated to English.

Knowing that I had AmountPassed and strNum to work with, I then wrote a little more code for the procedure, tested that, and worked out any kinks until all was well to that point. And so it goes. You write a little code, maybe use the Help to work out any compile errors, test the code, fix any errors in logic, and then you're on to the next bit of code.

If I'd tried to just type the whole procedure in one fell swoop before testing it, any problems would have been more difficult to find because they could be anywhere in the code. By writing a little, testing a little, and debugging a little along the way, I was gradually able to solve all the problems and come up with a procedure that actually works.

The only purpose of using Debug.Print in the code is to give yourself some feedback as to what's going on behind the scenes as that code is running. After any problems are solved at a given point in a procedure, you can delete any Debug.Print statements that you don't need any more. After all, the Debug.Print statements won't be of any value to the user of your custom function. Debug.Print statements serve only as a programmer's debugging tool.

Slowing down code

Using Debug.Print in code to get a little feedback as to what's happening in your procedure is helpful, but when you run the procedure, it will still execute in an eyeblink's time. To get things to slow down, you can set breakpoints in your code via the VBA editor Code window. A breakpoint in your code doesn't actually make the code run in slow motion; rather, it forces the VBA editor to suspend execution of code at that point so that you can explore the values of variables or whatever in the Immediate window.

Testing Function and Sub procedures

The syntax for calling a procedure from code, as well as from the Immediate window, is different from the syntax for calling a function. To run a procedure from the Immediate window, just type the procedure's name (without the parentheses) and press Enter — for example, if your module contains a Sub procedure declared as Sub.

To test a custom Sub procedure from the Immediate window, just type the procedure's name without any quotation marks and then press Enter. To test a function from the Immediate window, use the syntax ? functionName (arguments) where functionName is the name of your custom function, and arguments represents any sample data that you want to pass to the function for testing. After the function runs, the Immediate window will display the value returned by your function.

To set a breakpoint in a procedure, follow these steps:

1. Make sure that the procedure you want to test is open and visible in the Code window.

2. Move the cursor to the line where you want to suspend code execution; then do whichever of the following is most convenient at the moment:

• Right-click the line and choose ToggleOBreakpoint.

• Choose DebugOToggle Breakpoint from the VBA editor menu bar.

* • Click the Toggle Breakpoint button on the Debug toolbar.

If the Debug toolbar isn't visible in your VBA editor, choose ViewO ■ ■Oil ToolbarsODebug from the menu bar to make that toolbar visible.

After the breakpoint is set, test your code normally from the Immediate window. Your code will execute at its usual blazing speed until execution reaches the line that you defined as a breakpoint. Instead of being compiled and executed, the breakpoint line of code gains a bright yellow highlighter in the Code window and won't actually execute until you press F8. Basically, this means that you can make your code slam on the brakes and go into step mode, where you miraculously take over all code execution yourself.

Of the several ways to use step mode, just take a look at the easiest and most common way to set a breakpoint and use step mode. Suppose that while creating the NumWord() procedure, I want to take a look at all my variables just before the line that reads strNum = Format(AmountPassed, "000000000.00") executes. In the Code window, I'd right-click that line and choose ToggleO Breakpoint. Then I'd run the function and pass some huge number to it, as a test, by entering something like ? NumWord(123456789.00).

The procedure would run at its usual blazing speed up to the breakpoint line and then slam on the brakes. If I wanted to take a quick look at some variable defined in the code before the breakpoint line executes, I could ask the Immediate window by typing ? variableName. For example, if I enter ? strNum into the Immediate window while the code is suspended, I get nothing in return because strNum is empty before the breakpoint line executes. Still, getting nothing in response to the ? strNum expression is confusing. Here's a much quicker and easier way to check out the contents of your procedure's variables: the Locals window.

Using the Locals window

While your code is suspended in a breakpoint and in step mode, you can easily check the value and data type of every variable defined in your code up to that breakpoint. This saves you from having to type a bunch of ? variableName statements in the Immediate window. To see an overview or all the variables in the procedure, just choose ViewOLocals Window from the VBA editor menu bar or click the Locals Window button on the Debug toolbar.

The Locals window will open, showing the name, value, and data type of every variable defined up to that point in your code. Figure 12-9 shows an example where I opened the Locals window while code was suspended in the sample NumWord() procedure. There I can see the name, value, and data type of every variable that exists in NumWord() just before the breakpoint line executes.

In the Locals window, I can easily see the strNum variable that I previously checked on in the Immediate window. The Locals window lets me know that the variable is empty by showing "" as its value and String as its data type. That's all true and correct because a Dim statement near the top of the procedure has already declared strNum a string variable.

Moving and sizing editor windows

Like all windows in the VBA editor, you can anchor the Locals window to any edge of the program window. While it's anchored, you can change its height or width by dragging the border just above its title bar. You can also drag it by the title bar toward the center of the screen to make it a free-floating window. To put the Watch window back into hiding, click the Close (X) button in its title bar.

To get the Immediate and Locals windows to stack up as they are in Figure 12-9, first make sure that both are open. Drag one window's title bar right to the middle of the other's title bar and then release the mouse button. To resize them, drag the border line that separates the two panes up or down or drag the leftmost border left to right. To reverse the stack order of the two panes, drag the bottom pane's toolbar up to the middle of the top pane's window.

The Locals window shows the name, value, and data type of every variable created prior to the currently highlighted line of code. And knowing about all your variables at an exact moment of time can be a great aid to debugging your code. Keep reading because things really get good when you learn to use the step mode.

Stepping through code in step mode

Say you've set your break point, code execution has stopped at the breakpoint line, and maybe you're looking at variables in the Locals window. The real question is, "What next?" The answer is that you basically have four choices:

^ To execute the currently highlighted line of code (only): Press F8 or choose DebugOStep Into.

^ To skip the currently selected line without executing it: Press Shift+F8 or choose DebugOStep Over.

^ To execute all lines of code up to — but excluding — a specific line of code: First click the line to which you want execution to run. Then press Ctrl+F8 or choose DebugORun to Cursor from the menu bar.

^ To bail out of break mode: Press Ctrl+Shift+F8 or choose DebugO Step Out.

The best way to use step mode, as a beginner anyway, is to just press F8 to execute the currently selected line of code. When you do so, the line executes. Any changes to variables made by executed statements appear in the Locals window. For example, if I press F8 at the moment shown in Figure 12-9, the strNum variable's value in the Locals window changes from "" (nothing) to "123456789.00" because the executed statement gave strNum a value.

Even better, you can sit there and just tap the F8 key to watch the procedure execute one line at a time. You can actually see how code execution jumps over If...End If statements and how it goes around in circles in a loop. If the Locals window is open, you can watch variables appear and receive values just as they do when the code is really executing. It's code execution in super-slow motion, where you control the speed of things by tapping the F8 key.

If the code that you're debugging affects an open form in Access, you might notice the Access program window flash onscreen as the code executes. If you want to take a look at that open form — without losing your place in the VBA editor — just click the form's taskbar button or press Alt+F11 to switch back and forth between Access and the VBA editor.

Debug toolbar

Figure 12-9:

Checking out the locals in step mode.

Debug toolbar

Figure 12-9:

Checking out the locals in step mode.

Code suspended at breakpoint Locals window
0 0

Post a comment