Improve company productivity with a Business Account.Sign Up

x
?
Solved

Clean up a recorded macro...

Posted on 2015-01-11
7
Medium Priority
?
82 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.

Join & Write a Comment

Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
When you have multiple client accounts to manage, it often feels like there aren’t enough hours in the day. With too many applications to juggle, you can’t focus on your clients, much less your growing to-do list. But that doesn’t have to be the cas…
Watch the working video to know how to import Outlook PST/OST files to Amazon WorkMail. Kernel released this tool which is very easy to use and migrate single or multiple PST and OST files to Amazon WorkMail. To know more about Kernel Import PST to …

606 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