MegaHit Movie Rentals Lori is becoming an Excel wizard, thanks to this book and
the power of VBA. She has figured out a way to more easily
track overdue videos and she wants to use VBA to bring her idea
to life. In these case problems, assume the role of Lori and
perform the steps that she identifies. You may want to reread
the chapter opening before proceeding. - MegaHit Movie Rentals uses an Excel workbook to keep
track of videos that are rented. Lori wishes to add
functionality to this workbook, specifically the ability to
report overdue videos. Working late one night, she opens the
EX12XTRA04 Excel file and saves it to her personal storage
location as "Overdue Rentals". She reviews the contents of
the worksheet. The
RentalLog
sheet contains the log entries for 21 rentals and is shown
is figure 12.8.. The
Overdue
sheet is reserved for Lori's report.
| Figure 12.8 RentalLog Worksheet |  <![CDATA[<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif:: ::/sites/dl/free/0072470941/26807/image021.gif','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (37.0K)</a>]]> |
She spends the next few moments planning the steps required
to transfer the overdue rentals to the overdue rentals report.
Her planning notes are as follows: - Examine the Rental Log and determine which movies are
still due
- Copy the Trans#, Cust#, Title, Out date, and Due date
for each overdue film to the Overdue Rentals report
- Add a line to the bottom of this report that sums the
total late fees
- Clear the report on each run
On a piece of paper or in a word processor, write down your
initial thoughts on the commands, variables, objects, and
control structures that you might need to use and reference in
order to meet the above objectives. When finished, proceed to
the next step. - Lori decides to store the entire operation in a single
procedure. To begin, she inserts a new module and then
creates a Sub procedure called "GetOverdue" using the Visual
Basic Editor. The procedure uses a loop to move down the line
of cells examining the contents of the "returned" column. If
the returned entry is blank, it copies the information to the
Overdue Rentals report. The loop ends when it encounters a
blank row. She saves the workbook and proceeds to the next
step.
- Lori tweaks the "GetOverdue" Sub procedure to accumulate
a total late fee. She adds a variable called "TotalFees".
Then, she adds a step in her loop that adds the amount in the
late fee column to the TotalFees total. Finally, she adds
some code after the loop to insert the total after the last
row in the report. Her final code is shown in figure
12.9.
| Figure 12.9 GetOverdue procedure |  <![CDATA[<a onClick="window.open('/olcweb/cgi/pluginpop.cgi?it=gif:: ::/sites/dl/free/0072470941/26807/image023.gif','popWin', 'width=NaN,height=NaN,resizable,scrollbars');" href="#"><img valign="absmiddle" height="16" width="16" border="0" src="/olcweb/styles/shared/linkicons/image.gif"> (28.0K)</a>]]> |
Pleased with her results, she saves the workbook, closes
Excel, and goes home with a handful of free employee rentals to
enjoy. Data File:  ex12xtra04 (18.0K) |
2002 McGraw-Hill Higher Education