Excel VBA, Get Border Properties

by azurous in Border Style

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:

Excel VBA, Borders In Column A

The results after pressing the run button can be seen below:

Excel VBA, Borders In Column A, Result
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:

Excel VBA, Get Border Properties 1
In the figure below diagonals have been added to the borders of cell B2:

Excel VBA, Get Border Properties 2
In the figure below some of the borders have been colored:

Excel VBA, Get Border Properties 3
In the figure below the line style and thickness of some of the borders have been changed:

Excel VBA, Get Border Properties 4
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:

 

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

Tagged with: , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *