. 52
( 62 .)



which describes the action of highlighting a range 4 rows to 2
rows above (notice the negative sign) the current active cell. If
you know the range that you want to specify, then

ActiveCell.FormulaR1C1 ¼ “¼ SUM(R[À4]C:R[À2]C)”

could be rewritten as:

.Value ¼ “¼SUM(C1:C3)”

Notice that we use Value even for writing formulas. To
write a formula, start with an equals (¼) sign and then write
the formula within the double quotes. The code literally enters
into the cell whatever it is told to, so in this case, with the
beginning equal sign, it is writing the format that is required
for entering formulas.

Chapter 16

Copy and Paste
As already shown, we can shorten the Copy part from
Range (“C1:C5” .Select
To something like this:

Range (“C1:C5”).Copy

The next step specifies a different sheet. It™s best to leave it
as is for the moment:

Sheets (“Sheet2” .Select
Range (“D1”).Select

Other Tricks
When you see lines of code that look similar at the beginning and
vary in the latter parts, you can use the With structure to make
the code simpler and also faster to run. The best way to explain it
is to show what it looks like before and after. Here it is before:

Selection.Font.Bold ¼ True
Selection.Font.Italic ¼ True

Let™s make the change that we already know how to make,
to create a final form that is more apparent:

Range(“D5” .Font.Bold ¼ True
.Font.Italic ¼ True

And here it is the final form:
With Range(“D5” .Font
.Bold ¼ True
.Italic ¼ True
End With

Recording Macros 285

The With statement abbreviates all the identical references
you have to specify for working with particular items in Excel
and allows you to specify the actions on the different parts with-
out specifying those references again. It™s a great way to make
the code simpler and easier to read, and also to make it run
faster. The rule of thumb is that the fewer dots you see in the
code (more specifically, they are called ˜˜dot operators™™), the less
time VBA needs to run through it.

I mentioned that you can write descriptive comments in your
code by entering an apostrophe and then writing the text after
that. Comments are great for reminding you what the code is
doing. You may think that you do not need comments as you
will remember the purpose of the code, but it is easy to forget
even your own genius in the code!
There is another use of the comment apostrophe, by the
way: If you need to turn off code, simply put an apostrophe in
front of it and it will be ignored by VBA.

This page intentionally left blank.


On-Screen Controls

A quick way to make your models more user-friendly is to put
controls on the screen to allow the user to activate different
settings or launch to macros to do various tasks. This chapter
will show you how to use these on-screen controls.

To place these controls”or more accurately”these control objects
on the screen, use the Control Toolbox toolbar. This will appear in
the middle of the screen when you select View > Toolbars and then
select Control Toolbox. Under the View > Toolbars menu is actually
another toolbar called the Forms toolbar, but you should not use
this because it is the set of controls for versions of Excel earlier
than Excel 97.
The Control Toolbox toolbar is shown in Figure 17-1.
You can reshape this so that it is a horizontal box. In any form,
you can also just drag it up into the toolbar area and park it there.
The Control Toolbox gives you the controls that you can place
on the screen, as well as other buttons for manipulating them.

How to Place a Control Object on a Sheet
Click on the control you want, then find a location on the work-
sheet. Click again and then drag to define the size of the control.

Copyright © 2004 by John S. Tjia. Click here for terms of use.
Chapter 17


Design mode Properties
View code

Check box Text box
Command button Option button
List box Combo box
Spin button
Toggle button
Scroll bar

More controls

Release when you have the dimensions you want. Use the mouse
again to readjust if necessary.

Design Mode
As you place a control on the sheet, the Design mode icon at the
top left of the Control Toolbox toolbar will show that it has been
activated: it has the appearance of being in a depressed position
with borders around it. Once the control is in place and has been
sized to what you want, turn off the Design mode by clicking on
it again. This locks the control so it cannot be changed.
Later on, if you need to edit the control again, press the
Design mode to put you in the edit mode.

Control Properties
Each of the controls has a set of properties associated with it.
When you are designing the control, you can see its properties
by clicking on the Properties icon on the Control Toolbox, or by
right-clicking on the mouse when it is placed over the control.

On-Screen Controls 289


Figure 17-2 shows what the Properties window looks like for the
check box control.

Check Box
A check box is used to show a choice, and typically it is either
checked or unchecked. In the Design mode, show the Properties
window. The properties you should work with are the following:
Caption: This is the text that will be shown as the check box
Value: This shows the state of the check box. It this is
TRUE, it means that the check box is checked. If it is FALSE,
it means it is unchecked.

Chapter 17

LinkedCell: This is the location, either a cell address or a
range name, that stores the value of the check box. When
the check box state changes, so does the value of the linked
cell, and this provides the direct link between this control
and whatever IF formulas you have on the worksheet
that will be driven by this. It also works the other way: if
you change the value in the LinkedCell, then the check
box will change also.

Text Box
This is a way to type text into a predefined area. You can set this
text box control as part of a group of other controls that form
a ˜˜control panel™™ in your model. Anything entered in the text
box is automatically entered into a cell that you have identified
as the LinkedCell. The properties you should work with are the
Text: This sets or shows the text in the control.
Multiline: If TRUE, the control will display text in multiple
MaxLength: Enter 0 to allow any number of characters.
Otherwise, enter an integer to set the maximum number of
characters for the control.
LinkedCell: This is the cell that will store the Text property
of the text box. Likewise, the text in this cell will determine
what will appear in the text box.

Command Button
This is a button that typically is used to run a routine with a click


. 52
( 62 .)