Solved: Improving Text Box Code

[Belch]

Hi all,

I have some code that adds a text box and then amends the text within it (font, size, etc).
I actually just recorded the action to a macro and then used that code. I have noticed the resulting code uses a lot of With statements and sets a lot of properties. I have also noticed that documents with this code in seem to take longer to load (the code is run when the doc opens).
Could the speed difference be due to the inefficiency of the code? If so, any suggestions on how to improve it would be appreciated.

The code at the moment:

With ActiveWindow.Selection
.GoTo What:=wdGoToPage, Which:=wdGoToFirst
End With
ActiveDocument.Shapes.AddTextbox(msoTextOrientationHorizontal, 175, _
850, 245, 31).Select
Selection.ShapeRange.TextFrame.TextRange.Select
With Selection
.Collapse
.ShapeRange.Select
With .Font
.Name = "Arial"
.Size = 12
.Bold = True
.Italic = False
End With
.ParagraphFormat.Alignment = wdAlignParagraphCenter
.TypeText Text:="N O N E"
With .ShapeRange
.Select
.ScaleHeight 0.62, msoFalse, msoScaleFromTopLeft
.IncrementTop -149.8
.Fill.Visible = msoFalse
.Fill.Transparency = 0#
With .Line
.Weight = 0.75
.DashStyle = msoLineSolid
.Style = msoLineSingle
.Transparency = 0#
.Visible = msoFalse
End With
.LockAspectRatio = msoFalse
.Height = 19.45
.Width = 245.5
.TextFrame.MarginLeft = 7.2
.TextFrame.MarginRight = 7.2
.TextFrame.MarginTop = 3.6
.TextFrame.MarginBottom = 3.6
.RelativeHorizontalPosition = wdRelativeHorizontalPositionColumn
.RelativeVerticalPosition = wdRelativeVerticalPositionParagraph
.LockAnchor = False
With .WrapFormat
.AllowOverlap = True
.Side = wdWrapBoth
.DistanceTop = InchesToPoints(0)
.DistanceBottom = InchesToPoints(0)
.DistanceLeft = InchesToPoints(0.13)
.DistanceRight = InchesToPoints(0.13)
.Type = 3
End With
.ZOrder 5
End With
End With

[Killian]

Hi Neil,

There are a few of ways to optimize the code - you can add a couple of lines of code to time the routine so you can see what improves the speed and what doesn't Dim s As Single
Dim f As Single
s = Timer
' some code here
f = Timer
MsgBox f - s
Now, the first thing I would recommend is switching screenupdating off at the start on any routine that add/amends any page content. Updating the screen uses system resource and takes time, albeit fractions of a second, it can add up in a long routine. It also stops the screen flickering which is always nice.Application.ScreenUpdating = False

Next, macro recorder uses the selection object (which is understandable) but we can set up and object variable when we add the text box and refer to that directly. This improves efficiency, convenience and readability.Dim myTextBox As Shape
...
Set myTextBox = ActiveDocument.Shapes.AddTextbox( _
msoTextOrientationHorizontal, 175, 700, 245.5, 18)


Using nested With blocks also helps with readability and speeds up code execution because an object can be resolved once for a number of methods/property changes. A bit of re-organization can help here, but in doing this you have to take care with the order of events with some objects.
I have read some concerns about nesting With blocks but in VB(A) you don't have the same options that other languages offer so it's a case of appropriate use of the available constructs.

Another aspect of recorded macros is that ALL the properties of an object tend to be set exlpicitly when most are not necessary - the default behaviour is often acceptable so you need to go through each item and determine which ones need to be explicitly changed and which are fine left as the default values.
A good illustration of this is that all the line attributes are set, then it's visible property set to false, which makes doing that a bit redundant.

Specifically, with your recording, it looks like you added the textbox, then moved it to the required position and sized it. This should all be done in one go with the add method.

So here's another version of your routine - I've swapped things around a bit and filtered out the surplus settings - as far as I can see it still behaves correctly.
I timed your original code at over 0.1 seconds - the revised version took 0.015 so I think that should speed things up a bitApplication.ScreenUpdating = False
With ActiveWindow.Selection
.GoTo What:=wdGoToPage, Which:=wdGoToFirst
End With
Set myTextBox = ActiveDocument.Shapes.AddTextbox( _
msoTextOrientationHorizontal, 175, 700, 245.5, 18)
With myTextBox
.Line.Visible = msoFalse
With .TextFrame
.MarginLeft = 7.2
.MarginRight = 7.2
.MarginTop = 3.6
.MarginBottom = 3.6
With .TextRange
.Font.Name = "Arial"
.Font.Size = 12
.Font.Bold = True
.Font.Italic = False
.ParagraphFormat.Alignment = wdAlignParagraphCenter
.Text = "N O N E"
End With
End With
End With
Set myTextBox = Nothing
Application.ScreenUpdating = True

[Belch]

Thanks Killian, I've cut my code down like you suggested and it has cut the time to load in half (the documents are loaded through a system we have here at work, the long code was returning ~1.6 but after cutting it down I am getting ~0.8.

Thanks again,