15.

Task Three: Adding a Column of Dates

The order summary report you're working with doesn't include the date in each row since the text file includes numbers for only a single month. Before you can append these new records to the order-history database, you'll need to add the current month to each record.

Add a Constant Date

First you'll create a macro that fills the range with the date Nov-2002 by inserting a new column A and putting the date into each row that contains data.

Record Macro

1. Select a worksheet that has the labels filled in, click the Record Macro button, type AddDates as the name of the macro, and then click OK.

2. Select cell A1, and then choose the Insert menu and click Columns. Excel inserts a new column A, shifting the other columns to the right.

3. Type Date in cell A1, and then press the Enter key.

4. Press Ctrl+Shift+* to select the current region.

5. Choose the Edit menu, click Go To, and click the Special button. Click the Blanks option, and click OK to select only the blank cells. These are the cells that need date values.

6. Type Nov-2002 and press Ctrl+Enter to fill the date into all the cells. Excel fills the date into all the rows. (Excel displays the date as Nov-02, but it stores the full date.)

   Stop Recording

   

7. Select cell A1, and then click the Stop Recording button to stop the recorder.

Step Through the Macro

1. With cell A1 selected, choose the Edit menu, click Delete, click the Entire Column option, and then click OK.

   Run Macro

   

2. Click the Run Macro button, select the AddDates macro, and then click Step Into. If you ignore the comments, this is what the macro should look like:

   Sub AddDates()     Range("A1").Select     Selection.EntireColumn.Insert     ActiveCell.FormulaR1C1 = "Date"     Range("A2").Select     Selection.CurrentRegion.Select     Selection.SpecialCells(xlCellTypeBlanks).Select     Selection.FormulaR1C1 = "Nov-2002"     Range("A1").Select End Sub

3. Press F8 repeatedly to step through the macro.

This macro is pretty straightforward. Notice that the statement that enters the word Date has the word ActiveCell as the object, changing the 'formula' of only the active cell, whereas the statement that enters the actual date changes the 'formula' of the entire selection uses Selection as the object. When you enter a formula using the Enter key alone, the macro uses the word ActiveCell. When you enter a formula using Ctrl+Enter, the macro uses the word Selection. (If the selection is only a single cell, ActiveCell and Selection are equivalent.)

The recorder always records putting a value into a cell by using the Formula R1C1 property-even if you enter a label-just in case you might have entered a formula.

Prompt for the Date

Your recorded macro should work just fine if you always run it using the same month's data file. But the next time you actually use this macro, you'll be working with December orders, not November orders. You need to change the macro so that it asks you for the date when you run it.

1. Insert a new line after the comments in the AddDates macro, and enter this new statement:

   myDate = InputBox("Enter the date in MMM-YYYY format")

   InputBox is a Visual Basic function that prompts for information while a macro runs. The words in parentheses are the message it displays. The variable myDate stores the date until the macro is ready to use it.

   Tip 

   The InputBox function is a useful tool for making a macro work in slightly changing circumstances.

2. Select and delete the text 'Nov-2002' in the macro. Be sure to delete the quotation marks.

3. Type myDate where the old date used to be. The revised statement should look like this:

   Selection.FormulaR1C1 = myDate

4. Activate a worksheet that needs the date column added. (Delete the old date column, or run the FillLabels macro, as needed.)

5. Click the Run Macro button, select the AddDates macro, and then click Run. The macro prompts for the date.

   Important 

   If you click the Cancel button, the macro leaves the date cells empty. In Chapter 7, you'll learn how to program the macro to determine whether the user clicked the Cancel button.

6. Type Nov-2002 and click OK. The macro inserts the date into the appropriate cells in column A.

   

7. Save the Chapter02 workbook.

This completes your third task. Now you're ready to append the new data to the database.