How To: Record Your Own Macro

In a recent post, I showed how to copy someone else’s VBA procedure (i.e., “macro”) and paste it into your own workbook. Fortunately you don’t have to rely on others for custom macros. Excel includes a Macro Recorder which captures your actions, so that you can replay them later, saving yourself time and keystrokes.

It’s easy to record and rerun macros. You can start the Macro Recorder in a number of ways.

  • Tools menu > Macro > Record New Macro
  • Record Macro button on the Visual Basic toolbar
  • Record Macro Button

    The Macro Recorder will now record all of your steps until you turn it off, using one of several methods.

    • Tools menu > Macro > Stop Recording
    • Stop Recording button on the Visual Basic toolbar
    • Stop Recording button on the Stop Recording toolbar

    Stop Recording Button  Stop recording Button

    To see your handiwork, go to the VB Editor (VBE). There are three ways to get there:

    • Tools menu > Macro > Visual Basic Editor
    • Visual Basic Editor button on the Visual Basic toolbar
    • Shortcut key combo Alt+F11

    VB Toolbar - VB Editor Button

    If your workbook had no macros to begin with, the Macro Recorder creates a new code module, which you can see in th Project Explorer Window. If the Project Explorer isn’t visible, go to View menu > Project Explorer.

    Project Explorer

    To view the macro you’ve recorded, double click on the new code module.

    Code Module with Recorded Macro

    The macro can be run in a number of ways:

    • Tools menu > Macro > Macros, select the Macro, and click Run
    • Shortcut Key combo Alt+F8, select the Macro, and click Run
    • Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
    • Click in the macro with the mouse and press F5

    Run Macro Button

    This is the first exposure most people have to VBA programming. In a series of upcoming posts, I’ll cover the following VB topics:

     

    Peltier Tech Charts for Excel

    Comments

    1. I need instructions on how to program 1 cell on a worksheet so that when you click on it it deletes the data that has been entered in unprotected cells. Can this be done? I have to present this to my boss the end of next week and he’s a computerphob, the easier it is the better he likes it. Thanks

    2. Bob –

      Here’s a procedure that will clear unprotected cells in the active sheet. Place it in a regular module in the workbook.

      Sub ClearUnlockedCells()
        Dim r As Range
        Dim iCalc As Long
        
        Application.ScreenUpdating = False
        iCalc = Application.Calculation
        Application.Calculation = xlCalculationManual
        For Each r In ActiveSheet.UsedRange.Cells
          If Not r.Locked Then
            r.ClearContents
          End If
        Next
        Application.ScreenUpdating = True
        Application.Calculation = iCalc
      End Sub

       
      Don’t use a cell as the trigger, open the Forms toolbar, and stick a button on the sheet. When the Assign Macro dialog pops up, select the above macro. Then change the caption on the button to something like “Clear Inputs”. Then protect the sheet.

    3. Hi,

      Why the macro recorder doesn’t record any action on objects? For exemple I would like to add a “Round Single Corner Rectangle” and modify some properties of this shape in VBA. The easiest way should be to record a macro and then use some elements of it to write my own code but I got nothing…

      Thanks

    4. Fred –

      Microsoft had to rush at the end of the Office 2007 development cycle to prepare it for release, that they did not have resources to update the macro recorder for the brand new Office shapes. This affects charts too, since the pieces that comprise the charts are also new Office shapes.

      Excel 2010 has a lot of the macro recorder goodies that Excel 2007 lacks.

    5. Thanks for the quick answer.

      In fact I’am currently using Excel 2010 and I have nothing recorded in my macro when, for exemple, I change dimensions of a rectangle . May be there’s an option to select when we work with shapes? Any ideas?

    6. Fred –

      I inserted, moved, and resized a rectangle in Excel 2010 with the macro recorder on, and got this code:

      Sub Macro2()
          ActiveSheet.Shapes.AddShape(msoShapeRectangle, 153.75, 63, 176.25, 63).Select
          Selection.ShapeRange.IncrementLeft 202.5
          Selection.ShapeRange.IncrementTop 89.25
          Selection.ShapeRange.ScaleWidth 0.4127659574, msoFalse, msoScaleFromBottomRight
      End Sub

      In general I prefer using explicit size and position statements:

      With ActiveSheet.Shapes(1)
          .Left = 100
          .Top = 75
          .Width = 125
          .Height = 75
      End With
    7. You were right. I hadn’t noticed yet that IT service had installed Office 2007 instead of 2010 on my new laptop. Sorry for this mistake. I prefer explicit statements too. The macro recorder can be usefull to obtain the exact VBA shape name…
      Thanks again

    8. Hi Jon,
      I had a recorded macro for chart creation in 2003 and it does not work in 2007.
      I had used your dynamic charting by dates procedure to make the chart.
      Can you help ? It stops at the Set ChtObj line.

      Sub MainGraph(wanum As String)

      ‘ Macro1 Macro

      Dim wsItem As Worksheet
      Dim chtObj As ChartObject
      Dim chrObj As Integer
      For Each wsItem In ThisWorkbook.Worksheets

      For Each chtObj In wsItem.ChartObjects

      chtObj.Delete

      Next

      Next

      Set chtObj = ActiveSheet.ChartObjects.Add(Left:=250, Width:=700, Top:=170, Height:=400)

      With chtObj.Chart
      ‘ remove extra series
      Do Until .SeriesCollection.Count = 0
      .SeriesCollection(1).Delete
      Loop
      End With

      chrObj = 1
      ‘Charts.Add

      With chtObj.Chart
      .ChartType = xlLine
      .SeriesCollection.NewSeries.XValues = “=APChart.xls!ChartDates”

      .SeriesCollection(1).Name = “Estimate”
      .SeriesCollection(1).Values = “=APChart.xls!ChartFirmA”

      .SeriesCollection.NewSeries.XValues = “=APChart.xls!ChartDates”

      .SeriesCollection(2).Name = “Actuals”
      .SeriesCollection(2).Values = “=APChart.xls!ChartFirmB”

      .Location Where:=xlLocationAsObject, Name:=”NewAP”
      .Parent.Name = “CHART” & chrObj
      End With

      With ActiveChart
      .HasTitle = True
      .ChartTitle.Characters.Text = “Estimate/Actuals ” & wanumb & Space(5) & “(As of ” & Date & “)”
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = “Years/Months”
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = “Hours”
      End With
      ActiveChart.SeriesCollection(1).Select
      With Selection.Border
      .Weight = xlThin
      .LineStyle = xlAutomatic
      End With
      With Selection
      .MarkerBackgroundColorIndex = 5
      .MarkerForegroundColorIndex = 5
      .MarkerStyle = xlDiamond
      .Smooth = False
      .MarkerSize = 6
      .Shadow = False
      End With

      ActiveChart.SeriesCollection(2).Select
      ActiveChart.SeriesCollection(2).ChartType = xlColumnClustered
      With Selection.Interior
      .ColorIndex = 1
      .Pattern = xlSolid
      End With

      ActiveChart.ChartArea.Select
      With Selection.Border
      .Weight = 1
      .LineStyle = -1
      End With
      Selection.Interior.ColorIndex = xlAutomatic
      Sheets(“New787”).DrawingObjects(“CHART” & chrObj).RoundedCorners = False
      Sheets(“New787”).DrawingObjects(“CHART” & chrObj).Shadow = False
      ActiveChart.SeriesCollection(2).Select
      With Selection.Border
      .Weight = xlThin
      .LineStyle = xlAutomatic
      End With
      Selection.Shadow = False
      Selection.InvertIfNegative = False
      With Selection.Interior
      .ColorIndex = 1
      .Pattern = xlSolid
      End With

      ActiveSheet.ChartObjects(“CHART” & chrObj).Activate
      ActiveChart.ChartArea.Select
      ActiveChart.Axes(xlValue).Select
      Selection.TickLabels.NumberFormat = “0”
      ActiveChart.ChartArea.Select
      ActiveWindow.Visible = False
      ‘Windows(“APChart.xls”).Activate
      ‘Range(“U40”).Select
      ActiveSheet.Shapes(“CHART” & chrObj).Select
      Selection.Locked = False
      ActiveSheet.Protect
      ActiveWindow.ScrollRow = 7
      With ActiveChart
      .Legend.Top = 5
      .Legend.Left = 5
      End With

      ‘Call ExportChart
      ‘ ActiveSheet.Shapes(“CHART” & chrObj).Select
      Windows(“APChart.xls”).Activate
      Range(“A3”).Select
      End Sub

    9. Asad –

      If there’s an unprotected active sheet, that line of code ought to work.

    10. how to make makro to put the current date and current time in specifed cell

    11. Steve –

      Following guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.

    Trackbacks

    1. […] document.write(''); You are welcome. In case it is not one of the pages that you found, have a look at the following: Quick Excel Chart VBA Examples This gives advice about creating charts using the macro recorder. See also: How To: Record Your Own Macro – Peltier Tech Blog […]

    Speak Your Mind

    *

    Peltier Tech Charts for Excel 3.0