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

author image

About admin

You Might Also Like...

The FOR Loop
VBA Hirdesh Bhardwaj, Hirdesh Bhardwaj Book
Data types in Mysql / Mariadb

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
Facebook
Facebook
Google+2k
Google+
http://hirdeshbhardwaj.com/2017/04/20/withend-with-statement
YouTube80
YouTube