[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Clean up a recorded macro...

Posted on 2015-01-11
7
Medium Priority
?
79 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
6 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 72

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
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 

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 72

Accepted Solution

by:
Qlemo earned 2000 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

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

Question has a verified solution.

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

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…
Suggested Courses

612 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