Nate Oliver's blog

Two days ago we ventured into the Excel 2007 and 2010 Macro recorder, we have our Shape-recording, back - very nice. That entry was located here:

http://msmvps.com/blogs/nateoliver/archive/2010/04/04/recording-with-shapes-excel-2007-v-excel-2010.aspx

You might have noticed that I thought the code is slop, based on a few comments I made...

It is. It may be perfectly satisfactory for what you're doing, whatever that may be, but it's not quite how I would do it. So here's the recorded version:

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Shapes.AddShape(msoShapeSmileyFace, 180, 45, 147, 137.25).Select
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(255, 255, 0)
        .Transparency = 0
        .Solid
    End With
    Range("I5").Select
End Sub

Here's the home-cooked version:

Sub foo()
Dim xlShp As Shape
Application.ScreenUpdating = False

With Worksheets(1).Range("C2")
    Set xlShp = .Parent.Shapes.AddShape(msoShapeSmileyFace, _
        .Left, .Top, .Width * 4, .Height * 12)
End With

xlShp.Fill.ForeColor.RGB = RGB(255, 255, 0)
Application.ScreenUpdating = True
Set xlShp = Nothing
End Sub
 

In the case of the latter, we've done a few different things. We've taken control as to which Worksheet this thing lands on, the size, in terms of Columns of Rows it will be, in a language we can understand, 4 Columns wide, 12 Rows large. And whatever else we want. It's not to say you don't want the ActiveSheet, but there's a distinct possibility you don't.

It's great to have the Macro Recorder back, but, in general, we're using this for syntax purposes, not true and tried production-level code.

This was tested with Excel 2010.