While it is common to declare many variables and use them individually throughout code, this is not always the best practice. So using with statement we can execute a series of statements that repeatedly refer to a single object.

 Simply, if we have set of statements to be apply on a single object so it is better to make it with statement instead of mention that object again and again.


With objectExpression

‘ Set of Statements here

End With

By using With…End With, you can perform a series of statements on a specified object without specifying the name of the object multiple times.

So let’s take an example that use set of operation on cell A1

Sub MyData()


ActiveCell.Font.Bold = True
ActiveCell.Font.Color = vbRed
ActiveCell.Font.Italic = True

End Sub

No let’s do the same process using with… End With Statements

Sub MyData()


With ActiveCell.Font

.Bold = True

.Color = vbRed

.Italic = True

End With

End Sub

Example 2

 Sub MyFormat()


With Selection

.Value = “45”

.Interior.Color = &H569

End With

End Sub

author image

About admin

You Might Also Like...

VBA Hirdesh Bhardwaj, Hirdesh Bhardwaj Book
HTML Links

1 Comment

  1. good read…

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email