Add Boarders with VBA

Adding boards around a selection in VBA is a useful program if you want a shortcut to your menu.  I was asked to produce some code which allowed the boarder to show through when colour was added to a selection of cells.  If you make a selection and turn the macro recorder on and there will be 38 lines of code produced to get borders inside, outside, horizontal and vertical.  

The following YouTube video adds borders with VBA. It takes you through the process from start to finish.

 
 
 

That is a complete boarder around every cell.  When you add interior colour in Excel the borders tend to disappear so adding them back gives the look as follows;

Colour Cells VBA

Below is the recorded code which will produce the borders alone.

Sub Macro1() 'Recorded VBA Macro to colour cells.

Selection.Borders(xlDiagonalDown).LineStyle=xlNone
Selection.Borders(xlDiagonalUp).LineStyle=xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle=xlContinuous
.ColorIndex=0
.TintAndShade=0
.Weight=xlThin
End With
End Sub

This took me down a path and I ended up with the following procedure which will produce the same result.

Option Explicit

Sub AddBoarder() 'Excel VBA macro to improve recorded macro.
Dim i As Long
Dim Var As Variant

Var=Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)
For i=LBound(Var) To UBound(Var)
Selection.Borders(Var(i)).Weight=xlThin
Next i
End Sub

This efficiently adds a boarder to a selection and is a nice addition to any personal macro workbook. It uses a loop to achieve the task.


The good part about being involved in a community like Ozgrid is that occasionally you will see far better solutions. Wim Gielis (Wigi), a star in the Excel community wrote to me with a more efficient solution which has no loop at all.


Sub AddBorderWigi() 'Excel VBA improvement by Wim Gielis (respect).
Selection.Borders.Weight=xlThin
End Sub

Ouch!!!! It is surprising when looking at the recorded code for the procedure and then seeing what that code can be shortened to by a guy like Wigi. To see more of his fantastic solutions, check out Wigi's site here.


http://www.wimgielis.be/

Adding the colour using Wigi's improved code can be done as follows;


Option Explicit

Sub RedInterior() 'Turns the slection Red
Selection.Borders.Weight=xlThin
Selection.Interior.Color=vbRed
End Sub


Attached is an Excel file to show workings and the VBA procedure.