Watching the Execution of Your VBA Procedures

To help you understand what's going on when your code runs and how your custom object works, let's step through the cmdSave_Click procedure.

Treat this exercise as a brief introduction to the debugging techniques that are covered in detail in Chapter 13.

1. In the Project Explorer window, select the Salaries form and click the View Code button at the top of this window.

2. When the Salaries (Code) window appears, select the cmdSave procedure from the combo box at the top left-hand side of the Code window.

3. Set a breakpoint by clicking in the left margin next to the following line of code:

If txtLastName.Value = "" Or txtFirstName.Value = "" Or _ txtSalary.Value = "" Then

fl chap11.xls - Salaries (Code]


jcmdSave [click


Private Sub cmdSave ClickQ

1 It txtLastName.Value = " Or txtFirstName. Value = " Or txtSalary . Value =

" Ihpn^^Bi

MsgBox "Enter Last Name, First Name and Salary." txtLastName.SetFocus

Exit Sub

End If

If Not 1 sN urn eric(txtS alary) Then

MsgBox "You must enter a value for the Salary." txtSalary. SetFocus

Exit Sub End If


If txtSalary <0 Then MsgBox "Salary cannot be a negative number" Exit Sub

End If

Works he ets ("S ala rie s"), S ele ct index = Act iveSheet. Used Range Rows, Count +1

IboxPeople, Enabled = True



S. < I. I

Figure 11-6: A red circle in the margin indicates a breakpoint. When VBA encounters the statement with a breakpoint, it automatically switches to the Code window and displays the text of the line as white on a red background.

4. In the Project Explorer window, highlight the WorkAndPay module and click the View Code button.

5. Place the cursor anywhere inside the ClassDemo procedure and press F5, or choose Run | Run Sub/UserForm.

6. When the form appears, enter data in the Last Name, First Name, and Salary text boxes, and click the form's Save button. Visual Basic should now switch to the Code window since it encountered the breakpoint in the first line of the cmdSave_Click procedure.

I fp chapl 1 .xls - Salaries (Code)



yj J Clich


Private Sub omdSave_Click()

|lf titLastName Value = " Or IMF IrstName Value = " Di [«Salary.Value = " Tlien

Msg Bo* "Enter Last Warns, First Name and Salary." txtLast N arn e. SetF oc us Exit Sub End If

If Not I sW time rlc(txtS alary) Then

MsgBox "You musl enter a value for the Salary." txtSalary.SelFocus

Exit Sub End If

H «Salary <0Then

Msg Bo* "Salary cannot be a negative number." Exit Sub End if

Works he el s(" Sa la ria s"). Select Index = Active She el. Used Range. Rows. Count +1 IboxPeopls. Enabled = True

Figure 11-7: When Visual Basic encounters a breakpoint while running a procedure, it switches to the Code window and displays a yellow arrow in the margin to the left of the statement at which the procedure is suspended.

7. Step through the code one statement at a time by pressing F8.

Visual Basic runs the current statement and then automatically advances to the next statement and suspends execution. The current statement is indicated by a yellow arrow in the margin and a yellow background. Keep pressing F8 to execute the procedure step by step. When Visual Basic encounters the With emp statement, it will switch to the Class_Initialize procedure.

1 M chap11.xls - CEmployee (Code)


|cia&* TI [initialize

Case 1 1 by percent "

CalcNewSatary = curSatary + ((curSalary + amount) /1Q0)~ Case 2 " by amount CalcNawSafajy - turSaiary + amaunt End Sal act Er>d Function

Case 1 1 by percent "

CalcNewSatary = curSatary + ((curSalary + amount) /1Q0)~ Case 2 " by amount CalcNawSafajy - turSaiary + amaunt End Sal act Er>d Function private Sub ClassJnittalizeQ Randomize mjd = liK®9090 - 10000) *Rnd + 10000) End S üb

Figure 11-8: When Visual Basic encounters the reference to the object variable emp, it goes out to execute the Class_Initialize procedure. After executing the statements inside this procedure, VBA returns to the cmdSave_Click procedure.

When Visual Basic encounters the statement Cells(Index, 1).Formula emp.lD, it will go out to execute the Property Get Id procedure in the CEmployee class module.

Microsoft Visual Basic - chopH.xU (break] - [CEmployee (Code)]

Project ObjCoiCtoj

Z ObjColClass (chap 11.wis)

B Microsoft Excel Objects if) Prkiszl (Salaries) SÎ) Sheetl (Sheetl) if) Sheetz (5heet2) Cl 3heet3 (5heet3) If] S£ieet4 (Sheet4) ® This Workbook B~& Forms d Salaries B Ö Modules

¿1 Module 1 MyColecbon WorkAndPay S ifl Class Modules 3 Œtnployee

AlphätoeBc I Categorized ]

(CEmptoyee jlnstanünq 1 - Private

Debug Run Tools Add-lna Window Help - 5 x

T I J Id [PropirtyGet]

Option Explicit 'declarations

Private m_LastName As String Private m_FirstName As String Private m_Salary As Currency Private mjd As String

Property Get IdQ As String

Id = mjd End Property_

Property Get LastNameQ As String

LastNarne = mJ_astName End Property_

Property Get FirstNameQ As String

FirstName = m_FirstName End Property_

Property Get Salary Q As Currency


Figure 11-9: Reading properties of your custom object is accomplished through the Property Get procedures.

8. Using the F8 key, trace the execution of the cmdSave_Click procedure to the end.

When VBA encounters the end of the procedure (End Sub), the yellow highlighter will be turned off. At this time, click the Microsoft Excel button on the Windows taskbar at the bottom of the screen to return to the active form. Enter data for a new employee, and click the Save button. When Visual Basic displays the Code window, choose Debug | Clear All Breakpoints. Now press F5 to run the rest of the procedure without stepping through it.

Tip 11-6: VBA Debugging Tools

Visual Basic provides a number of debugging tools to help you analyze how your application operates, as well as locate the source of errors in your procedures. See Chapter 13 for details on working with these tools.

0 0

Post a comment