B.3. With Statement

I find myself using the With statement more and more. Anytime you refer to properties and methods of an object in multiple lines, you can save a lot of effort by using a With statement. The following example uses the With statement on a Worksheet object to change the Name of the worksheet, change the color of the tab for the worksheet, and then select cell A10. When you type this, notice that when you type . at the beginning of each line within the With statement, you get the same type of help that you get if you type . after the variable name. This example should be run in Excel.

 Dim xlWs As Excel.Worksheet Set xlWs = Excel.ActiveSheet With xlWs   .Name = "Test Sheet"   .Tab.ColorIndex = 33   .Range("A10").Select End With Set xlWs = Nothing