Page 40 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 17

To avoid inadvertently using a VBA keyword as a variable name (there are
hundreds of VBA keywords, so this is easy to do), I suggest that you type the
variable name in all lowercase letters. If the variable name becomes capitalized,
this indicates that it is a reserved word. For example, you may decide to use FV
as a variable name. If you type the variable name "fv" in a VBA statement, then
press Enter, you will see the variable become "FV," a sign to you that FV is a
reserved word in VBA (the FV function calculates the future value of an annuity
based on periodic, fixed payments and a fixed interest rate.)
In fact, it's also a good idea to type words that you know are reserved words
in VBA in lowercase also. If you type "activecell," the word will become
"ActiveCell" when you press the Enter key. If it doesn't, you have typed it
incorrectly.

Objects, Properties and Methods
VBA is an object-oriented programming language. Objects in Microsoft
Excel are the familiar components of Excel, such as a worksheet, a chart, a
toolbar, or a range. Objects have properties and methods associated with them.
Objects are the nouns of the VBA language, properties are the adjectives that
modify the nouns and methods are the verbs (the action words). Objects are
used almost exclusively in Sub procedures, while properties and some methods
can be used in Function procedures. A discussion of objects and methods can
be found in the section "VBA Code for Command Macros" later in this chapter.

Objects
Some examples of VBA objects are the Workbook object, the Worksheet
object, the Chart object and the Range object. It's very unlikely that a custom
function would include any of these keywords. But if a custom function takes as
an argument a cell or range of cells, the argument is a Range object and has all
of the properties of a Range object.

Properties
Objects have properties that can be set or read. Some properties of the
Range object are the ColumnWidth property, the NumberFormat property, the
Font property and the Value property. A property is connected to the object it
modifies by a period, for example
CelFmt = Range("E5").NumberFormat

returns the number format of cell E5 and assigns it to the variable CelFmt, and
Range("ES').NumberForrnat = "0.000"

sets the number formatting of cell E5.