Nesting With Statements

With..End With blocks are a good way to shorten your code and make it more readable. They also speed up execution because VBA only has to evaluate the object once. You can nest With blocks, just be sure you know which With block you’re in. Indenting your code helps in this regard.

Sub UseNestedWith()
With Workbooks("NestedWiths.xls")
With .Worksheets("Sheet1")
With .Range("A1")
.Value = "This applies to the range object"
.Offset(1, 0).Formula = "=WithTest"
End With
.Names.Add "WithTest", "This applies to the worksheet object"
End With
.BuiltinDocumentProperties("Subject") = "This applies to the workbook object"
End With
End Sub


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.