<<

. 57
( 62 .)



>>

it will not work if the line has negative numbers and zeros. It
returns a zero in that case.
There may be some lines that will have only very small num-
bers. The plug lines in particular may show numbers that appear to
be zeroes but contain a very small number like 0.00003. They are
not zeros because in the balancing iterations Excel will stop once
the maximum change number set in Tools > Options > Calculation
is reached. If you do not want these near-zero lines to appear, then
the formula becomes more complicated, because we would need to




TLFeBOOK
Bells and Whistles 313




return a ˜˜y™™ only if the numbers on the line are outside the range of
À0.001 to 0.001 (this range is set at your discretion):

¼IF(OR(MAX(C6:H6)> 0.001,
MIN(C6:H6,0) < À0.001), “y”,“n”)

Write this formula in the A column for each row that you
want the Autofilter command to act on. The entries you should
have in this column are as follows:

Enter This in Column A To Have This Effect

For all rows that will always show when the
y Auto¬lter is on. This includes blank rows required
for visual spacing, and title rows that need to
appear.
For all rows that will always be hidden when the
n Auto¬lter is in effect. Examples would be rows
used for internal calculations or for comments
about the model.
The formula For all rows that need to be shown only if they
contain numbers other than zeros.



Activate the Autofilter
With the settings in place, select the column by clicking on the
column letter A. Then select Data > Filter > Autofilter to turn the
Autofilter on. You must already have the entries in column A in
place to do this; otherwise, an error message that says ˜˜No list
was found . . .™™ will appear on the screen. A button with a down-
ward arrow will appear in cell A1. Click on this and select ˜˜y™™
from the list of options. When you do this, only the ˜˜y™™ rows will
appear on the screen; the ˜˜n™™ rows will be hidden from view.
You can print this page as it appears, and the ˜˜n™™ rows will
remain hidden. Another thing you can do is to copy this filtered
range to another sheet and the hidden rows will not be copied
over. However, formulas in the original range become values in
the target sheet. After printing, click on the button with the
downward arrow and select ˜˜All™™ to show all the rows again.




TLFeBOOK
This page intentionally left blank.




TLFeBOOK
CHAPTER 19


Writing a Macro in Visual
Basic for Applications




In Chapter 16, we looked at macros and how to record them a
little bit. In this chapter, we will see an overview of how to write
a simple macro in VBA. I hope that this primer will get you
started on your own road to mastery of this really easy-to-use
and powerful language. The main purpose for using VBA in our
model is to be able to manipulate elements in the model™s cells
and worksheets. VBA itself should not be used to do calculations
on the worksheet. That™s the work of the formulas and functions
that we have created in the model.
The term macro is generally used to describe any code”also
called a procedure”that can be run to manipulate and produce
some effect on the balance sheet.
There is, however, another type of macro called the function
macro for developing your own functions. This is covered briefly
at the end of this chapter.


THE VISUAL BASIC EDITOR
Macros, whether they are recorded or ones that you create and
write yourself, are kept in another part of the Excel file called the
Visual Basic Editor, or VBE. This is the second part of the two
315



Copyright © 2004 by John S. Tjia. Click here for terms of use.
TLFeBOOK
Chapter 19
316




parts that constitute a workbook file. The worksheets constitute
the first part.


Let™s Go to the VBE
To get from the worksheet part to the VBE, you have to go
through one of two steps:
Use either:
Tools > Macro > Visual Basic Editor
u

or
Alt + F11
u


If you do this, you will see the screen that appears as
Figure 19-1.


FIGURE 19“1




TLFeBOOK
Writing a Macro in Visual Basic for Applications 317




Note that on the taskbar at the bottom of the screen there are
now two buttons representing the active components of Excel: the
spreadsheet part and the VBE part.
There are three main areas in the VBE, as you can see.
Top left corner: This is the Project Explorer window,
u

and shows you the items that the VBE has. If you do not
see this window on your screen, or if you
inadvertently closed it by clicking on the X box, you can
make it reappear by going through this sequence in the
VBE (and not in the worksheet part): View > Project
Explorer.
Bottom left corner: This is the Properties window. It
u

will show the properties of the item being highlighted
in the Project Explorer window. To make this
window appear if it is not there, use: View > Properties
Window.
Right side of the screen: This is the Code Editor window.
u

This is the window that will show the macros and is
where they can be edited. To make this window appear if
it is not there, use: View > Code.

From the VBE Back to the Worksheet
There are several ways to move from the VBE to the worksheet:
The easiest way is to click on the taskbar button for the
u

Excel spreadsheet. This will shift the screen to the
spreadsheet. The VBE is still running, but it is in the
background. When you want to go back to the VBE again,
click on the taskbar button for the VBE and you are back
there again.
Alt + Q. This will cause the VBE to close so that you are
u

automatically put back in the worksheet part. To go back
to the VBE, you will have to restart it again by going
through the steps outlined above.
Close the VBE window by clicking on the X at the top
u

right of the screen. Like Alt + Q, you will need to restart
VBE to get back to it.




TLFeBOOK
Chapter 19
318




INSERTING A VBA MODULE
We haven™t yet seen any place specifically for writing code in
the Project Explorer window. Let™s insert a VBA module by
Insert > Module while we are in VBE. (See Figure 19-2.)
You will then see an additional listing for Modules, and under
that a module sheet with the name Module1. (See Figure 19-3.)
This is the default name; the next module sheet will be
called Module2, etc. The cursor should now be in the Code
Editor, the wide field on the right of the screen.
Module1 is just one of the many VBA modules that you
can insert into the VBE. In fact, as you develop your macro
writing skills and you write macros for data management, user


FIGURE 19“2




FIGURE 19“3




TLFeBOOK
Writing a Macro in Visual Basic for Applications 319




interfaces, printing, and so on, it is a good idea to write them
on separate module sheets. You can rename the module sheets
for greater clarity. So your collection of modules may be called,
for example, modData, modValuation, modPrint, etc. You can
rename a module by clicking on it and then putting the new
name next to the item marked (Name) in the Properties
window in the lower left-hand corner of the screen.
If you have experimented with recording macros, the VBE
will already have created Module1 and perhaps other module
sheets as a place where the recorded macros are kept.


Deleting a Module
If you want to delete a VBA module or a UserForm module, just
click on the module name in the Project Explorer window and
right-click on your mouse. On the short menu that appears, click
on ˜˜Remove [your module name]. . .™™ You have a chance to remove
it altogether or to save it as a separate file before you delete it.


OBJECTS
An object is anything in Excel that can be manipulated on the
screen through manual input or by a macro. Objects are what
VBA programming manipulates. An Excel object can be
Excel itself
u

The workbook (the .xls file)
u

A worksheet or chart sheet in the workbook
u

A column in the worksheet
u

A row in the column
u

<<

. 57
( 62 .)



>>