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
Certain comments are subject to moderation and may not appear immediately. You can use HTML tags in your comment. If you include a greater-than or less-than sign or anything else that could be interpreted as HTML, your comment won't look nice. You need to escape those characters. To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].
Leave a comment