Solved

Clean up a recorded macro...

Posted on 2015-01-11
7
49 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
ID: 40543241
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
ID: 40543251
Qlemo -  Thanks for the advice.  I'll do that going forward.  Ron
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 40543258
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:ronadair
ID: 40543275
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 69

Accepted Solution

by:
Qlemo earned 500 total points
ID: 40543285
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
ID: 40543292
Thanks for the tip.  Not sure what the last sentence means, but thanks.
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 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