Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

The order summary report you import doesn’t include the dates in each row because that would not be useful to people using the report. But you want to add the data to a master order history list, and the master list does include dates in each row because it contains data for multiple months.

Add a Constant Date

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

  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. On the Home tab of the Ribbon, click the Insert arrow, and click Insert Sheet Columns.

    Excel inserts a new column A, shifting the other columns to the right.

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

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

  5. On the Home tab of the Ribbon, click the Find & Select arrow, and click Go To Special. Click the Blanks option, and click OK to select only the blank cells.

    These are the cells that need date values.

  6. Type Nov-2007, and press Ctrl+Enter.

    Excel fills the date into all the rows. (Excel displays the date as Nov-07, but it stores the full date, as you can verify by looking at the formula bar.)

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

Step Through the Macro

To understand what the macro recorder created, look at the macro as you step through it.

  1. With cell A1 selected, on the Home tab of the Ribbon, click the Delete arrow, click Delete Sheet Columns, and then click OK.

  2. On the View tab of the Ribbon, click the View Macros 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-2007" Range("A1").Select End Sub

    This macro is pretty straightforward. Notice that the statement that enters the word Date uses the word ActiveCell as the object, changing the “formula” of only the active cell, whereas the statement that enters the actual date uses the word Selection as the object, changing the “formula” of the entire range of selected cells. When you entera 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 consists of only a single cell, ActiveCell and Selection are equivalent.)

    In addition, using just the Enter key changes the selection to the next cell down. That’s why the Range("A2").Select statement is in the macro. It doesn’t hurt anything, but it is also unnecessary. Removing unnecessary statements from a recorded macro makes it easier to read, and easier to modify in the future if you ever need to.

  3. Delete the Range("A2").Select statement from the macro.

  4. Press F8 repeatedly to step through the macro.

The recorder always records the insertion of a value into a cell by using the FormulaR1C1 property-even if you enter a constant-just in case you might have entered a formula.

Prompt for the Date

Your recorded macro should work just fine-assuming that you always run it using the Nov2007 text 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 puts in the correct date. One way to do that is to have the macro ask you for the date as it runs.

  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.

    See Also 

    For more information about the InputBox function-including how to make the macro work properly when you click Cancel, see the section titled “Ask Yourself a Question” in Chapter 7, “Control Visual Basic.”

  2. Select and delete the text "Nov-2007" 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 to have the date column added. (Delete the old date column, or run the FillLabels macro, as needed.)

  5. On the View tab of the Ribbon, click the Macros button, select the AddDates macro, and then click Run.

    The macro prompts for the date.

  6. Type Nov-2007, and click OK.

    The macro inserts the date into the appropriate cells in column A.

  7. Save the Chapter02 workbook.

    Tip 

    In this example, the name of the imported file contains the name of the month. Consequently, it is possible to extract the name of the month from the worksheet name without prompting the user. To do that, change the statement that includes the InputBox function to this: myDate=Left(ActiveSheet.Name,7). Left is a VBA function that is essentially identical to the Excel function with the same name. In this case, it extracts the first seven letters from the worksheet’s name, returning "Nov2007". Depending on the stetting in you have for Regional Options in Windows, Excel will interpret that string as a date. Prompting for the date avoids making assumptions about the file name or the type of string Excel can interpret as a date, but if you can make those assumptions, you can make the macro run with less interaction from you.

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

Related

Категории