How to set a border around specific range in Excel workbook

There is a univeral method of getting VBA code, that you can modify and re-use for various purposes. This is a perfect to get a macro you can use with Spreadsheet Excel Compiler to create a standalone EXE from Excel file.

Record a macro in Microsoft Excel

  1. Run Microsoft Excel Application
  2. Create new Workbook
  3. Press Record new macro button in the left-bottom corner of the Window
  4. Do the action you need (set a border around range)
  5. Press a Record Macro button again to stop recording
  6. Open the VBA editor and find your macro
Record new macro in Microsoft Excel
The macro you've recorded is added into the last code module in your workbook. You can easily find it if you expand the project node and open the list of modules.

How to open Visual Basic Editor?

You are right. We've missed this question. You can either use hot key Alt+F11 or use Visual Basic command on the Developer tab on the ribbon.
Microsoft Excel | Developer tab | Visual Basic command
Excel gives you a macro (Visual Basic code) that does exactly what you've done using interface commands. You can use this macro to insert this code into your own procedure, or to create a procedure that you will use in your Excel workbook.

Set a single line border around the specific Range

Excel gives us following macro: 
Sub Macro2()
Range("N7:Q14").Select
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
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
End Sub
In this example we've selected range N7:Q14 on the active worksheet. If you are going to copy this code into your program, do not forget to change this address.

Does Excel Compiler support this code?

Spreadsheet Excel Compiler supports this Visual Basic code. You can copy it in to the empty Excel file, save it on disk and compile with xlCompiler, to see how it works.
xlCompiler is a perfect way to create ready to sell EXE file from the Excel workbook.
Go to top