Thinking In Windows - Part 6 of 8 for Excel 2000

Thinking In Windows

Elements, Collections, Objects, Properties, Methods, and Procedures

Viewing (and modifying) a Recorded Macro

Many of you asked about the process of changing the date in last week's workshop demonstration (recall that we changed the date [and initialized fields] in a report that was re-used on a weekly basis). After all, that date is going to change every week! And the computer can only "remember" the date that I gave it when first creating the new macro. Last week that date was "09/13/2013". That date won't do us any good when I run the macro this week! What do you suppose the odds are that we can work with automating that process for changing the date?

The macro that we recorded for the last workshop may be viewed (and modified) using the following procedure:

To display a macro using the Macro dialog box

You are in the Excel Spreadsheet in which you created the macro last week

From the Tools menu, choose Macro and then Macros . . .

In the Macro Name box, the name of the macro you created last week should appear

Choose the Edit button

Notice that the Visual Basic Editor comes up with the macro opened in an editing window. The macro consists of statements which are instructions to perform actions. Statements are made up of keywords, operators, variables, and / or procedure calls. For example, the Sub and End Sub keywords mark the beginning and end of a macro.

Sub Initialize()


'
' Initialize Macro
' Macro recorded 9/17/2013 by Maija Chamberlain
'
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "9/20/2013"
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("D6").Select
    Selection.Copy
    Range("E6:H6").Select
    ActiveSheet.Paste
    Range("D6:H6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D8:H14").Select
    ActiveSheet.Paste
    Range("B21:Y25").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D6").Select

End Sub

OK, now from our "slice of time" perspective, if we are concerned with "modifying the date" which line (or lines) in that code would we most likely want to work with (or replace)? Well, there's only one line in the code that even mentions anything remotely looking like a date:

    ActiveCell.FormulaR1C1 = "9/20/2013"

The line of code just previous to that line moves the cursor to cell C1 and "Selects" that cell. And the line of code following that line begins the process of zeroing out the first range in Row 6 (D6:H6). So, that line we isolated has got to be our candidate for modification! But, what do we do to modify it?

Well, to start we could gather ourselves and pull together as much stuff as we can remember (or reference) from our previous sessions. Let's go all the way back to the 5th sentence (and its "footnote") in the third session and locate these words "And,from the perspective of Visual Basic, each of the elements (except one, the 'cell'*) in the collections we have seen thus far are actually 'objects.'" Has someone got the "footnote" handy?

It looks like we have to get that "cell" that we selected into an "object" form. How do we do that?

Now, without some experience in Visual Basic (VB) programming (and, sometimes even with VB programming experience), this is where the path to the solution gets obscured because "you can't see the forest, because of all of the trees!" Believe me, no matter which combination of keywords you might use in that "search" box up in the upper right-hand corner of the VB editor, you are going to be overwhelmed with the possibilities!

Yet, not to despair! We know that there is another resource available to us! A "Google" search of the World Wide Web! (This is actually how I solved the problem!) I forgot about the necessity for an "object" and jumped ahead and searched on the following keywords "Excel get cell value VBA," which translates loosely into "How do I get a cell value into VBA from Excel?" What I was looking for was some VB code references concerning "date" (or "dates"). Viola! The third link I looked at had this VB code reference under the heading "Turning a date that isn't a date into a date:"

    Dim Cell As Object
    For Each Cell In Selection
        If IsDate(Cell.Value) Then
            Cell.Value = Date.Value(Cell.Value)
        End If
    Next Cell

So, I said to myself, "Self, I can use most of this for the solution I'm looking for!" Because I already know that the C1 cell has a "date value" in it and I just need to add seven days to that value, I thought I could just modify the "Cell.Value = " line in that borrowed code to be something like "Cell.Value = Cell.Value + 7"

The long and the short of this is that this is how the macro would look now:

Sub Initialize()


    Range("C1").Select
    Dim Cell As Object
    For Each Cell In Selection
        If IsDate(Cell.Value) Then
            Cell.Value = Cell.Value + 7
        End If
    Next Cell
    Range("D6").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("D6").Select
    Selection.Copy
    Range("E6:H6").Select
    ActiveSheet.Paste
    Range("D6:H6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D8:H14").Select
    ActiveSheet.Paste
    Range("B21:Y25").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D6").Select

End Sub

So now you can go in and Edit your Macro until it looks like the one above . . . and then use it from week to week to save a lot of time . . . .

To find out more about a Visual Basic keyword used in a macro, simply place the cursor somewhere in the keyword and press F1. When you do this, a topic in the Visual Basic Reference appears and presents more information about the term you have "selected".

And, just to reiterate:

Tips for Using the Macro Recorder

Plan what you want to do before you begin recording

Remember that the Macro Recorder records everything you do, including your mistakes

Select cells or objects first, and then turn on the recorder

This makes your macros portable - that is, applicable to whatever the active selection is
when you run the macro.

Switch to the appropriate workbook and select the appropriate worksheet before you turn on
the recorder

View your macros while you are recording them

                Your Visual Basic macros are stored on special sheets called Visual Basic modules.
               After you have started the recorder, you can choose the Arrange command on the
               Window menu to view the module while working on your worksheet. With this feature,
               you can watch the Macro Recorder actually create the Visual Basic code as you work.

Use the Macro Recorder as a learning tool

When you examine the Visual Basic code that the recorder creates, you'll learn more
about how Visual Basic works.

Next week we'll learn how to add a macro to the Microsoft Excel tools menu and assign a macro to a button on a spreadsheet. See you then!

If you have a question, then click here

Return to main menu