Excel VBA, Get Border Properties
In this article I will explain how you can check if a cell has a border or not using VBA for Excel. I have also explained how you can get the different border properties using VBA.
Jump To:
You can download the file and code related to this article here.
Example 1, Check If Border Exists:
In this section I’ve provided a code which checks if the cells in column A have a border in the bottom edge or not. If a border exists the value “True” will be printed in the adjacent cell in column B. If a border doesn’t exist the value “False” will be printed.
In the figure below some of the cells in column A have borders in the bottom edge:
The results after pressing the run button can be seen below:
The code used in this example can be seen below:
Private Sub btnRun_Click()
Dim i As Integer
i = 1
For i = 1 To 23
'checks if a border exists in the bottom edge
If Range(Cells(i, 1), Cells(i, 1)).Borders(xlEdgeBottom).LineStyle _
<> xlNone Then
Cells(i, 2) = "True"
Else
Cells(i, 2) = "False"
End If
Next i
End Sub
The line below checks if a border exists in the bottom edge of the cell:
If Range(Cells(i, 1), Cells(i, 1)).Borders(xlEdgeBottom).LineStyle _
<> xlNone Then
Example 2, Get Border Properties:
In this example the user creates borders for cell B2. After pressing the Run button the border properties for cell B2 is retrieved and printed:
In the figure below diagonals have been added to the borders of cell B2:
In the figure below some of the borders have been colored:
In the figure below the line style and thickness of some of the borders have been changed:
As can be seen from the figures above all the border properties can be retrieved using VBA for Excel. The code used for this example can be seen below:
Private Sub btnRun_Click()
Dim intLineStyle As Integer
Dim intThickness As Long
'get border properties for left edge
If Range("B2").Borders(xlEdgeLeft).LineStyle _
<> xlNone Then
'exists
Cells(5, 2) = "Yes"
'line style
intLineStyle = Range("B2").Borders(xlEdgeLeft).LineStyle
Cells(5, 3) = Get_LineStyle(intLineStyle)
'line weight/thickness
intThickness = Range("B2").Borders(xlEdgeLeft).Weight
Cells(5, 4) = Get_LineThickness(intThickness)
'color
Cells(5, 5) = Range("B2").Borders(xlEdgeLeft).Color
Else
Cells(5, 2) = "No"
Cells(5, 3) = "-"
Cells(5, 4) = "-"
Cells(5, 5) = "-"
End If
'get border properties for top edge
If Range("B2").Borders(xlEdgeTop).LineStyle _
<> xlNone Then
'exists
Cells(6, 2) = "Yes"
'line style
intLineStyle = Range("B2").Borders(xlEdgeTop).LineStyle
Cells(6, 3) = Get_LineStyle(intLineStyle)
'line weight/thickness
intThickness = Range("B2").Borders(xlEdgeTop).Weight
Cells(6, 4) = Get_LineThickness(intThickness)
'color
Cells(6, 5) = Range("B2").Borders(xlEdgeTop).Color
Else
Cells(6, 2) = "No"
Cells(6, 3) = "-"
Cells(6, 4) = "-"
Cells(6, 5) = "-"
End If
'get border properties for right edge
If Range("B2").Borders(xlEdgeRight).LineStyle _
<> xlNone Then
'exists
Cells(7, 2) = "Yes"
'line style
intLineStyle = Range("B2").Borders(xlEdgeRight).LineStyle
Cells(7, 3) = Get_LineStyle(intLineStyle)
'line weight/thickness
intThickness = Range("B2").Borders(xlEdgeRight).Weight
Cells(7, 4) = Get_LineThickness(intThickness)
'line color
Cells(7, 5) = Range("B2").Borders(xlEdgeRight).Color
Else
Cells(7, 2) = "No"
Cells(7, 3) = "-"
Cells(7, 4) = "-"
Cells(7, 5) = "-"
End If
'get border properties for bottom edge
If Range("B2").Borders(xlEdgeBottom).LineStyle _
<> xlNone Then
'exists
Cells(8, 2) = "Yes"
'line style
intLineStyle = Range("B2").Borders(xlEdgeBottom).LineStyle
Cells(8, 3) = Get_LineStyle(intLineStyle)
'line weight/thickness
intThickness = Range("B2").Borders(xlEdgeBottom).Weight
Cells(8, 4) = Get_LineThickness(intThickness)
'line color
Cells(8, 5) = Range("B2").Borders(xlEdgeBottom).Color
Else
Cells(8, 2) = "No"
Cells(8, 3) = "-"
Cells(8, 4) = "-"
Cells(8, 5) = "-"
End If
'get digaonal up border properties
If Range("B2").Borders(xlDiagonalUp).LineStyle _
<> xlNone Then
'exists
Cells(9, 2) = "Yes"
'line style
intLineStyle = Range("B2").Borders(xlDiagonalUp).LineStyle
Cells(9, 3) = Get_LineStyle(intLineStyle)
'line weight/thickness
intThickness = Range("B2").Borders(xlDiagonalUp).Weight
Cells(9, 4) = Get_LineThickness(intThickness)
'line color
Cells(9, 5) = Range("B2").Borders(xlDiagonalUp).Color
Else
Cells(9, 2) = "No"
Cells(9, 3) = "-"
Cells(9, 4) = "-"
Cells(9, 5) = "-"
End If
'get diagonal down border properties
If Range("B2").Borders(xlDiagonalDown).LineStyle _
<> xlNone Then
'exists
Cells(10, 2) = "Yes"
'line style
intLineStyle = Range("B2").Borders(xlDiagonalDown).LineStyle
Cells(10, 3) = Get_LineStyle(intLineStyle)
'line weight/thickness
intThickness = Range("B2").Borders(xlDiagonalDown).Weight
Cells(10, 4) = Get_LineThickness(intThickness)
'line color
Cells(10, 5) = Range("B2").Borders(xlDiagonalDown).Color
Else
Cells(10, 2) = "No"
Cells(10, 3) = "-"
Cells(10, 4) = "-"
Cells(10, 5) = "-"
End If
End Sub
'returns the line style
Private Function Get_LineStyle(ByVal intCode _
As Integer) As String
If intCode = XlLineStyle.xlContinuous Then
Get_LineStyle = "Continious"
Exit Function
ElseIf intCode = XlLineStyle.xlDash Then
Get_LineStyle = "Dash"
Exit Function
ElseIf intCode = XlLineStyle.xlDashDot Then
Get_LineStyle = "Dash Dot"
Exit Function
ElseIf intCode = XlLineStyle.xlDot Then
Get_LineStyle = "Dot"
Exit Function
ElseIf intCode = XlLineStyle.xlDouble Then
Get_LineStyle = "Double"
Exit Function
ElseIf intCode = XlLineStyle.xlSlantDashDot Then
Get_LineStyle = "Slant Dash"
Exit Function
ElseIf intCode = XlLineStyle.xlDashDotDot Then
Get_LineStyle = "Dash Dot Dot"
Exit Function
End If
End Function
'returns the line weight/thickness
Private Function Get_LineThickness(ByVal intCode _
As Integer) As String
If intCode = xlThick Then
Get_LineThickness = "Thick"
Exit Function
ElseIf intCode = xlMedium Then
Get_LineThickness = "Medium"
Exit Function
ElseIf intCode = xlThin Then
Get_LineThickness = "Thin"
Exit Function
ElseIf intCode = xlHairline Then
Get_LineThickness = "Hairline"
Exit Function
End If
End Function
The main function used in this program is a Button_Click event handler. The event handler executes when the user presses the Run button. When this happens the program checks each of the edges for borders:
If Range("B2").Borders(xlEdgeLeft).LineStyle _
<> xlNone Then
...
Else
...
End If
If Range("B2").Borders(xlEdgeTop).LineStyle _
<> xlNone Then
...
Else
...
End If
...
If a border is detected then the properties are extracted and printed in the associated cell. The line below gets the line style property for the border on the left edge of cell B2:
intLineStyle = Range("B2").Borders(xlEdgeLeft).LineStyle
Note that the value returned is an integer representing the border style. This value is passed to the function Get_LineStyle() and the corresponding string is returned:
'returns the line style
Private Function Get_LineStyle(ByVal intCode _
As Integer) As String
If intCode = XlLineStyle.xlContinuous Then
Get_LineStyle = "Continious"
Exit Function
ElseIf intCode = XlLineStyle.xlDash Then
Get_LineStyle = "Dash"
Exit Function
ElseIf intCode = XlLineStyle.xlDashDot Then
Get_LineStyle = "Dash Dot"
Exit Function
ElseIf intCode = XlLineStyle.xlDot Then
Get_LineStyle = "Dot"
Exit Function
ElseIf intCode = XlLineStyle.xlDouble Then
Get_LineStyle = "Double"
Exit Function
ElseIf intCode = XlLineStyle.xlSlantDashDot Then
Get_LineStyle = "Slant Dash"
Exit Function
ElseIf intCode = XlLineStyle.xlDashDotDot Then
Get_LineStyle = "Dash Dot Dot"
Exit Function
End If
End Function
Similarly the line below gets the weight/thickness of the border on the left edge of cell B2:
intThickness = Range("B2").Borders(xlEdgeTop).Weight
Again the value returned in an integer. The function below takes that integer as input and returns the associated string expression:
'returns the line weight/thickness
Private Function Get_LineThickness(ByVal intCode _
As Integer) As String
If intCode = xlThick Then
Get_LineThickness = "Thick"
Exit Function
ElseIf intCode = xlMedium Then
Get_LineThickness = "Medium"
Exit Function
ElseIf intCode = xlThin Then
Get_LineThickness = "Thin"
Exit Function
ElseIf intCode = xlHairline Then
Get_LineThickness = "Hairline"
Exit Function
End If
End Function
You can download the file and code related to this article here.
See also:
- Excel VBA, Create Border
- Excel VBA, Format Cells and Ranges Using the Macro Recorder
- Excel VBA, Border Style and Thickness
- Excel VBA Border Colors
- Excel VBA, Remove Border
- Excel VBA Borders
If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com