Datasheet

ManualsBrandsWiley ManualsSoftware978-0-7645-5660-9

P1: IML/FFX P2: IML/FFX QC: IML/FFX T1: IML

WY010-01 WY010-Kingsley WY010-Kimmel-v1.cls June 10, 2004 22:23

Primer in Excel VBA

This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or

who are inexperienced with programming using the Visual Basic for Applications (VBA) language.

If you are already comfortable with navigating around the features provided by Excel, have used

the macro recorder, and have a working knowledge of VBA and the Visual Basic Editor (VBE), you

might want to skip straight to Chapter 3.

If this is not the case, this chapter has been designed to provide you with the information you need

to be able to move on comfortably to the more advanced features presented in the following

chapters. We will be covering the following topics:

❑

The Excel macro recorder

❑

User-deﬁned functions

❑

The Excel Object Model

❑

VBA programming concepts

Excel VBA is a programming application that allows you to use Visual Basic code to run the many

features of the Excel package, thereby allowing you to customize your Excel applications. Units of

VBA code are often referred to as macros. We will be covering more formal terminology in this

chapter, but we will continue to use the term macro as a general way to refer to any VBA code.

In your day-to-day use of Excel, if you carry out the same sequence of commands repetitively, you

can save a lot of time and effort by automating those steps using macros. If you are setting up an

application for other users, who don’t know much about Excel, you can use macros to create buttons

and dialog boxes to guide them through your application as well as automate the processes

involved.

If you are able to perform an operation manually, you can use the macro recorder to capture that

operation. This is a very quick and easy process and requires no prior knowledge of the VBA

language. Many Excel users record and run macros and feel no need to learn about VBA.

However, the recorded results might not be very ﬂexible, in that the macro can only be used to carry

out one particular task on one particular range of cells. In addition, the recorded macro is likely

COPYRIGHTED MATERIAL

Summary of content (62 pages)

PAGE 1
P2: IML/FFX QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 AL WY010-Kingsley RI Primer in Excel VBA MA TE This chapter is intended for those who are not familiar with Excel and the Excel macro recorder, or who are inexperienced with programming using the Visual Basic for Applications (VBA) language.
PAGE 2
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 to run much more slowly than the code written by someone with knowledge of VBA. To set up interactive macros that can adapt to change and also run quickly, and to take advantage of more advanced features of Excel such as customized dialog boxes, you need to learn about VBA. Don’t get the impression that we are dismissing the macro recorder.
PAGE 3
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA ❑ Next, think about when you want to start recording. In this case, you should include the selection of cell B1 in the recording, as you want to always have “Jan” in B1. If you don’t select B1 at the start, you will record typing “Jan” into the active cell, which could be anywhere when you play back the macro. ❑ Next, think about when you want to stop recording.
PAGE 4
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Call the macro MonthNames1, because we will create another version later. In the Shortcut key: box, you can type in a single letter. This key can be pressed later, while holding down the Ctrl key, to run the macro. We will use a lower case m. Alternatively, you can use an upper case M .
PAGE 5
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Figure 1-3 your macros, hold down the Ctrl key and press the Break key to interrupt the macro. You can then end the macro or go into debug mode to trace errors. You can also interrupt a macro with the Esc key, but it is not as effective as Ctrl+Break for a macro that is pausing for input.
PAGE 6
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Figure 1-4 The same dialog box can be opened by pressing the Run Macro button on the Visual Basic toolbar, as shown in Figure 1-5. Figure 1-5 Shortcut Keys You can change the shortcut key assigned to a macro by first bringing up the Macro dialog box, by using Tools ➪ Macro ➪ Macros, or the Run Macro button on the Visual Basic toolbar. Select the macro name and press Options.
PAGE 7
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA It is possible to assign the same shortcut key to more than one macro in the same workbook using this dialog box (although the dialog box that appears when you start, the macro recorder will not let you assign a shortcut that is already in use). It is also quite likely that two different workbooks could contain macros with the same shortcut key assigned.
PAGE 8
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 If you needed to resynchronize the Stop Recording toolbar using the instructions above, upper case M will already be assigned. If you have difficulties assigning the uppercase M shortcut to MonthNames2 on the second recording, use another key such as uppercase N , and change it back to M after finishing the recording. Use Tools ➪ Macro ➪ Macros . . .
PAGE 9
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA First, you can run VBE by pressing the Visual Basic Editor button on the Visual Basic toolbar. You can also activate it by holding down the Alt key and pressing the F11 key. Alt+F11 acts as a toggle, taking you between the Excel Window and the VBE window. If you want to edit a specific macro, you can use Tools ➪ Macro ➪ Macros . . .
PAGE 10
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Procedures In VBA, macros are referred to as procedures. There are two types of procedures—subroutines and functions. You will find out about functions in the next section. The macro recorder can only produce subroutines. You can see the MonthNames1 subroutine set up by the recorder in the above screenshot.
PAGE 11
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA the Project Explorer. Right-click anywhere in the Project Explorer window and click Insert to add a new standard module, class module, or UserForm. To remove Module1, right-click it and choose Remove Module1 . . . . Note that you can’t do this with the modules associated with workbook or worksheet objects.
PAGE 12
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 There are many other objects that you can attach macros to, including combo boxes, list boxes, scrollbars, check boxes and option buttons. These are all referred to as controls. See Chapter 10 for more information on controls. You can also attach macros to graphic objects in the worksheet, such as shapes created with the Drawing toolbar.
PAGE 13
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Figure 1-11 Figure 1-12 When you draw your button in the worksheet, you enter into the design mode. When you are in the design mode, you can select a control with a left-click and edit it. You must turn off the design mode if you want the new control to respond to events.
PAGE 14
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Figure 1-13 Figure 1-14 To change the text on the command button, change the Caption property. You can also set the font for the caption and the foreground and background colors. If you want the button to work satisfactorily in Excel 97, it is a good idea to change the TakeFocusOnClick property from its default value of True to False.
PAGE 15
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.
PAGE 16
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Staying in the Customize dialog box, click the Commands tab and select the Macros category. Drag the Custom Button with the smiley face icon to the new toolbar, or an existing toolbar. Next, either click the Modify-Selection button, or right-click the new toolbar button to get a shortcut menu. Select Assign Macro . . .
PAGE 17
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA of a worksheet. Versions of Excel since Excel 97 expose a wide range of events for which we can write code. The click-event procedure for the ActiveX command button that ran the MonthNames2 macro, which we have already seen, is a good example. We entered the code for this event procedure in the code module behind the worksheet where the command button was embedded.
PAGE 18
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Private Sub Workbook_BeforeClose(Cancel As Boolean) If ThisWorkbook.Sheets("Sheet1").Range("A1").Value <> True _ Then Cancel = True End Sub This code even prevents the closure of the Excel window.
PAGE 19
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Here, we have created a function called CentigradeToFahrenheit() that converts degrees Centigrade to degrees Fahrenheit. In the worksheet we could have column A containing degrees Centigrade, and column B using the CentigradeToFahrenheit() function to calculate the corresponding temperature in degrees Fahrenheit.
PAGE 20
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Figure 1-20 The InvoiceAmount() function has three input parameters: Product is the name of the product; Volume is the number of units sold, and Table is the lookup table.
PAGE 21
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA return the corresponding value from the second column of the lookup table, which it assigns to the variable Price. If you want to use an Excel worksheet function in a VBA procedure, you need to tell VBA where to find it by preceding the function name with WorksheetFunction and a period.
PAGE 22
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 be recalculated if you change a value in the lookup table. Excel does not realize that it needs to recalculate the function when a lookup table value changes, as it does not see that the table is used by the function. Excel only recalculates a UDF when it sees its input parameters change.
PAGE 23
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA application, you need to learn about the objects it contains. In Word, you deal with documents, paragraphs, and words. In Access, you deal with databases, record sets, and fields. In Excel, you deal with workbooks, worksheets, and ranges. Unlike many programming languages, you don’t have to create your own objects in Office VBA.
PAGE 24
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Figure 1-21 If you want to refer to a member of a collection, you can refer to it by its position in the collection, as an index number starting with 1, or by its name, as quoted text. If you have opened just one workbook called Data.xls, you can refer to it by either of the following: Workbooks(1) Workbooks("Data.
PAGE 25
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA The preceding code is understood to mean that Workbooks("Sales.xls") returns an instance of the Workbook class. The . Worksheets("DataInput") part returns a single Worksheet, and the .Range("B2") part returns a single range. As you might have intuitively guessed, we are getting one Workbook, one Worksheet from that Workbook, and a specific Range.
PAGE 26
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 You refer to the property of an object by referring to the object, then the property, separated by a period (the member-of operator). For example, to change the width of the column containing the active cell to 20 points, you would assign the value to the ColumnWidth property of the ActiveCell using ActiveCell.
PAGE 27
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA property. The Text property of the cell gives you the formatted appearance of the cell. The following example displays $12,345.60 in a Message box: Range("B10").Value = 12345.6 Range("B10").NumberFormat = "$#,##0.00" MsgBox Range("B10").Text This is the only means by which we can set the value of the Text property.
PAGE 28
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 This is far more efficient than the code produced by the macro recorder: Range("A1:B3").Select Selection.Copy Range("G4").Select ActiveSheet.Paste Events Another important concept in VBA is that objects can respond to events. A mouse click on a command button, a double-click on a cell, a recalculation of a worksheet, and the opening and closing of a workbook are examples of events.
PAGE 29
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA worksheets cells to no color, to remove any existing background color. The second and third statements set the entire columns and entire rows that intersect with the selected cells to a background color of pale yellow. The predefined color xlColorIndexNone can yield a color other than a creamy yellow if you have modified Excel’s color palette.
PAGE 30
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 View ➪ Object Browser, press F2, or click the Object Browser button on the Standard toolbar to see the window shown in Figure 1-22. Figure 1-22 The objects are listed in the window with the title Classes. You can click in this window and type an r to get quickly to the Range object.
PAGE 31
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Figure 1-23 Immediate window visible. You can tile the Excel window and the VBE window so that you can type commands into the Immediate window and see the effects in the Excel window as shown in Figure 1-24. Figure 1-24 When a command is typed in the Immediate window (see lower right corner of Figure 1-24), and Enter is pressed, the command is immediately executed.
PAGE 32
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Here, the Value property of the ActiveCell object has been assigned the text “Sales”. If you want to display a value, you precede the code with a question mark, which is a shortcut for Print: ?Range("B2").Value ` is equivalent to Print Range("B2").Value This code has printed “Sales” on the next line of the Immediate window. The last command has copied the value in B2 to J2.
PAGE 33
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA An easy way to execute a sub procedure is to click somewhere in the code to create an insertion point, then press F5. MsgBox has many options that control the type of buttons and icons that appear in the dialog box. If you want to get help on this, or any VBA word, just click somewhere within the word and press the F1 key.
PAGE 34
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.
PAGE 35
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Figure 1-27 Values 16 to 64 control the icons that appear (see Figure 1-28). 32 gives a question mark icon. If we wanted both value 4 and value 32, we add them: MsgBox Prompt:="Delete this record?", Buttons:=36 Figure 1-28 Constants Specifying a Buttons value of 36 ensures that our code is indecipherable to all but the most battle-hardened programmer.
PAGE 36
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 rather than a function. This is perfectly legal, but we need to know some rules if we are to avoid syntax errors. We can capture the return value of the MsgBox function by assigning it to a variable.
PAGE 37
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA InputBox Another useful VBA function is InputBox, which allows us to get input data from a user in the form of text. The following code generates the dialog box shown in Figure 1-29. UserName = InputBox(Prompt:="Please enter your name") Figure 1-29 InputBox returns a text (string) result. Even if a numeric value is entered, the result is returned as text.
PAGE 38
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 If (SalesData <> "") Then Call PostInput(SalesData, "B3") End If End Sub Function GetSalesData() GetSalesData = InputBox("Enter Sales Data") End Function Sub PostInput(InputData, Target) Range(Target).Value = InputData End Sub Master uses the GetSalesData function and the PostInput sub procedure.
PAGE 39
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA statement or one of its variations, which we will see shortly. The following Dim statement declares a variable called SalesData: Sub GetData() Dim SalesData As String SalesData = InputBox(Prompt:="Enter Target Sales") ... Implicit variable declaration is supported but should be avoided. Your code should be as explicit as possible.
PAGE 40
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 at the top of the module and, therefore, implicit declaration is used: Sub GetData() SalesData = InputBox(Prompt:="Enter Target Sales") If SaleData = "" Then Exit Sub Range("B2").Value = SalesData End Sub This code will never enter any data into cell B2.
PAGE 41
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA it is defined. We can increase the lifetime of Sales by declaring it in a Static statement: Sub LifeTime() Static Sales As Integer Sales = Sales + 1 MsgBox Sales End Sub The lifetime of Sales is now extended to the time that the workbook is open. The more times LifeTime is run, the higher the value of Sales will become.
PAGE 42
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 If a procedure in the module declares a variable with the same name as a module-level variable, the module-level variable will no longer be visible to that procedure. It will process its own procedure-level variable. Module-level variables, declared in the declarations section of the module with a Dim statement, are not visible to other modules.
PAGE 43
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Data type Storage size Range Decimal 14 bytes +/−79,228,162,514,264,337,593,543,950,335 with no decimal point; +/−7.9228162514264337593543950335 with 28 places to the right of the decimal; the smallest non-zero number is +/−0.
PAGE 44
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 The following can be a trap: Dim Col, Row, Sheet As Integer Many users assume that this declares each variable to be Integer. This is not true. Col and Row are Variants because they have not been given a type.
PAGE 45
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA (A strongly typed language is a language whose compiler makes a careful distinction between the types of the arguments declared and the types actually passed to methods.
PAGE 46
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 This example creates a new empty workbook and assigns a reference to it to the object variable aWorkbook. A new worksheet is added to the workbook, after any existing sheets, and a reference to the new worksheet is assigned to the object variable aWorksheet.
PAGE 47
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA If you find this confusing, you can compromise with a combination of object variables and With...End With: Sub NewWorkbook4() Dim aWorkbook As Workbook, aWorksheet As Worksheet Set aWorkbook = Workbooks.Add With aWorkbook Set aWorksheet = .Worksheets.Add(After:=.Sheets(.Sheets.Count)) With aWorksheet .Name = "January" .Range("A1").Value = "Sales Data" End With .
PAGE 48
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.
PAGE 49
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA If Answer = vbYes Then ThisWorkbook.Save MsgBox ThisWorkbook.Name & " has been saved" End If End If This code uses the Saved property of the Workbook object containing the code to see if the workbook has been saved since changes were last made to it. If changes have not been saved, the user is asked if they want to save changes.
PAGE 50
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Case "Pears": Price = 18 Case "Mangoes": Price = 25 Case Else: Price = CVErr(xlErrNA) End Select End Function Select Case can also handle ranges of numbers or text, as well as comparisons using the keyword Is. The following example calculates a fare of zero for infants up to 3 years old and anyone older than 65, with two ranges between.
PAGE 51
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Do . . . Loop To illustrate the use of a Do...Loop, we will construct a sub procedure to shade every second line of a worksheet, as shown in Figure 1-31, to make it more readable.
PAGE 52
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 can, therefore, change any reference to the active cell to a Cells(i,1) reference and apply the EntireRow property to Cells(i,1) to refer to the complete row: Public Sub ShadeEverySecondRow() Dim i As Integer i = 2 Do Until IsEmpty(Cells(i, 1)) ` use different color for contrast Cells(i, 1).EntireRow.Interior.
PAGE 53
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA MsgBox "Sorry, Only three tries" Exit Sub End If PassWord = InputBox("Enter Password") Loop Until PassWord = "XXX" MsgBox "Welcome" End Sub GetPassword loops until the password XXX is supplied, or the number of times around the loop exceeds three. For . . . Next Loop The For...Next loop differs from the Do...Loop in two ways.
PAGE 54
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 FilePath = ActiveWorkbook.FullName For i = Len(FilePath) To 1 Step -1 If Mid$(FilePath, i, 1) = ".
PAGE 55
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Public Sub FileList() Dim File As Variant With Application.FileSearch .LookIn = "C:\" .FileType = msoFileTypeAllFiles .Execute For Each File In .FoundFiles MsgBox File Next File End With End Sub If you test this procedure on a directory with lots of files, and get tired of clicking OK, remember that you can break out of the code with Ctrl+Break.
PAGE 56
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 Message = Message & "Upper Bound = " & UBound(Data) & vbCr Message = Message & "Number of Elements = " & WorksheetFunction. Count(Data) _ & vbCr Message = Message & "Sum of Elements = " & WorksheetFunction.Sum(Data) MsgBox Message End Sub Array1 uses the LBound (lower bound) and UBound (upper bound) functions to determine the lowest and highest index values for the array.
PAGE 57
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA You can think of a two-dimensional array as a table of data. The previous example defines a table with 10 rows and 20 columns. Arrays are very useful in Excel for processing the data in worksheet ranges. It can be far more efficient to load the values in a range into an array, process the data, and write it back to the worksheet, than to access each cell individually.
PAGE 58
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 You can declare the required size at runtime with a ReDim statement, which can use variables to define the bounds of the indexes: ReDim Data(iRows, iColumns) As String ReDim Data(minRow to maxRow, minCol to maxCol) As String ReDim will reinitialize the array and destroy any data in it, unless you use the Preserve keyword. Preserve is used in the following procedure that uses a Do...
PAGE 59
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA If you anticipate this particular problem, you can set up your code to gracefully deal with the situation. VBA allows you to trap error conditions using the following statement: On Error GoTo LineLabel LineLabel is a marker that you insert at the end of your normal code, as shown below with the line label errorTrap. Note that a colon follows the line label.
PAGE 60
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 The code in ErrorTrap1, after executing the On Error statement, saves the current directory drive and path into the variable CurrentPath. It then executes the ChDrive statement to try to activate the A drive. If there is no disk in the A drive, error 68 (Device unavailable) occurs and the error recovery code executes.
PAGE 61
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Primer in Excel VBA Message = Message & "File does not exist" Answer = MsgBox(Message, vbInformation, "Error") Resume Next End Sub On Error Resume Next As an alternative to On Error GoTo, you can use: On Error Resume Next This statement causes errors to be ignored, so it should be used with caution. However, it has many uses.
PAGE 62
P1: IML/FFX WY010-01 P2: IML/FFX WY010-Kingsley QC: IML/FFX T1: IML WY010-Kimmel-v1.cls June 10, 2004 22:23 Chapter 1 TestForName calls the NameExists function, which uses On Error Resume Next to prevent a fatal error when it tries to assign the name’s RefersTo property to a variable. There is no need for On Error GoTo 0 here, because error handling in a procedure is disabled when a procedure exits, although Err.Number is not cleared.