How to Use With… End With Block in VBA Excel?

The With - End With block in VBA is used to tell VBA that we are going to use the given object and it should consider the properties of the given object only once we use dot operator. We will see examples later in this article.

Syntax With - End With Block

With [Object]

 'Code to alter or use [Object]

 '--

 '--

End With

So now that we know the syntax of With - End With block, let's see its usage.

Example of With - End With Block

Let's say I want to do several changes to range A2:A10.  I want to select this range, change it's fill colors, font style etc. How would I do it generally? Probably like this:

Sub test()

 Range("A1:A10").Select

 Range("A1:A10").Interior.ColorIndex = 8

 Range("A1:A10").Font.Name = "Algerian"

 Range("A1:A10").Font.ColorIndex = 12

 Range("A1:A10").Font.Underline = xlUnderlineStyleDouble

 Range("A1:A10").Copy Range("B1:B10")

 Range("A1:A10").Clear

End Sub

The above selects range A1:A10. Changes the interior color of range to color index 8. Changes the font to Algerian. Changes color of font to color index 12. Underlines the text in range with double underlines. Then it copies the range A1:A10 to range B1:B10 of the same sheet. At last it clears the range A1:A10.

You can notice that to do every operation with range A1:A10, we have to write it every time. Then the dot operator accesses its properties. This decreases the processing speed and increases work overhead to VBA programmers. The alternative to do this is to use a With block: The below code does the same as the above code but faster.

Sub test()

With Range("A1:A10")

 .Select

 .Interior.ColorIndex = 8

 .Font.Name = "Algerian"

 .Font.ColorIndex = 12

 .Font.Underline = xlUnderlineStyleDouble

 .Copy Range("B1:B10")

 .Clear

End With

End Sub


Let's see another example.

If you have created an object of Outlook Mail then you can use that object to initialize all it's properties and use methods.

Set outMail = Outlook.Application.CreateItem(0)

With outMail

 .To = "abcd.mail.com" 'Mandatory. Here you define the destination mail id.

 .cc = "cc.mail.com"  'optional. Cc mail id if you want.

 .BCC = "bcc.mail.com" 'optional. Bcc mail id if you want.

 .Subject = subj 'should have. The massage on mail body.

 .Body = msg  'optional. The massage on mail body.

 .Attachments.Add "C:/exceltip.com\test.xlsx" 

 .Send 

End With

How does it work?

Well, when we write With Range("A1:A10"), vba locks its reference to the object range("A1:A10").  So whenever we write dot (.) operator, VBA lists all the members of this range class which will affect only object Range("A1:A10") or any object you mentioned. The reference lock releases when VBA reads the End With statement.

Nested With Blocks

We can have a block within another block. In the above example we used a range object to select it. Then we used Range.Font several times to work with fonts. This is again repetitive. The above code can be written like this too:

Sub test()

With Range("A1:A10")

 .Select

 .Interior.ColorIndex = 8

 

'Using another With within a With Block

 With .Font

 .Name = "Algerian"

 .ColorIndex = 12

 .Underline = xlUnderlineStyleDouble

 End With

 

 .Copy Range("B1:B10")

 .Clear

End With

End Sub

The inner with block should refer to an object that is a sub member of the outer object. We can refer to the outer with as object with and inner With as Child With. When you start inner with, the object should be written with the preceding dot operator.

Once you are a child, you can't access parent specific properties. For example, the below code is wrong.

Sub test()

With Range("A1:A10")

 .Select

 .Interior.ColorIndex = 8

 

'Using another With within a With Block

 With .Font

 .Name = "Algerian"

 .ColorIndex = 12

 .Underline = xlUnderlineStyleDouble

 

'The below code will generate error as copy and clear methods does not belong to font class. 

 .Copy Range("B1:B10")

 .Clear

 

End With

End With

End Sub

Fully Qualified With Block

If I want to make some changes with the font of range  A1:A10 of sheet2 in the code containing the workbook then we should use a fully qualified block.

The below two codes will work the same.

Sub test2()

With ThisWorkbook

 With .Sheets("Sheet2")

  With .Range("A1:A10")

   With .Font

     .Name = "Algerian"

     .ColorIndex = 12

     .Underline = xlUnderlineStyleDouble

   End With

  End With

 End With

End With

End Sub

 

'Fully qualified with block

Sub test3()

 With ThisWorkbook.Sheets("Shee2").Range("A1:A10").Font

   .Name = "Algerian"

   .ColorIndex = 12

   .Underline = xlUnderlineStyleDouble

 End With

End Sub

You can see the difference. So when you know that you are going to use multiple methods and properties from an object, give a fully qualified name at the beginning. If you will be using child objects then use the previous approach. Experienced programmers use both methods at suitable situations.

So yeah guys, this is how we use With - End With block in VBA. I hope I was explanatory enough and this article helped you understand the concept of With - End With. If you have any doubts regarding this article, or any other topic, ask me in the comments section below. I'll be happy to help you.

Related Articles:

VBA variables in Excel| VBA stands for Visual Basic for Applications. It is a programming language from Microsoft. It is used with Microsoft Office applications such as MSExcel, MS-Word and MS-Access whereas VBA variables are specific keywords.

Excel VBA Variable Scope| In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers.

ByRef and ByVal Arguments | When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in the original argument.

Delete sheets without confirmation prompts using VBA in Microsoft Excel | Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.

Add and Save New Workbook Using VBA In Microsoft Excel 2016| In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc

Display A Message On The Excel VBA Status Bar| The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering.

Turn Off Warning Messages Using VBA In Microsoft Excel 2016| This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube