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 as 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 technique i.e. applying excel VBA borders.

Examples of Borders in VBA

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

Example #1 – Apply VBA Borders with Coding

Creating a macro to apply excel VBA borders with different style making them available as an add-in 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 worksheet under HOME tab we have, we have border option and if you click on the drop-down list we have a variety of options.

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 the range of cell 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.

Range(“B5”)

Then we need to access the “Borders” property.

Code:

Sub Border_Example1()

  Range("B5").Bo

End Sub

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

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

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

Once the line style property 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

Put dot to see all the available border styles.

Code:

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

Ok, now I have selected 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.

Line Type:  “xlContinuous”.

Code:

Sub Border_Example1()

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

End Sub

Result:

Line Type:  “xlDash”.

Code:

Sub Border_Example1()

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

End Sub

Result:

Line Type:  “xlDashDot”.

Code:

Sub Border_Example1()

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

End Sub

Result:

Line Type:  “xlDashDotDot”.

Code:

Sub Border_Example1()

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

End Sub

Result:

Line Type:  “xlDot”.

Code:

Sub Border_Example1()

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

End Sub

Result:

Line Type:  “xlLineStyleNone”.

Code:

Sub Border_Example1()

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

End Sub

Result:

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 #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 cell is mentioned we need to access the VBA Borders Around method.

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

Weight of the Border is thick.

Weight:=xlThick

And the result of this code is as below.

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

VBA Training (3 Courses, 12+ Projects)

• 3 Courses
• 12 Hands-on Projects
• 43+ Hours
• Full Lifetime Access
• Certificate of Completion

LEARN MORE >>