Using With Blocks A handy and useful way of using dot notation concerns what are termed With-End With blocks, or simply With blocks. You use the keyword With, teamed with an object of some sort, to indicate that one or more subsequent statements pertain to other objects (or properties or methods) that belong to the object named in the With. Everything from the With statement through the accompanying End With statement is considered part of the With block. It's a lot easier to understand With blocks by looking at them than by reading about them. Here's an example, first with no With block and then using one: ActiveSheet.Rows(2).Font.ColorIndex = 3 ActiveSheet.Rows(2).Font.Bold = True
These two statements, when executed, turn the font color of the active sheet's second row to red, and its style to bold. With ActiveSheet.Rows(2) .Font.ColorIndex = 3 .Font.Bold = True End With
These four statements do the same thing as the prior two. Inside the block that is, after the With statement whatever begins with a dot is deemed to belong to the object that's cited in the With statement. In the prior example, the font, its color index, and its style are taken to belong to the second row of the active sheet. You can extend the reach of the With statement beyond what was shown earlier. Because the Font property starts both the statements after the With statement, it could be moved up to the outer With: With ActiveSheet.Rows(2).Font .ColorIndex = 3 .Bold = True End With
Nested With Blocks With statements can be nested within other With statements. The requirement, of course, is that the element used in the inner With must belong to the element used in the outer With. Here's an example that doesn't use a nested With, but could: With ActiveSheet.Cells(10, 12) .VerticalAlignment = xlTop .WrapText = True .Font.Name = "Times New Roman" .Font.Size = 12 End With
This code sets four properties for cell L10 on the active sheet: Its value is displayed beginning at the top of the cell, lengthy text is wrapped, its font is Times New Roman, and its font size is 12. Because the cell's Font property is used twice within the block, it makes sense to revise it as a nested With, as follows: With ActiveSheet.Cells(10, 12) .VerticalAlignment = xlTop .WrapText = True With .Font .Name = "Times New Roman" .Size = 12 End With End With
Notice the second inner With block. The fact that its element, .Font, begins with a dot means that it belongs to a prior With here, cell L10 on the active sheet. Understanding the Rationale for With Blocks With blocks are touted as a way to speed up processing. The notion is that it takes more time to interpret these two statements ActiveSheet.Rows(2).Font.ColorIndex = 3 ActiveSheet.Rows(2).Font.Bold = True
than to interpret this With block, even though it has two additional statements: With ActiveSheet.Rows(2).Font .ColorIndex = 3 .Bold = True End With
Although it's true that there's an increase in speed, for most purposes, the increase is imperceptible. The following two subroutines were run on a 1.8GHz Pentium 4. The first one has no With block and took 17 seconds to run to conclusion on a fresh worksheet: Sub NoWith() Dim i As Integer For i = 1 To 10000 ActiveSheet.Cells(i, 1).VerticalAlignment = xlTop ActiveSheet.Cells(i, 1).WrapText = True ActiveSheet.Cells(i, 1).Font.Name = "Times New Roman" ActiveSheet.Cells(i, 1).Font.Size = 12 Next i End Sub
The second one uses nested With blocks and took 14 seconds, again on a fresh worksheet: Sub WithWiths() Dim i As Integer For i = 1 To 10000 With ActiveSheet.Cells(i, 1) .VerticalAlignment = xlTop .WrapText = True With .Font .Name = "Times New Roman" .Size = 12 End With End With Next i End Sub
Fourteen seconds versus 17 seconds is an 18% increase in speed. But if you format 100 rows instead of 10,000 you're unlikely to notice that 18%. And some tasks are not sped up at all by using With blocks. With blocks are better thought of as a way to encourage you to structure your code more tightly. They save you keystrokes, even though they always add two statements to the code (the With and the End With). Because you don't need to repeatedly type, or to copy and paste, the element in the With statement, you find yourself using With blocks as a timesaver. One consequence is better structure in your code. For example, it's not at all unusual to enter a VBA statement that sets some object's property: a cell's font size, or the maximum value shown on a chart axis, or the number format property of a pivot table's data field. Subsequently, 10 statements farther down in your code, you decide to set some other property belonging to the same object: the cell's height or the chart axis's text alignment, or the data field's summary statistic. It's tempting to just type the statement that would set the property. But if you have in mind your later problems maintaining the code, or debugging it, or walking someone else through it you'll go back and put that statement along with the earlier statement in a With block. When you can rationally do so, it's best to keep statements that operate on the same object together. As a bonus, you'll find yourself doing less typing after you've established the With block and the less typing you do, the better. | 