Page 54 - Excel for Scientists and Engineers: Numerical Methods

CHAPTER 2 FUNDAMENTALS OF PROGRAMMING WITH VBA 31

Note the difference between identical expressions with and without the use
of the Set keyword. In the expression
XValues = Workbooks("Book1 ").Worksheets("Sheet3").Range("E2:E32")

the variable XValues contains only the values in cells E2:E32, while the
expression
Set MyRange = Workbooks("Book1 ").Worksheets("Sheet3").Range("E2: E32")
creates an object variable MyRange, a Range object that allows you to read (or
set) any of the properties of this object. For example, in addition to the value of
any cell in the range E2:E32, you can obtain its number format, column width,
row height, font and so on.
Remember, VBA will allow you to equate a variable to an object in an
assignment statement, but the variable does not automatically become an object.
If you then attempt to use the variable in an expression that requires an object,
you'll get an "Object required" error message. You must use the Set keyword in
order to create an object variable.

Methods
Objects also have methods. The Excel 2003 VBA Help lists 71 methods,
listed below, that apply to the Range object. Many of these methods correspond
to familiar menu commands.

Activate ClearNotes FindNext RowDifferences
Addcomment ClearOutline Find Previous Run
AdvancedFilter ColumnDifferences Functionwizard Select
ApplyNarnes Consolidate GoalSee k Setphonetic
ApplyOutlineStyles COPY Group Show
AutoComplete CopyFrom Recordset Insert ShowDependents
AutoFill CopyPicture lnsertlndent ShowErrors
AutoFilter CreateNames Justify S howprecedents
AutoFit cut ListNames sort
AutoFormat Dataseries Merge Sortspecial
Autooutline Delete NavigateArrow Speak
BorderAround Dialog Box NoteText SpecialCelts
Calculate Dirty Parse Subtotal
Checks pelling FillDown Pastespecial Table
Clear FillLeft Printout TextToColumns
ClearComments FillRight Printpreview Ungroup
Clearcontents FillUp RemoveSu btotal UnMerge
ClearFormats Find Replace

Some Useful Methods
Methods can operate on an object or on a property of an object. Some
methods that can be applied to the Range object are the Copy method, the Cut
method, the FillDown method or the Sort method. Statements involving