Excel

Macro Situation #1

 

 

1.      In the VBA SAMPLE 4 workbook file, create a macro that
enters budget data into the active worksheet.

 

The data that the macro should enter are the following:

 

 



 

 

Specifically, the macro should do the following:

 

        Once entered, the heading information in row 1 should
be formatted to Arial 14 point Bold Italic blue, and should be centered
across cells A1:D1.

 

        Once entered, the heading information in row 3 should
be formatted to Arial 11 point Bold blue, and should be centered across
cells A3:D3.

 

        In row 4, the current date should be entered as a
variable, so that it’s always correct whenever the workbook is
opened.  The date should be centered across the range A4:D4, and should
be formatted to Arial 10 point Bold blue.

 

        The data in the range A7:D7 should be formatted to
Arial 11 point Bold, with a thick bottom border.

 

        The range A17:D17 should also have a thick bottom
border.

 

        The data in the range A8:D17 should be Arial 10 point.

 

        Formulas should be entered in cells B18, C18, and D18
to sum the expenses in B8:B17, C8:C17, and D8:D17, respectively.

 

        Cells B18:D18 should be formatted to display dollar
signs with two decimal places.

 

        The range B8:D17 should be formatted to display with
commas and two decimal places.

 

        The width of columns A and D should be changed to 14.

 

        The labels in cells B7:D7 should be right-aligned.

 

        When the macro stops running, the active cell should
be cell B8, so the user will be ready to enter data into the worksheet.

 

 

2.      Name the macro Budget_Report.  Store the macro in the VBA
SAMPLE 4 workbook file.

 

Rename the module Budget_Report_Macro.

 

Store the Budget_Report_Macro module in the same Modules folder as the
Weekly_Report_Macro (which was already in the VBA SAMPLE 4 workbook when
you opened it).

 

 

NOTE:  You should be able to run this macro on any worksheet.  Use the
blank untitled worksheets in the VBA SAMPLE 4 workbook file to text the
macro.  If you need to run multiple tests, feel free to insert as many
worksheets as necessary.

 

 

 

 

 

 

Macro Situation #2

 

 

Here’s the situation:  The employees of Paperback Palace Bookstore
have the option of taking the National Bookseller’s Exam.  You, as
the manager, want to reward those employees who did well on the exam. 
So you put together the Spring 2004 Exam worksheet (which is the first
sheet in the VBA SAMPLE 4 workbook file).

 

In that worksheet, in column F, you want to calculate a salary bonus for
each employee who got a certain score.  Because your employees will be
taking this exam periodically, you want to create a mechanism whereby
you can easily perform the bonus calculation now, and whereby you will
be
abl⁥潴甠敳椠⁴条楡湩琠敨映瑵牵ⱥ愠⁳敷汬മච桔
獩椠⁳潨⁷桴⁥潢畮敳⁳楷汬眠牯㩫ꀍ

      If the employee’s test score is between 95 and 100
(inclusive), they will get a bonus of 20% of their salary.

 

      If the employee’s test score is between 89 and 94
(inclusive), they will get a bonus of 10% of their salary.

 

      If the employee’s test score is between 83 and 88
(inclusive), they will get a bonus of 5% of their salary.

 

      If the employee’s test score is below 83, they won’t
receive any bonus.

 

Your mission is to create a macro that will calculate the values in
F6:F26 of the Spring 2004 Exam worksheet.

 

 

NOTE:  You should plan to use the macro to calculate the bonus in cell
F6, and then you can Autofill from F6 through F26.  This macro does not
have to automatically fill all of the cells in column F!

 

 

Name the macro module Exam_Bonus_Macro, and store it in the same Modules
folder with Budget_Report_Macro and Weekly_Report_Macro.  (You can name
the different parts of the procedure however you want.)

 

Add comments to the macro to explain what the macro does and why you
decided to design it the way you did.

 

Make sure that, once you calculate the bonus in cell F6, you copy the
formula through cell F26.

 

 

HINTS:

 

        You will need to create a User-defined function to do
this.

        Function statements can have multiple arguments.

        In your macro, the two key pieces of information are
the salary amount and the test score.

 

 

 

 

 

 

 

Macro Situation #3

 

 

Here’s the situation:  You periodically have employee lists that are
downloaded and dumped into an Excel worksheet, with varying numbers of
rows.  Some days 50 rows might be downloaded, for example, while other
days there might be only 11 or 12 rows of data.  Despite the fact that
the number of employees (i.e., rows) changes from report to report,
潨敷敶Ⱳ礠畯愠睬祡⁳慷瑮琠敨映湩獩敨⁤牰摯捵⁴潴
氠潯桴⁥慳敭‬楷桴琠敨映汯潬楷杮猠数楣楦慣楴湯
㩳ꀍ

        You want the Hourly Rate figures and the Gross Pay
figures in columns H and I to be formatted to display dollar signs and
two decimal places.

 

        You want columns C, E and F to be AutoFit in order to
fully display all of the data in those columns.

 

Your mission, therefore, is to design a single macro that can be used
for formatting the values in columns H and I of both of these
worksheets.  Your objective is to have a macro that will format the
filled cells in columns H and I of any sheet, regardless of how many
filled cells there are.  Therefore, you want the macro to be flexible
enough to format only the filled cells in these two columns, and
“smart” enough to know when to stop.  Do not simply record a macro
that formats the entire contents of columns H and I to display dollar
signs and two decimal places, because (presumably) you’ll be entering
other non-currency numeric data in other cells of those columns, and you
don’t want them to be displayed with dollar signs.

 

Notice that in the Payroll List1 worksheet, the employee data begin in
row 7 and extend down through row 66, whereas in the Payroll List 2
sheet the employee data begin in row 7 and continue only through row 40.

 

Other things your macro should do are as follows:

 

1.      At the beginning, the user should be asked what date they
want entered into cell E3 of the worksheet.  The user should be
informed that the date should be entered in mm-dd-yy format.  Then,
once the user specifies the date for the report, that date should be
displayed with the full weekday, full month, day and a four-digit year
(for example, Friday, May 21, 2004).

 

2.      Whatever date the user enters when running the macro should
appear in cell E3 of the worksheet, formatted as indicated in #1 above.

 

3.      Once the macro has finished formatting the hourly rate and
gross pay data in columns H and I and widening columns C, E and F, cell
A1 should be selected.

 

4.      In addition, at the end of the macro a Message Box should
be displayed, reminding the user to check column widths before printing
the report.

 

The Message Box should display only an OK button, and it should have a
yellow triangle icon with a black exclamation mark inside the triangle.

 

The word IMPORTANT! (in caps and with an exclamation mark) should be
displayed on the title bar of the Message Box window.

 

5.      The very last thing the macro should do before stopping is
select cell A1 of the worksheet.

 

 

Name the macro Format_Rate_And_Pay.

 

Name the module Payroll_Report_Macro, and store the module in the same
Modules folder as all of the other macros and procedures created in this
assignment.

(...continued)

(continued...)