With…End With Statement

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.

 Syntax

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()

Range(“A1”).Activate

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()

Range(“A1”).Activate

With ActiveCell.Font

.Bold = True

.Color = vbRed

.Italic = True

End With

End Sub

Example 2

 Sub MyFormat()

Range(“C2:E9”).Select

With Selection

.Value = “45”

.Interior.Color = &H569

End With

End Sub

Leave a Reply

Your email address will not be published.

Enjoy the tutorial? Please spread the word :)

Follow by Email
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/04/20/withend-with-statement
YouTube43
YouTube