Improving Recorded Macros

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

The good way to learn the basics of VBA is to record a macro and see how Excel writes its own code. Often, though, recorded macros contain much more code than is necessary. The following exercises demonstrate how you can improve and streamline code that has been produced by a recorded macro.

Exercise 2: Improving on Recorded Macros

This exercise shows that when macros are recorded, often more code is generated than necessary. It demonstrates the use of the With statement to précis the code.

  1. Select any cell or block of cells.
  2. Start the macro recorder and call the macro FormatCells. The Relative References setting will not be relevant.
  3. Go to Format > Cells > Font and choose Times New Roman and Red.
    Go to Patterns and choose Yellow.
    Go to Alignment and choose Horizontal, Center
    Go to Number and choose Currency.
  4. Click OK and stop the recorder.
  5. Click the Undo button (or Ctrl+Z) to undo your changes to the worksheet.
  6. Select a block of cells and run the FormatCells macro. Note that it can not be undone! Type in the cells to check the result of the formatting.
  7. Look at the code:

    Sub
    FormatSelection()

    Selection.NumberFormat = “$#,##0.00”

    With Selection

    .HorizontalAlignment = xlCenter

    .VerticalAlignment = xlBottom

    .WrapText = False

    .Orientation = 0

    .ShrinkToFit = False

    .MergeCells = False

    End With

    With
    Selection.Font

    .Name = “Times New Roman”

    .FontStyle = “Regular”

    .Size = 10

    .Strikethrough = False

    .Superscript = False

    .Subscript = False

    .OutlineFont = False

    .Shadow = False

    .Underline = xlUnderlineStyleNone

    .ColorIndex = 3

    End With

    With
    Selection.Interior

    .ColorIndex = 6

    .Pattern = xlSolid

    .PatternColorIndex = xlAutomatic

    End With

    End Sub

     

  8. Note all the extra instructions that have been recorded. Delete lines of code so that only the following remains:

    Sub
    FormatSelection()

    Selection.NumberFormat = “$#,##0.00”

    With
    Selection

    .HorizontalAlignment = xlCenter

    End With

    With
    Selection.Font

    .Name = “Times New Roman”

    .ColorIndex = 3

    End With

    With
    Selection.Interior

    .ColorIndex = 6

    End With

    End Sub

  9. Run the macro to test the edited code. It still works as before.
  10. Now modify the code even further:

Sub
FormatSelection()

With
Selection

.NumberFormat = “$#,##0.00”

.HorizontalAlignment = xlCenter

.Font.Name = “TimesNewRoman”

.Font.ColorIndex = 3

.Interior.ColorIndex = 6

End With

End Sub

  1. Test the macro. Everything still works and the code will run much faster.
  2. Try recording the same macro using toolbar buttons instead of going to the dialog box:
    Change the Font to Times New Roman
    Change the Font Colour to Red
    Change the Fill Color to Yellow
    Click the Center button
    Click the Currency button
  3. Look at the code. You still get lots of stuff that you don’t necessarily want. Excel is recording all the default settings. Most of these are safe to delete.
  4. Experiment with editing directly into the code to change colours, the font, the number format etc.

Entry filed under: Vba Excel. Tags: .

Improving Recorded Macros FUNGSI EXCEL HITUNG KATA

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: