CE En 270 - Homework #19

Custom Subs - Resizing a Table

Excel spreadsheets are a perfect tool for working with tables of information.  However, in many cases, you don't know beforehand how big the table should be.  One solution to this problem is to use VB to resize your tables.  For this assignment, you will be creating a spreadsheet that looks something like this:

The table lists a set of parts where each part has a cost.  There is a formula at the bottom of the spreadsheet to sum the total cost and also to report the total number of parts in the list.  The Resize Table button deletes all information on the table and generates a new table with a number of rows equal to the specified table size.  It also completely reformats the table area and enters the two equations at the bottom in the correct location and with the correct formatting.  The Generate Sample Data button inserts several rows of sample data into the table in order to test the formatting and equations.

Click here to download a copy of the spreadsheet shown above.  The spreadsheet you download will look like this:

Complete the following steps in order to set up the spreadsheet:

1. Record a macro that sets up the formatting of the data section of the table (including borders, colors, currency style, etc.) and the entry of the labels, formatting, and formulas for the summary section at the bottom (use the Count function to get the number of parts).  When you enter the formulas, be sure to use an absolute reference to the row at the top of the table and a relative reference for the row at the bottom.  This will ensure that the formatting will be correctly applied when you make the macro more general in the next step.

2. Modify the code for the macro (sub) you recorded in step 1 so that it accepts a single integer argument and reformats the table according to the size (number of rows) indicated.  Add VB code to the Resize Table button that reads the table size from cell B4 and passes it to your modified macro sub.  When you do this, you will need to modify the parts of the recorded VB code that selects a range of cells.  To do this, you may want to use the following method:

Range(Cells(r1, c1), Cells(r2, c2)).Select

where r1, c1 and r2, c2 refer to the cells at the corner of the range you want to modify.  The trick is to specify the correct values based on the size of range you want to modify.

3. Record a macro that enters six or seven rows of sample data into your table (assuming your table is at least this big).  Modify the VB code for the Generate Sample Data button so that it calls this sub.  Use this button to test your Resize Table sub.

Submittal Instructions:

Click here to upload your homework assignment.