WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA With

VBA With

With Statement in Excel VBA

With statement is used in VBA to access to all the properties and methods of a mentioned object. We need to supply which VBA object we are referring to first then close the With statement with End With, then inside this statement. We can perform all the property changes and methods of the mentioned object.

Below is the syntax of With Statement in VBA.

With [OBJECT]

     [Code… What needs to be done?]

End With

The object is nothing but a range of cells or cells we are referring to, and then we can change the properties and perform all the methods associated with that specific cell or cells.

How to Use With Statement in Excel VBA?

Below are some examples to use With Statement in Excel VBA.

You can download this VBA With Excel Template here – VBA With Excel Template

Example #1

Assume you have a certain value in A1 cell, I have entered the text as “Excel VBA” in cell A1.

Excel VBA With Statement Example 1.1

Now for this cell, I need to do some tasks, i.e., formatting in excel.

I want to change the font size, font name, and interior color, insert a border, etc.… Typical what we do is we first refer the cell by using VBA RANGE object.

Code:

Sub With_Example1()

    Range ("A1")

End Sub

Excel VBA With Statement Example 1.2

Now to change font size, we access the “font” property of this cell.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Excel VBA With Statement Example 1.3

Under FONT property, we access the Size property and enter size by putting an equal sign.

Code:

Sub With_Example1()

    Range("A1").Font.Size = 15

End Sub

Excel VBA With Statement Example 1.4

Now similarly, we do other formatting tasks, as shown below.

Code:

Sub With_Example1()

    Range("A1").Font.Size = 15
    Range("A1").Font.Name = "Verdana"
    Range("A1").Interior.Color = vbYellow
    Range("A1").Borders.LineStyle = xlContinuous

End Sub

Excel VBA With Statement Example 1.5

This will do all the mentioned tasks, but if you look at the code to perform each every formatting activity, we have supplied the cell address every time. This makes the code look lengthy and time-consuming.

Now we will use VBA With a statement to reduce the entry of cell address every time. Open WITH statement in Excel VBA and supply cell address.

Code:

Sub With_Example1()

    With Range("A1")

End Sub

Excel VBA With Statement Example 1.6

Inside the With statement, put a dot to see all the properties and methods of cell A1.

Excel VBA With Statement Example 1.7

Now first formatting activity is changing font size, so access FONT and under this access SIZE property.

Code:

Sub With_Example1()

    With Range("A1")
         .Font.Size = 15

End Sub

Example 1.8

Similarly, supply other formatting codes and close VBA With Statement.

Code:

Sub With_Example1()

    With Range("A1")
         .Font.Size = 15
         .Font.Name = "Verdana"
         .Interior.Color = vbYellow
         .Borders.LineStyle = xlContinuous
    End With

End Sub

Example 1.9

Run the code to see all the formatting in the mentioned object, i.e., cell A1.

VBA With Example1 - Output

So, all the formatting applied to the cell. Look how cool this technique is.

Example #2

For example, if you want to change all the properties related to the font, you can mention the cell and FONT property.

Code:

Sub With_Example2()

    With Range("A1").Font

    End With

End Sub

Example 2.1

Inside the VBA With Statement, we can see the IntelliSense list. It will show properties and methods related to FONT property only.

Example 2.2

We can perform any set of activities with this now.

Code:

Sub With_Example2()

    With Range("A1").Font
         .Bold = True 'Font will be Bold
         .Color = vbAlias 'Font color will be Alias
         .Italic = True 'Font will be italic style
         .Size = 20 ' Font size will be 20
         .Underline = True 'Font will be underlined
    End With

End Sub

Example 2.3

The result of this will be as shown below.

VBA With Example 2- Output

Example #3

The below code will access only cell border-related properties.

Code:

Sub With_Example3()

    With Range("B2").Borders
         .Color = vbRed 'Border color will be red
         .LineStyle = xlContinuous 'Full border
         .Weight = xlThick 'Thick border
    End With

End Sub

Example 3.1

The result of this code is as follows.

VBA With Example 3 - Output

Things to Remember

  • With statement is used to minimize the code.
  • We need to supply object first for With statement.
  • Once the specific object is supplied, we can access only that object’s properties and methods.

Recommended Articles

This has been a guide to VBA With. Here we discuss how to use With…End With Statement in Excel VBA along with examples and downloadable excel sheet. You can learn more about VBA from the following articles –

  • VBA Delete Sheet
  • VBA If Else
  • VBA Type Statement
  • Named Range in VBA
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA With Excel Template

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More