With...End With Blocks

The With…End With block instruction enables you to perform multiple operations on a single object. This is another way to make the code execute more quickly and code styles more efficient.

The following procedure will format the selected cells with the font ‘Times New Roman’, font size 12, Bold, Italic and the colour Blue.

Sub ChangeFont()
    Selection.Font.Name = "Times New Roman"
    Selection.Font.Size = 12
    Selection.Font.Bold = True
    Selection.Font.Italic = True
    Selection.Font.ColorIndex = 5
End Sub

The above procedure can be rewritten using a With…End With block as follows:

Sub ChangeFont()
    With Selection.Font
        .Name = "Times New Roman"
        .Size = 12
        .Bold = True
        .Italic = True
        .ColorIndex = 5
    End With
End Sub

Using the this type of block, your code is cleaner and easier to maintain. The With...End With block encapsulates the object and member without the need to repeat unnecessary (duplicate) code.

If fact, when you record a macro and you navigate through the dialog boxes making various changes before choosing the OK button you in fact capture the code using the above structure. Try the Font Dialog box whilst recording the macro.

Next Topic: User Defined Functions (UDF's)

Want to teach yourself Access? Free online guide at About Access Databases

VBA Keywords: Selection, Name, Font, Bold, Italic, ColorIndex and Size..