Peltier Tech

Peltier Technical Services - Excel Charts and Programming

How To: Fix a Recorded Macro

by 12 Comments

Tuesday, March 11, 2008 by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
 

In a recent post, I showed how to record a macro to save yourself time and effort with that repetitive task. The recorder has captured all of your keystrokes, intentional and otherwise. Objects are selected first, then acted upon. Defaults are applied indiscriminately every time the recorder followed you into a dialog. Actions you take and undo are all recorded. The result is syntactically accurate, but inefficient.

Let’s clean up the code to make it run faster and smoother, without the flashing shown on screen. Go to the VB Editor and open the code module containing your recorded macro. The code recorded in the previous example is shown below.

Sub RecordedMacro()
 '
 ' RecordedMacro Macro
 ' Macro recorded 3/7/2008 by Jon Peltier
 '

'
     With Selection
         .HorizontalAlignment = xlCenter
         .VerticalAlignment = xlBottom
         .WrapText = False
         .Orientation = 0
         .AddIndent = False
         .IndentLevel = 0
         .ShrinkToFit = False
         .ReadingOrder = xlContext
         .MergeCells = False
     End With
     With Selection.Font
         .Name = "Arial"
         .Size = 10
         .Strikethrough = False
         .Superscript = False
         .Subscript = False
         .OutlineFont = False
         .Shadow = False
         .Underline = xlUnderlineStyleNone
         .ColorIndex = xlAutomatic
     End With
     With Selection.Font
         .Name = "Arial"
         .Size = 12
         .Strikethrough = False
         .Superscript = False
         .Subscript = False
         .OutlineFont = False
         .Shadow = False
         .Underline = xlUnderlineStyleNone
         .ColorIndex = xlAutomatic
     End With
     Selection.Font.ColorIndex = 3
 End Sub

That’s a lot of code for the small number of actions that were done while recording. We centered the text in the selected cells, changed the font to Arial, changed the font size to 12 points, and changed the text to red. Every time an action was taken, properties which were not changed during that action were still recorded, despite being unchanged. For example, the font’s ColorIndex was twice “changed” to xlAutomatic, before finally being changed to 3 (red in the default Excel color palette). Removing the redundant and default statements greatly reduces the code.

Sub RecordedAndFixedMacro()
 ' Macro recorded & fixed up 3/7/2008 by Jon Peltier
 '
     With Selection
         .HorizontalAlignment = xlCenter
     End With
     With Selection
         With .Font
             .Name = "Arial"
             .Size = 12
             .ColorIndex = 3
         End With
     End With
 End Sub

This macro didn’t have the usual repeated cycle of select an object, format the selection, select another object, so I’ve included another sample below.

Sub SelectAndFormatRecording()
 '
     Range("C9:G9").Select
     Selection.Font.Bold = True
     Range("C11:G11").Select
     Selection.Font.ColorIndex = 5
 End Sub

By avoiding the sequence of selections, the code is smaller and runs faster, and the screen doesn’t change or even flash with each subsequent selection change.

Sub FormatByReference()
 '
     Range("C9:G9").Font.Bold = True
     Range("C11:G11").Font.ColorIndex = 5
 End Sub

A technique for streamlining code is to group operations performed on each object within With/End With blocs. The first recorded macro above was recorded with several With/End With blocks. But frequently when recording macros, we are carrying out operations as we think of them, and operations on the same object may be scattered around the macro. In this case, we can construct our own blocks, first rearranging the code, then forming the blocks.

Sub DisorderlyRecording()
 '
     Range("J12:M19").Font.Bold = True
     Range("J14:M14").Font.ColorIndex = 5
     Range("J12:M19").Font.Name = "Courier New"
     Range("J14:M14").Font.Italic = True
     Range("J12:M19").HorizontalAlignment = xlCenter
 End Sub

Reordering…

Sub ReorderedRecording()
 '
     Range("J12:M19").HorizontalAlignment = xlCenter
     Range("J12:M19").Font.Bold = True
     Range("J12:M19").Font.Name = "Courier New"
     Range("J14:M14").Font.ColorIndex = 5
     Range("J14:M14").Font.Italic = True
 End Sub

Blocking…

Sub ReorderedAndBlocked()
 '
     With Range("J12:M19")
         .HorizontalAlignment = xlCenter
         With .Font
             .Bold = True
             .Name = "Courier New"
         End With
     End With
     With Range("J14:M14").Font
         .ColorIndex = 5
         .Italic = True
     End With
 End Sub

Note that you can nest one With/End With block within another.

For a more detailed look at optimizing recorded macros and using Excel’s object model more effectively, see my colleague Tushar Mehta’s Beyond the macro recorder.

In a series of upcoming posts, I’ll cover the following VB topics:

Posted: Tuesday, March 11th, 2008 under VBA.
Tags: , , , .
Comments: 12

Peltier Tech Charts for Excel

Microsoft MVP Logo

Reader Interactions

Comments

  1. ckz says

    Using VBA I am assigning several different charts to the same worksheet. Based on a button the user presses, the correct chart will appear.

    So, to change the title of the worksheet and within the chart I assign the title, simply by saying Title = “Water”. Where ever I need that specific text in the code, I use Title.

    My question is, how do I subscript in the text of the editor box. This code changing is not my own. The previous programmer was able to add superscript within the editor box. Is there a way to do subscript?

    He simply had Title = “Water m³”

    I am trying to say Title = “CO2 emissions”
    (I cut and pasted the m3 from the program and the format stays, even in this email. I cut and pasted from a word document the CO2… and the 2 was subscript, but didn’t carry over in this email)

    I hope this question makes sense. I have seen a lot of website resources for formating specific cells, but nothing within the VBA editor.

    Thanks,

  2. Jon Peltier says

    One suggestion is to find a font that has sub- and superscripted characters.

    The way I had to do it on one project was to assign the superscript format to only some of the characters in the text element. I show this in the Scientific/Exponential Notation Axis Labels tutorial to produce the exponent on the powers of ten.

    With srs.Points(Counter)
    ' Apply label to point
    .HasDataLabel = True
    .DataLabel.Characters.Text = _
        myString1
    ' Superscript part of the label
    With .DataLabel.Characters(3, Len(myString1) - 2).Font
        .Superscript = True
        .Size = .Size + 1
    End With
End With

     
    The segment of the label is accessed using .Characters(start, length). I increased the size of the superscripted text by one point to make it legible.

  3. ckz says

    Thank you for the help. I am a little confused because the sample code is looking for a range of cells.

    In my situation, the title is actually written in the code, not drawn from data in a cell.

    Sub Title()
    Dim Title As String
    Dim StringLength As Integer

    Title = “CO2 Emissions”
    StringLength = Len(Title)

    Sheets(“Sheet1”).Cells(1, 1) = Title

    End Sub

    I don’t understand how to use the With statements to make the 2 in CO2 subscript.

    Thank you.

  4. Jon Peltier says

    Here is the relevant code:

    Sub SubScriptTitle()
  Dim sTitle As String
  
  sTitle = "CO2 Emissions"
  
  With Sheets("Sheet1").Cells(1, 1)
    .Value = sTitle
    .Characters(3, 1).Font.Subscript = True
  End With
End Sub

     
    Here, With means take Sheets(“Sheet1”).Cells(1, 1) and perform the following actions with it. The first action is to put the title into the cell, the second is to take a substring starting at character 3, one character long, and subscript it.

  5. Leah says

    Hi Jon,

    I am trying to rebuild a complex model that contains macros. It was built almost 10 years ago and has been added onto in bits and pieces, so I wanted to start from scratch. I am having a hard time figuring out what event in the excel model triggers which macros. Also, some of the macros start without a “sheets” reference, so I’m not sure what tab they are looking at.

    Do you have any suggestions for identifying the triggers for each macro and also how to see exactly what sheet/s they are pointing to?

    Thank you!
    Leah

  6. Jon Peltier says

    I don’t know what you mean, which sheets the macros are pointing to. Are the macros in regular code modules or in worksheet code modules?

    MZ-Tools, a free VBE add-in, has some features that might help. One gives you a list of procedures that point to the one you’re in. This is something that would help you greatly. You can also look at event procedures and see where they lead.

  7. Joel says

    Hi,
    I am having trouble with this macro maybe you guys can help me.
    I am trying to clear the content from one cell base on the color of another.
    Keep getting error message.

    Sub delete_shorts()
    With ActiveSheet
       If .Range(“A10”).Interior.ColorIndex = 3 Then
          .Range(“B10:F10”).ClearContents
        End If
    End With
    End Sub

    Any help will be greatly appreciated.
    Thank you and have a great day!!

  8. Jon Peltier says

    Not enough information. What line is highlighted when the error occurs, and what is the error description (not just the error number)?

    Note that ColorIndex is an old Excel 2003 and earlier way to indicate colors, and is not likely to work in 2007 or 2010, at least not the way you expect.

  9. Michelle says

    Hi, After 2 years of study I am on my last module which I find not that relevant to bookkeeping however I need it to get my certificate. I have created a worksheet with a macro to import data etc. but i didn’t print when recording the macro, can I go back to this macro and edit to include printing and if so how do i do that?

    I would be most grateful for any help you can offer?

    Thanks
    Michelle

  11. Parmesh says

    Hi Jon,

    This is Parmesh Gautam…..hope you can help me out in fixing a macro.

    I have created a macro to send mails from excel. This macro is working fine for few employees but for some it is not working and giving the below errors. can you please let me know how to fix this.

    “Compile error – Cannot find project or library”
    and macro stucks at “Set outApp = CreateObject(“outlook.application”)

  12. Jon Peltier says

    Parmesh –

    As described in Getting Answers to your Excel Questions, sometimes there just isn’t time for me to respond to a question, especially one which isn’t related to the topic and which promises to be much more convoluted than it seems (any time you invoke Outlook, you’re asking for convolution).

    Visit one of the many Excel forums (Mr Excel is about the best there is), and post your question, as clearly as you can. You will get much faster and more comprehensive assistance there than here.

    But there may be better help as well. In this case, my colleague Ron de Bruin has written extensively about emailing from Excel. Visit his index at Mail from Excel with Outlook.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Peltier Tech Charts for Excel