Solved

Clean up a recorded macro...

Posted on 2015-01-11
7
42 Views
Last Modified: 2016-02-25
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?
0
Comment
Question by:ronadair
  • 4
  • 2
7 Comments
 

Author Comment

by:ronadair
Comment Utility
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
 

Author Comment

by:ronadair
Comment Utility
Qlemo -  Thanks for the advice.  I'll do that going forward.  Ron
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:ronadair
Comment Utility
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
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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
 

Author Comment

by:ronadair
Comment Utility
Thanks for the tip.  Not sure what the last sentence means, but thanks.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video discusses moving either the default database or any database to a new volume.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now