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, we 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 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, make sure that each individual piece is doing exactly what you think it's doing.

To write the PCase() function, we started out by just writing this bit of code:

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, we typed ? PCase("MARVIN DODoskY") into the Immediate window and pressed Enter. When we did, the Debug.Print statement in the code displayed the following line in the Immediate window:

PCase = Marvin Dodosky

To test it again, we typed ? PCase("123 OAK TREE LANE") into the Immediate window and got back PCase = 123 Oak Tree Lane. At this point, we knew that the basic problem — converting the first letter of each word to uppercase and making all other letters lowercase — 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 the result showed us — the programmers — 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, we knew that the basic problem of capitalizing the first letter of each word was solved, so we could then move on to writing code to solve the next problem.

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

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

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 our progress, we typed? PCase("MCDONALD") into the Immediate window and pressed Enter. The Immediate window showed PCase = McDdonald. Oops — that should have been McDonald — not McDdonald (with three d's rather than two). This is a logical error in the sense that the code ran without generating any error messages. The problem lies in the logic of how we handled the problem.

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

& Mid(PCase,3) — was wrong: It should have been Mid(PCase,4). So we changed the code, as shown here:

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, we tested the procedure by entering ? PCase("MCDONALD") into the Immediate window. We got back

PCase = McDonald in the Immediate window. Now we knew that the Mc problem was solved. On to the next problem — dealing with the Mac last names. From there on out, it was more of the same. We wrote a little code, tested our progress, and fixed any problems that we discovered. By making sure that each piece of the puzzle worked at each step in the process, we could finally create a custom function that did what we 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 our progress, fixing all compile and logical errors, and then moving on to the next problem. Basically, we 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, we 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 our progress at this point, we typed ? NumWord(1234.56) into the Immediate window and then pressed Enter. The Immediate window returned this result:

AmountPassed = 1234.56 strNum = 000001234.56

We could then see what we really had to work with before writing more code. Also, we knew that because strNum is a string, we 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 of a number was being translated to English.

Knowing that we had AmountPassed and strNum to work with, we then wrote a little more code for the procedure, tested the code, 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 system to work out any compile errors, test the code, fix any errors in logic, and then move on to the next bit of code.

If we had 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 have been anywhere in the code. By writing a little, testing a little, and debugging a little along the way, we were gradually able to solve all the problems and come up with a procedure that works.

The only purpose of using Debug.Print in the code is to give yourself some feedback about 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 aren't of any value to the users of your custom function. Debug.Print statements serve only as a programmer's debugging tool.

Testing Function and Sub procedures

The syntax for calling a Sub procedure from code, as well as from the Immediate window, is different from the syntax for calling a function. To run a Sub 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 displays the value returned by your function.

Slowing down code

Using Debug.Print in code to get a little feedback about what's happening in your procedure is helpful, but when you run the procedure, it still executes in an eyeblink. 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 make the code run in slow motion; rather, it forces the VBA Editor to suspend execution of the code at that point so that you can explore the values of variables or whatever in the Immediate window.

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:

• Right-click the line and choose ToggleOBreakpoint from the shortcut menu.

• Choose DebugOToggle Breakpoint from the VBA Editor menu bar.

• Click in the gray area on the left side of the code window next to the line where you want to suspend the code's execution.

a • 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 executes at its usual blazing speed until execution reaches the line that you defined as a breakpoint. Rather than get compiled and executed, the breakpoint line of code gains a bright yellow highlighter in the Code window and doesn't execute until you press F8. Basically, you can then 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 earlier in this chapter, you want to take a look at all your variables just before the line that reads strNum = Format(AmountPassed, "000000000.00 ") executes. In the Code window, you would right-click that line and choose ToggleOBreakpoint. Then you would 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 you wanted to take a quick look at some variable defined in the code before the breakpoint line executes, you could ask the Immediate window by typing ? variableName. For example, if you enter ? strNum into the Immediate window while the code is suspended, you get nothing in return because strNum is empty before the breakpoint line executes. Still, getting nothing in response to the ? strNum expression is confusing. The following section presents 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 opens, showing the name, value, and data type of every variable defined to that point in your code. Figure 12-9 shows an example where we opened the Locals window while code was suspended in the sample NumWord() procedure. There you can see the name, value, and data type of every variable that exists in NumWord() just before the breakpoint line executes.

Debug toolbar

Immediate window

Figure 12-9:

Checking out the locals in step mode.

Debug toolbar

Immediate window

Figure 12-9:

Checking out the locals in step mode.

Debug Fenster Access 2007

Code suspended at breakpoint

Locals window

Code suspended at breakpoint

Locals window

Moving and sizing editor windows

As with 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 Locals window back into hiding, click the Close (X) button on its title bar.

To get the Immediate and Locals windows to stack up as they're shown in Figure 12-9, first make sure that both windows 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 to the 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.

In the Locals window, you can easily see the strNum variable that you previously checked on in the Immediate window. The Locals window lets you know that the variable contains "123456789.00 " 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.

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

Stepping through code in step mode

Suppose that you set your breakpoint, code execution stops at the breakpoint line, and 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 highlighted line of code (only) or step through a procedure being called by the current line: Press F8 or choose DebugOStep Into.

^ To execute the highlighted line of code and not step through another procedure being called by the current line: 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 DebugOStep Out.

The best way to use step mode (as a beginner, anyway) is to just press F8 to execute the selected line of code. When you do so, the line executes. Any changes to variables made by executed statements appear in the Locals window.

Even better, you can sit there and just tap the F8 key to watch the procedure execute one line at a time. You can 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 superslow 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 on-screen 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 Access taskbar button or press Alt+F11 to switch back and forth between Access and the VBA Editor.

Getting back to normal in the Code window

When you finish debugging or just want to start over with a clean slate, do one of the following:

1 To get out of step mode: Press Ctrl+Shift+F8 or choose DebugOStep Out. Code execution continues, and things go back to normal.

1 To remove a breakpoint: Right-click the line and choose ToggleO Breakpoint from the shortcut menu.

1 To clear all breakpoints from your code: Choose DebugOClear All Breakpoints.

1 To clear the Locals window of its value: Right-click any text within the window and choose the Reset option from the shortcut menu.

Closing the VBA Editor window also terminates step mode. For instance, if you choose FileOClose and Return to Microsoft Office Access while in step mode, you see the prompt This command will stop the debugger (see Figure 12-10). If you click OK, the Visual Basic Editor then closes, step mode is terminated, and you return to the Access program window. (Clicking Cancel closes the dialog box without doing anything to the code. Clicking Help shows some confusing information about the dialog box.)

Figure 12-10:

Exit options when closing in step mode.

Figure 12-10:

Exit options when closing in step mode.

Microsoft Visual Basic

This command will stop the debugger,




Was this article helpful?

0 0


  • arianna greece
    How to convert first letter to uppercase in access 2007?
    8 years ago
  • jose
    How do i see what's in a variable in vba debug?
    8 years ago

Post a comment