Borders in VBA Excel

If you are good at formatting in excelFormatting In ExcelFormatting is a useful feature in Excel that allows you to change the appearance of the data in a worksheet. Formatting can be done in a variety of ways. For example, we can use the styles and format tab on the home tab to change the font of a cell or a table.read more, you can call yourself an “Excel Beautician.” Formatting is vital in making the reports look appealing to the end-user or readers. We hope you are well aware of the formatting techniques in the regular worksheet. Formatting through VBA codingFormatting Through VBA CodingConditional formatting is also possible in Excel VBA programming by using the ‘Format Conditions Collection’ macro/procedure. A conditional format is one that is applied only to cells that meet certain criteria, such as values above a certain threshold, positive or negative values, or values with a specific formula, and so on.read more requires a considerable amount of VBA coding languageVBA Coding LanguageVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more. This article will show you one of the most underrated formatting techniques, i.e., applying Excel VBA borders.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA Borders (wallstreetmojo.com)

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 styles, making them available as an add-on to the excelAdd-on To The ExcelAn add-in is an extension that adds more features and options to the existing Microsoft Excel.read more ribbon, makes the job easier whenever we want to apply VBA borders.

In the worksheet under the HOME tab, we have a border option. In addition, we have various options if you click on the drop-down list in excelDrop-down List In ExcelA drop-down list in excel is a pre-defined list of inputs that allows users to select an option.read more.

But in VBA, first, we must decide on the cell or range of cells. Then, we will apply the Excel VBA borders formatting styles. So, we can refer to the range of cells or cells using VBA RANGECells Using VBA RANGEThe range property of VBA is used to refer to any data, cells, or selection. It is an inbuilt property that allows us to access any part of the worksheet. Using the range property for a single cell-like range is referred to as range cells.read more object. So, for example, if you want to change the border of 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, we must open parenthesis to see all border formatting options..

Sub Border_Example1()

Range(“B5”).Borders(

End Sub

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

After selecting the border style, we need to select the characteristic we want to work with. One of the characters we need to use here is “LineStyle,” so select the “LineStyle” property.

Sub Border_Example1()

Range(“B5”).Borders (xlEdgeBottom).li

End Sub

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

Put an equal sign and select “XlLineStyle” enumeration.

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle =XlLineStyle.

End Sub

Put a dot to see all the available border styles.

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

Now, we have selected the option of “xlDouble.”

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 cell B5.

Line Type: “xlContinuous”.

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous

End Sub

Result:

Line Type: “clash.”

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDash

End Sub

Line Type: “xlDashDot.”

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDashDot

End Sub

Line Type: “xlDashDotDot”.

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDashDotDot

End Sub

Line Type: “xlDot.”

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlDot

End Sub

Line Type: “xlLineStyleNone”.

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlLineStyleNone

End Sub

It will remove the border of the specified cell.

Line Type: “xlSlantDashDot”.

Sub Border_Example1()

Range(“B5”).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlSlantDashDot

End Sub

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

We can also change the cell’s borders using the VBA Borders Around method. Once we mention the range of cells or cells, 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, border weight, and many more things we can do with this method.

Sub Border_Example1()

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

End Sub

It 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.

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

This article has been a guide to VBA Borders. Here, we learn how to set borders with Excel VBA coding, practical examples, and 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 VBAExcel VBA EndConditional Formatting for Blank Cells