excel-vba Excel-VBA Optimization Using With blocks
Example
Using with blocks can accelerate the process of running a macro. Instead writing a range, chart name, worksheet, etc. you can use with-blocks like below;
With ActiveChart
.Parent.Width = 400
.Parent.Height = 145
.Parent.Top = 77.5 + 165 * step - replacer * 15
.Parent.Left = 5
End With
Which is faster than this:
ActiveChart.Parent.Width = 400
ActiveChart.Parent.Height = 145
ActiveChart.Parent.Top = 77.5 + 165 * step - replacer * 15
ActiveChart.Parent.Left = 5
Notes:
-
Once a With block is entered, object can't be changed. As a result, you can't use a single With statement to affect a number of different objects
-
Don't jump into or out of With blocks. If statements in a With block are executed, but either the With or End With statement is not executed, a temporary variable containing a reference to the object remains in memory until you exit the procedure
-
Don't Loop inside With statements, especially if the cached object is used as an iterator
-
You can nest With statements by placing one With block within another. However, because members of outer With blocks are masked within the inner With blocks, you must provide a fully qualified object reference in an inner With block to any member of an object in an outer With block.
Nesting Example:
This example uses the With statement to execute a series of statements on a single object.
The object and its properties are generic names used for illustration purposes only.
With MyObject
.Height = 100 'Same as MyObject.Height = 100.
.Caption = "Hello World" 'Same as MyObject.Caption = "Hello World".
With .Font
.Color = Red 'Same as MyObject.Font.Color = Red.
.Bold = True 'Same as MyObject.Font.Bold = True.
MyObject.Height = 200 'Inner-most With refers to MyObject.Font (must be qualified
End With
End With
More Info on MSDN
