Page 60 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRJPMMING WITH VBA 37

variablename = Range("A' & x)

variablename = Cells(StartRow+x,StartCol)
The corresponding Formula properly is used to obtain the formula in a cell,
rather than its value.

Sending Values to a Worksheet
To send values from a module sheet back to a worksheet, simply use an
assignment statement like the ones shown in the following examples. You can
send a label
Range("EI").Value = "Jan.-Mar.''

a constant
Cells(1, 2).Value = 5

the value of a variable
=
Worksheets("Sheetl").Range("Al") variable2
or even a worksheet formula
Cells(1, 3).Formula = "=sum(Fl:FlO)"

to a cell in a worksheet. Again, the .Value keyword can usually be omitted.

Interacting with the User
VBA provides two built-in dialog boxes for display of messages or for input,
MsgBox and InputBox. These are often incorporated in Sub procedures; they
should never be used in Function procedures.
MsgBox

The MsgBox dialog box allows you to display a message, such as "Please
wait.. .I' or "Access denied." The box can display one of four message icons, and
there are many possibilities in the number and function of buttons that can be
displayed.
The syntax of the MsgBox function is

MsgBox (prompt-text, buttons, title-text, helpfile, context)
where prompt-text is the message displayed within the box, buttons specifies the
buttons to be displayed, and title-text is the title to be displayed in the Title Bar
of the box. For information about helpfile and context, refer to Microsoft Excel
Visual Basic Reference. The value of buttons determines the type of message