Clean up a recorded macro...

I recorded a macro to insert a disclaimer at the end of a run of random number generation (Excel 2010).  It looks to me to be unnecessarily long.  I've attached it below, I'd like someone to clean it up so it includes only the necessary elements.  Also, how do I include the code at the end of the macro that generates the random numbers?
ronadairAsked:
Who is Participating?
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
To add something, it is best to record, then clean up, and then add that to existing code. The cleanup is no requirement, but usually recorded macros are full of Select stuff recorded as you mark cells while recording.
I have to step back in regard of the disclaimer formatting - don't want to get into Shape :D.
0
 
ronadairAuthor Commented:
Sub Disclaimer()
'
' Disclaimer Macro
'

'
    ActiveSheet.Shapes.AddTextEffect(msoTextEffect1, "Your text here", "+mn-lt", 54 _
        , msoTrue, msoFalse, 249.121023622, 177.5149606299).Select
    Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoFalse
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "Values shown were generated from the last model run." & Chr(13) & "They are not indicative of expected values."
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 53). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignCenter
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 36).Font
        .Bold = msoFalse
        .Caps = msoNoCaps
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Shadow.Type = msoShadow21
        .Shadow.Visible = msoTrue
        .Shadow.Style = msoShadowStyleOuterShadow
        .Shadow.Blur = 3.25
        .Shadow.OffsetX = 1.3856406461
        .Shadow.OffsetY = 0.8
        .Shadow.RotateWithShape = msoFalse
        .Shadow.ForeColor.RGB = RGB(0, 0, 0)
        .Shadow.Transparency = 0.599999994
        .Shadow.Size = 100
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .Fill.ForeColor.TintAndShade = 0.1499999762
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 24
        .Line.Visible = msoTrue
        .Line.ForeColor.ObjectThemeColor = msoThemeColorText2
        .Line.ForeColor.TintAndShade = 0
        .Line.ForeColor.Brightness = 0
        .Line.Transparency = 0
        .Line.Weight = 1
        .Line.DashStyle = msoLineSolid
        .Line.Style = msoLineSingle
        .Name = "+mn-lt"
        .Spacing = 0
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(37, 17).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .Caps = msoNoCaps
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Shadow.Type = msoShadow21
        .Shadow.Visible = msoTrue
        .Shadow.Style = msoShadowStyleOuterShadow
        .Shadow.Blur = 3.25
        .Shadow.OffsetX = 1.3856406461
        .Shadow.OffsetY = 0.8
        .Shadow.RotateWithShape = msoFalse
        .Shadow.ForeColor.RGB = RGB(0, 0, 0)
        .Shadow.Transparency = 0.599999994
        .Shadow.Size = 100
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .Fill.ForeColor.TintAndShade = 0.1499999762
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 24
        .Line.Visible = msoTrue
        .Line.ForeColor.ObjectThemeColor = msoThemeColorText2
        .Line.ForeColor.TintAndShade = 0
        .Line.ForeColor.Brightness = 0
        .Line.Transparency = 0
        .Line.Weight = 1
        .Line.DashStyle = msoLineSolid
        .Line.Style = msoLineSingle
        .Name = "+mn-lt"
        .Spacing = 0
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(54, 43). _
        ParagraphFormat
        .FirstLineIndent = 0
        .Alignment = msoAlignCenter
    End With
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(54, 43).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .Caps = msoNoCaps
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Shadow.Type = msoShadow21
        .Shadow.Visible = msoTrue
        .Shadow.Style = msoShadowStyleOuterShadow
        .Shadow.Blur = 3.25
        .Shadow.OffsetX = 1.3856406461
        .Shadow.OffsetY = 0.8
        .Shadow.RotateWithShape = msoFalse
        .Shadow.ForeColor.RGB = RGB(0, 0, 0)
        .Shadow.Transparency = 0.599999994
        .Shadow.Size = 100
        .Fill.Visible = msoTrue
        .Fill.ForeColor.ObjectThemeColor = msoThemeColorBackground2
        .Fill.ForeColor.TintAndShade = 0.1499999762
        .Fill.ForeColor.Brightness = 0
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 24
        .Line.Visible = msoTrue
        .Line.ForeColor.ObjectThemeColor = msoThemeColorText2
        .Line.ForeColor.TintAndShade = 0
        .Line.ForeColor.Brightness = 0
        .Line.Transparency = 0
        .Line.Weight = 1
        .Line.DashStyle = msoLineSolid
        .Line.Style = msoLineSingle
        .Name = "+mn-lt"
        .Spacing = 0
    End With
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Solid
    End With
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.25
        .Solid
        End With
        With Selection.ShapeRange
        Selection.ShapeRange.IncrementLeft -10
        Selection.ShapeRange.IncrementTop 56
        Selection.ShapeRange.IncrementTop 12
    
    End With
    Range("I27").Select
    
End Sub

Open in new window

0
 
ronadairAuthor Commented:
Qlemo -  Thanks for the advice.  I'll do that going forward.  Ron
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
The code does not contain much to clean up. It certainly could get improved somehow, but it isn't worth the effort. With one exception - the following part
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Solid
    End With
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.25
        .Solid
        End With
        With Selection.ShapeRange
        Selection.ShapeRange.IncrementLeft -10
        Selection.ShapeRange.IncrementTop 56
        Selection.ShapeRange.IncrementTop 12
    
    End With
    Range("I27").Select

Open in new window

contains duplicated code, and the last part can be simplified:
    With Selection.ShapeRange.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0.25
        .Solid
   End
   With Selection.ShapeRange
       .IncrementLeft -10
       .IncrementTop 56
       .IncrementTop 12
    End With

Open in new window

The question now is where to place the disclaimer text, and what to use as such? Should be always placed in the I27 cell?
0
 
ronadairAuthor Commented:
The disclaimer text is shown on lines 11 & 12 of the code snippet & should be centered horizontally in the worksheet about 2/3rds of the way down.  Also, to add code to a macro do I just paste the recorded code at the end of the existing macro?
0
 
ronadairAuthor Commented:
Thanks for the tip.  Not sure what the last sentence means, but thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.