<<

. 58
( 62 .)



>>


A range
u


You will notice that this listing gives a range from large
objects to small objects. This is the object hierarchy in Excel (and
this hierarchy is called the object model in Excel, just to add to the
arcania of your Excel knowledge). There are 128 objects in Excel.
Don™t worry. You need to know just a handful to start finding
your way around VBA.




TLFeBOOK
Chapter 19
320




PROPERTY AND METHOD
Once you have defined the object you want to manipulate,
then you have a choice of working with the property or the
method. A good way to remember these terms is that a property
describes what an object is, and a method describes what you do
with it.


Property
Every object has a property, property in this case being the
standard definition of an ˜˜essential or defining attribute.™™ If the
range that you are looking at contains the number 170853 in
italics, then you can say that the range has the Value property
of 170853 and the Format property of italics (or, more specifically,
the Font Italic property is TRUE). A property always has a value,
whether it is a string value, a numeric value, a Boolean value, or
some other value.


Method
A method is an action that is performed with an object. Copying
the range to another range is an example of the copy method.
(Thus, this action has the effect of changing the property of the
destination cell.)
If you are wondering how to tell a command for property
and a command for method part, the property command has
an equal (=) sign by itself. You may see an equal sign in a
method command, but you will see this with a colon (˜˜:=™™)
when the method has to show arguments that are part of the
method command.


FOLLOW THE HIERARCHY
In a VBA macro, whether you want to apply a property or a
method to an object, the way to do it is to start identifying it
from within the hierarchy, from the largest object to the smallest.
Most of the time, you do not have to specify the object that is




TLFeBOOK
Writing a Macro in Visual Basic for Applications 321




Excel itself (this is called the Application object), and you can
start with the workbook. In the fullest approach, you would
define it in this order:
The workbook
u

The worksheet
u

The range
u


Each of these is separated from the next by a period. The period
is called the ˜˜dot operator.™™ Here is an example of the hierarchy
structure if you want to put the number ˜˜123™™ in cell C10 in the
sheet named ˜˜MySheet.™™ The workbook is named ˜˜MyFile.xls.™™
Write this as one line.

Workbooks(“MyFile.xls”).Sheets
(“MySheet”).Range(“C10”).Value = “123”

A shortened command will work, too:

Range(“C10”).Value = “123”

But note that in this case, as there is no definition of the
workbook or the sheet, Excel will perform this command on
whatever worksheet is currently active. So long as you are
clear about where you are as you launch the subroutine, you
can make the code short. However, when you are making the
subroutine work across many worksheets or even many work-
books, it helps to write the longer code so you can specify exactly
where the macro should work.
The .Value you see at the end of the code (note the dot
operator) is another example of being specific. Writing

Range(“C10”)=“123”

works, too, but Excel has to evaluate what you mean by this. It is
always better to be very specific so that Excel can work in the
most immediate way to accomplish the task.




TLFeBOOK
Chapter 19
322




WRITING YOUR FIRST MACRO
Now that we know the lay of the land, let™s try writing our first
code. We will name the first macro FirstMacro. In the Code
Editor window, enter the following line:

Sub FirstMacro

And then press Enter. Excel immediately adds to your entry
so that it appears as:

Sub FirstMacro()
End Sub

The Sub is short for subroutine, and because Excel now
recognizes this as the first line for the macro subroutine, it
adds the ˜˜()™™ at the end. The parentheses can be used in more
complex macros to hold information that the macro will work
with. Most macros will just have these parentheses without any-
thing between them. Excel also automatically adds a second line
of ˜˜End Sub™™ at the end. The space between the two is where
you write your macro, inserting additional lines as you go and,
of course, deleting any extraneous lines. Another way of saying
this is simply that the end of a macro must have the End Sub
line.
Now let™s add these lines

Sub FirstMacro()
Sheets(“Sheet1” .Select
)
Range(“A1”).Select
ActiveCell.Value = 17
Range(“A2”).Select
Selection.Value=30
End Sub

The first line specifies the sheets that we want the macro to
work on. This first step is equivalent to our selecting the Sheet1
sheet first to make that the active sheet. (Of course, for this macro
to work every time also means that there should always be a
sheet named ˜˜Sheet1.™™ It does not have to be the first sheet,




TLFeBOOK
Writing a Macro in Visual Basic for Applications 323




but it has to have this name.) Without this first line, the macro
would work on whatever worksheet is the active sheet.
Once you have selected a cell, you have a choice of using
either the ActiveCell keyword or the Selection keyword. Both
point to the cell that the cursor is on, which is by definition
the active cell, or the selection. In other cases where the macro
is highlighting a multi-cell range, you cannot use the ActiveCell
keyword, but you can use Selection.


Indents
Any line you write between the Sub and the End Sub lines
should be indented. Just tap the Tab key once before you begin
the line. This is really for ease in looking over the macro later on.
In fact, you will see that as we add other programming
sequences such as IF, it is helpful to indent those sections further
to indicate the nested quality of those sequences within the main
subroutine.


Automatic Capitalization for ˜˜Reserved Words™™
Also, notice that if you enter the words Select and Value all in
lowercase letters, VBA does an initial capitalization on them”it
capitalizes the first letters of these words. This shows that these
words are part of VBA™s reserved words”words that are used
within VBA to mean specific things or commands. You should
not use them except for their specific meanings and effects in
VBA.


RUNNING THE MACRO
Now that we have written this macro, let™s run it. Go back to
the worksheet of the file, to Sheet1. Once you are there, do
Tools > Macro > Macros and, at the dialog box, select the macro
name TestOne. Then click on Run. (See Figure 19-4.)
Once the macro is run, on your Sheet1, you will see the
two cells A1 and A2 suddenly contain the numbers 17 and 30,
respectively, written by this macro. At this point, you can also




TLFeBOOK
Chapter 19
324




FIGURE 19“4




FIGURE 19“5




define a shortcut key for this macro by clicking on the Options
button. Figure 19-5 shows the dialog box that will appear.
Specify the letter in the Shortcut key definition box. Refer to
Chapter 16 for a cautionary word on not using the shortcut key
combinations already used by Excel.




TLFeBOOK
Writing a Macro in Visual Basic for Applications 325




FUNCTION MACROS

A function macro creates your own custom function. It
u

returns a value.
All function macros begin with the Function keyword.
u

They end with the End Function.
They can be called from worksheet cells. This makes them
u

work like any of the functions Excel already has (such as
IF, SUM, etc.).
Here is a very simple function macro to get you started. It
converts a temperature from the Fahrenheit scale to the Celsius
scale. Let™s call it ConvertFtoC:

Function ConvertFtoC(Arg1)
ConvertFtoC=(Arg1-32)*5/9
End Function

Write this on a module sheet. For the moment, all you need

<<

. 58
( 62 .)



>>