WallStreetMojo

WallStreetMojo

WallStreetMojo

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

VBA Borders

Borders in VBA Excel

We use borders in excel to highlight our data, in any dashboard, it is very important skill to represent the important data and it is done by borders, borders are a property in VBA which can be accessed by using the range method and giving the appropriate border style as we know there are different border styles.

If you are good at formatting in excel, then you can call yourself an “Excel Beautician.” To make the reports looks appealing to the end-user or readers, formatting plays a vital role in it. I hope you are well aware of the formatting techniques in the regular worksheet. Formatting through VBA coding requires a considerable amount of VBA coding language. In this article, we will show you one of the most underrated formatting techniques i.e., applying excel VBA borders.

VBA Borders

Examples of Borders in VBA

Let us take a look at some examples to understand this in a better manner.

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

Example #1 – Apply VBA Borders with Coding

Creating a macro to apply excel VBA borders with different styles, making them available as an add-on to the excel ribbon, makes the job easier whenever we want to apply VBA borders.

Every cell in a worksheet has borders and background colors. By default, every cell has no border and background color.

In the worksheet under the HOME tab we have, we have a border option, and if you click on the drop-down list in excel, we have a variety of options.

VBA Borders Example 1

But in VBA first thing we need to do is to decide the cell or range of cells we are going to apply the excel VBA borders formatting styles. So we can refer to the range of cells or cells using VBA RANGE object. For example, if you want to change the border of the cell B5, then you can write the code like this.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Range(“B5”)

Then we need to access the “Borders” property.

Code:

Sub Border_Example1()

  Range("B5").Bo

End Sub

VBA Borders Example 1-1

After applying the excel VBA “Borders” property, open parenthesis to see all the kinds of border formatting options.

Code:

Sub Border_Example1()

  Range("B5").Borders(

End Sub

VBA Borders Example 1-2

Here we have xlDiagonalDown, xlDiagonalUp, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlInsideHorizontal, and xlInsideVertical.

VBA Borders Example 1-3

After the border style has been selected, we need to select the characteristic we want to work with. One of the characters we need to use here is “Line Style,” so select the “Line Style” property.

Code:

Sub Border_Example1()

  Range("B5").Borders (xlEdgeBottom).li

End Sub

VBA Borders Example 1-4

Once the line style property is selected, we need to set the line style type of line or type of borders in VBA we are going to apply.

Put equal sign and select “XlLineStyle” enumeration.

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle =XlLineStyle.

End Sub

VBA Borders Example 1-5

Put dot to see all the available border styles.

Code:

VBA Borders Example 1-6

We have many options here. xlContinuous, xldash, xlDashDot, xlDashDotDot, xlDot, xlDouble, XlLineStyleNone, and xlSlantDashDot.

Ok, now I have selected the option of “xlDouble.”

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDouble

End Sub

Now, if I run the code, it will apply the Double line to the bottom of the cell B5.

VBA Borders Example 1-7

Line Type:  “xlContinuous”.

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous

End Sub

Result:

VBA Borders Example 1-8

Line Type:  “clash.”

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDash

End Sub

Result:

VBA Borders Example 1-9

Line Type:  “xlDashDot.”

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDashDot

End Sub

Result:

VBA Borders Example 1-10

Line Type:  “xlDashDotDot”.

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDashDotDot

End Sub

Result:

VBA Borders Example 1-11

Line Type:  “xlDot.”

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDot

End Sub

Result:

Example 1-12

Line Type:  “xlLineStyleNone”.

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlLineStyleNone

End Sub

Result:

Example 1-13

This will remove the border of the specified cell.

Line Type:  “xlSlantDashDot”.

Code:

Sub Border_Example1()

  Range("B5").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlSlantDashDot

End Sub

Result:

Example 1-14

Example #2 – Change the Border Using VBA Borders Around Method

We can also change the borders of the cell by using the VBA Borders Around method. Once the range of cells or cells is mentioned, we need to access the VBA Borders Around method.

Example 2

Open parenthesis to see all the parameters.

Range(“B5”).BorderAround([Line Style], [Weight as xlBorderWeight], [ColorIndex], [Color], [Theme Color])

We can mention the line style, color of the line, border weight, and many more things we can do with this method.

Code:

Sub Border_Example1()

  Range("B5").BorderAround LineStyle:=xlContinuous, Weight:=xlThick

End Sub

This will change the Line Style to xlContinuous.

LineStyle:=xlContinuous

The weight of the Border is thick.

Weight:=xlThick

And the result of this code is as below.

Example 2-1

Like this using excel VBA Borders and Border Around property and method, we can change the border and elements of borders through VBA coding.

Recommended Articles

This has been a Guide to VBA Borders. Here we learn how to set Borders with Excel VBA Coding along with practical examples & downloadable code templates. You may also have a look at other articles related to Excel VBA –

  • What are Global Variables in Excel VBA?
  • Examples to Create a Pivot Table in VBA
  • Excel VBA End
  • Conditional Formatting for Blank Cells
1 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ 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 Borders Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More