Page 129 - Microsoft Office Excel 2003 Programming Inside Out

Creating Sub and Function Procedures

Inside Out

Protecting Your Original Cell Data
One of the dangers of programming is that you can inadvertently change the original values
in your worksheet. For example, if you create a Sub procedure that assigns some value to
the active cell, you’ll end up destroying your original data. So, in addition to always creating
backup copies of all your data, you can consider using the ByVal keyword to have the pro
cedure use a copy of the data and not the original cell value (or array, or object, or whatever)
itself. A Sub procedure to calculate the number of small bags of soil to be created from a
number of large bags would be written the following way:

Sub SmallBags(byVal intLargeBags)
MsgBox("The number of large bags is " & intLargeBags * 5).
End Sub

Passing Named Arguments
All the procedures in this chapter that have called a procedure have passed the arguments the
procedure requires in an order the procedure expects. For example, when you type MsgBox
to begin a statement to create a message box, the Visual Basic Editor displays a ToolTip indi­
cating the expected arguments, as shown in Figure 5-5.

Chapter 5

Figure 5-5. The Visual Basic Editor helps you create effective procedures by listing the
expected arguments.

103
Part 2: Visual Basic for Applications