Using the Macro Recorder

December 27, 2008 at 3:34 pm Leave a comment

Open Excel and the VBE (Visual Basic Editor). Unless it has been changed, the VBE window contains the Project Explorer window and the Properties window (these can be accessed from the View menu).

Project Explorer: Works like a file manager. Helps you navigate around the code in your workbook.

Properties Window: Shows the properties of the currently active object (e.g. Sheet1) of the current workbook (e.g. Book1).

Exercise 1: Recording a Macro.

This exercise shows what happens when a macro is recorded and demonstrates the difference between recording absolute and relative references.

  1. On an empty worksheet in a new workbook, select cell C10
  2. Start the Macro Recorder with option to save macro in This Workbook. At this point the VBE creates a new Modules folder. It’s quite safe to go and look at it – your actions won’t be recorded. Click the [+] next to the folder and see that the VBE has places a module in the folder and named it Module1. Double-click the module icon to open its code window. Switch back to Excel.
  3. Make sure that the Relative Reference button on the Stop Recording toolbar is NOT pressed in.
  4. Select cell B5 and stop the recorder.
  5. Switch to the VBE and look at the code:

    Range(“B5”).Select

  6. Now record another macro, exactly the same way, but this time with the Relative Reference button pressed in.
  7. Switch to the VBE and look at the code:

ActiveCell.Offset(-5, -1).Range(“A1”).Select

  1. Now record another macro, but instead of selecting cell B5, select a block of cells 3×3 starting at B5 (select cells B5:F7)
  2. Switch to the VBE and look at the code:

ActiveCell.Offset(-5, -1).Range(“A1:B3”).Select

  1. Play back the macros, having first selected a different cell than C10 (for Macro2 and Macro3 the starting cell must be in row 6 or below – see step 11 below)

    Macro1 – always moves the selection to B5
    Macro2 – moves the selection to a cell 5 rows up and 1 column to the left of the selected cell.
    Macro3 – always selects a block of six cells starting 5 rows up and 1 column to the left of the selected cell.

  2. Run Macro2 but force an error by selecting a cell in row 5 or above. The macro tries to select a non-existent cell because its code is telling it to select a cell 5 rows above the starting point, and that’s off the top of the sheet. Press Debug to be taken to the part of the macro that caused the problem.

    NOTE: When the VBE is in Debug mode the line of code that caused the problem is highlighted in yellow. You must “reset” the macro before you can proceed. Click the Reset button on the VBE toolbar or go to Run > Reset. The yellow highlighting disappears and the VBE comes out of Debug mode.

  3. It is important to try and anticipate user error like this. The simplest way is to modify the code to simply ignore errors and move on to the next task. Do this by adding the line…

    On Error Resume Next

    … immediately above the first line of the macro (underneath the line Sub
    Macro1()

  4. Run Macro2 as before, starting too high on the sheet. This time the line you typed tells Excel to ignore the line of code that it can’t execute. There is no error message and the macro exits having done all it can. Use this method of handling errors with caution. This is a very simple macro. A more complex macro would probably not perform as expected if errors were simply ignored. Also, the user has no idea that something has gone wrong.
  5. Modify the code of Macro2 to include a more sophisticated error handler thus:

    Sub Macro2()

    On Error GoTo ErrorHandler

    ActiveCell.Offset(-5, -1).Range(“A1”).Select

    Exit Sub

    ErrorHandler:

    MsgBox “You must start below Row 5”

    End Sub

 

  1. This time the user is presented with a dialog box when something goes wrong. If there is no error the line Exit Sub causes the macro to finish after it has done its job – otherwise the user would see the message even if there were no error.

Entry filed under: Vba Excel. Tags: .

Using the NotInList event (combo) Improving Recorded Macros

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 14 other followers

Blog Stats

  • 94,725 hits

Twitter Updates

Iklan


%d bloggers like this: