Example of a WITH Statement

Hello Cam,

Hope you enjoyed your Microsoft Access VBA course with Best STL.

Thank you for your question regarding providing an example of a WITH statement.

First of all the rules:

With...End With allows you to perform a series of statements on a specified object without requalifying the name of the object. If the qualification path to the object is long, using With...End With can improve your performance. A With block also reduces repetitive typing of the qualification path and the risk of mistyping one of its elements.

For example, to change a number of different properties on a single object, place the property assignment statements inside the With...End With, referring to the object only once instead of for each property assignment.

Part of the code required to create a PivotTable would be as follows:

With pvtTable

.PivotFields("Field1").Orientation = xlRowField
.PivotFields("Field2").Orientation = xlColumnField
.PivotFields("Field3").Orientation = xlDataField
.PivotFields("Sum Field").Function = xlAverage

End With

So, in the above example the object is pvtTable and the various PivotFields properties have been set, followed by an End With.

Another example:
Nest with control structures
Public Sub WithExample()

With ActiveSheet.Range("b2:d8")
.Value = 72
With .Font
.Bold = True
.Name = "Verdana"
.Size = 16
End With
.Interior.Color = vbGreen
End With

End Sub

The last example shows how to nest WITH statements.

These examples are both Excel related but I'm sure that they give you sufficient information for you to be able to create your own.

I hope this resolves your question. If it has, please mark this question as resolved.

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Rodney
Microsoft Office Specialist Trainer