Thinking Like a Computer Revisited

At the beginning of the chapter, I talked about a simple program that would loop through the items on a worksheet list and highlight any item that begins with 'A' in bold font. You're now familiar enough with the vocabulary of VBA that you can implement this program using terms your computer can understand, as shown in Listing 3.4. The part that I haven't covered yet is how to use Excel's Workbook object and Range object. I'll cover these in Chapters 7 and 8, respectively. However, I think you'll find that using these objects is pretty straightforward for the purposes of this easy exercise.

Listing 3.4: Simple List Processing

Sub SimpleListProcessingO ' Declare our varibles Dim wb As Workbook Dim rg As Range

' Initialize our variables

Set wb = Workbooks.Open("C:\Examples\List Example.xls") Set rg = wb.Worksheets("Sheet1").Range("B3")

' Loop through cells moving down one cell ' until an empty cell is found Do Until IsEmpty(rg)

If Left(rg.Value, 1) = "A" Then rg.Font.Bold = True Set rg = rg.Offset(1, 0)


' Dereference object variables Set rg = Nothing Set wb = Nothing End Sub

As in Listing 32, this procedure has four logical sections. Many procedures follow this four-section grouping, but there is no rule or guideline to follow regarding the organization of your procedures. You may find that you prefer to organize your procedures differently. The high-level organization is to declare variables, initialize variables, implement procedure logic, and dereference object variables. This procedure uses two object variables: the Workbook object and the Range object. I'll cover these objects in great detail booking Chapters 6 and 8, respectively. As you'd expect, the Workbook object has an Open method that allows VBA code to open workbooks. Note that this listing assumes that the list example workbook is in the folder C:\Examples. If you try this procedure, be sure to either copy the list example workbook to this location or modify this line appropriately.

Your Do.. .Loop relies on the IsEmpty function to determine if an empty cell has been reached. Once an empty cell is found, the Do.. .Loop terminates. Inside the loop, you can use the Left function to return the first character of the value found in the current cell. If the first character is "A", then you use bold font in the cell. To move to the next cell you employ the Offset method of the Range object. Finally, in the last section, you dereference your object variables and formally end the subroutine.

Hopefully what you take away from this is that although you can't just say, "make any cell that begins with the letter 'A' bold," it is fairly straightforward to accomplish this.

0 0

Post a comment