In Excel VBA, border weights are controlled by the XlBorderWeight enumeration, which offers four values: xlHairline (1), xlThin (2), xlMedium (-4138), and xlThick (4). To set a border’s weight, assign one of these constants to the Weight property of a Border object, for example: Range("A1").Borders(xlEdgeBottom).Weight = xlThick.
What Are Border Weights in Excel VBA?
In Excel’s object model, every cell range has a Borders collection containing six Border objects: left, right, top, bottom, and the two diagonals (from top-left to bottom-right, and from bottom-left to top-right). Each Border object has three key properties: LineStyle (e.g., continuous, dashed), Color or ColorIndex, and Weight. The Weight property determines the thickness of the line, and it accepts one of the four XlBorderWeight constants listed above.
The XlBorderWeight Enumeration Values
The XlBorderWeight enumeration is defined in the Excel VBA library. Its members are:
xlHairline(value 1) – The thinnest border, often used for gridlines in print layouts.xlThin(value 2) – A standard thin line, slightly thicker than hairline.xlMedium(value -4138) – A medium-thickness line, commonly used for emphasis.xlThick(value 4) – The thickest border, ideal for outer edges or total rows.
These constants are available in any VBA module without needing to declare them, as they are part of the Excel type library. You can also use the numeric values directly, but using named constants improves readability and reduces errors.
How to Set Border Weights in VBA
To apply a border weight, you first reference the border you want to modify. The most common approach is to use the Borders collection with an index like xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideHorizontal, or xlInsideVertical. For example, to set a thick bottom border on cells A1:C1:
Sub SetThickBottomBorder()
Dim rng As Range
Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:C1")
With rng.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.Color = RGB(0, 0, 0) ' black
End With
End Sub
Remember that changing a border’s weight without setting LineStyle may have no visible effect if the line style is xlLineStyleNone. Always set LineStyle to a visible style like xlContinuous or xlDash before adjusting weight.
Choosing the Right Border Weight
The choice of weight depends on the visual hierarchy you want to create. Here are practical guidelines:
- Hairline – Use for subtle separators that should not distract, such as gridlines in a dense data table.
- Thin – Good for standard table borders; it is the default weight when you apply borders via the Excel ribbon.
- Medium – Use to highlight a specific row or column, like a header row that needs to stand out without being overpowering.
- Thick – Reserve for the outer boundary of a report, a total row, or a box around a key result.
In practice, you might combine weights: thin inside borders for data cells, medium for header underlines, and thick for the outer frame. This creates a clean, professional look.
Common Problems and Misconceptions
One frequent issue is that setting Weight alone doesn’t change the border if the LineStyle is xlLineStyleNone. Another misconception is that xlMedium is the default; actually, when you apply a border through the ribbon, Excel uses xlThin by default. Also, note that the Weight property only affects continuous lines; for dashed or dotted lines, the weight may appear similar to thin regardless of the setting. If you need a thicker dashed line, you must use a continuous line style.
Another common error is trying to set Weight on a Borders collection as a whole. The Borders collection does not have a Weight property; you must access individual Border objects. For example, Range("A1").Borders.Weight = xlThick will fail with a compile error. Instead, loop through all borders or specify each one.
Comparing Border Weights with Other Formatting
Border weight is closely related to LineStyle and Color. While weight controls thickness, LineStyle controls the pattern (continuous, dash, dot, etc.), and Color controls the hue. When printing, hairline borders often appear as thin as possible, but on screen they may be barely visible. Medium and thick borders are more visible on both screen and print. If you export to PDF, the weights are preserved, but note that hairline may become invisible on low-resolution screens.
Performance and Best Practices
Applying borders to many cells individually can slow down your macro. To improve performance, consider using the Range.BorderAround method for a single border around a range, or set borders for an entire range at once using a With block. For example, to apply a thin border to all cells in a used range:
Sub ApplyThinBorders()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim usedRng As Range
Set usedRng = ws.UsedRange
With usedRng.Borders
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End Sub
This sets all six borders for every cell in the range at once, which is faster than looping. Always save a copy of your workbook before running a macro that modifies formatting, because border changes cannot be undone with Ctrl+Z if the macro is run from the VBA editor.
Frequently Asked Questions
How do I set a border weight without changing the line style?
You cannot change the weight of a border that has no line style. If you only set Weight, the border will remain invisible if LineStyle is xlNone. Set LineStyle to xlContinuous (or another visible style) first, then adjust the weight.
What is the default border weight in Excel?
When you apply a border from the ribbon (Home > Font > Borders), Excel uses xlThin as the default weight. However, the default Weight property for a Border object is xlThin as well, unless you have previously changed it.
Can I use border weights in conditional formatting?
No, conditional formatting rules do not expose the Weight property. You can only set border style and color in conditional formatting, not thickness. To apply different weights based on conditions, you would need to use a VBA macro that checks the condition and applies the border.
Why does my thick border look thin when printed?
Printers may render hairline and thin borders very similarly, especially at low DPI settings. Thick borders should print clearly, but if you are using a non-continuous line style, the weight may be ignored. Ensure you use a continuous line style for the thickest appearance.
How do I remove all borders from a range in VBA?
To remove all borders, set the LineStyle of each border to xlLineStyleNone. For example: Range("A1:C3").Borders.LineStyle = xlLineStyleNone. This also resets the weight effectively, since the border is invisible.
Related
- Excel Spreadsheet Timesheet Template: How to Make One
- Excel Spreadsheet to Track Job Applications? Yes, Here’s How
- Excel Spreadsheet for Inventory Tracking: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel Macro Subscript Out of Range: Causes and FixesExcel’s “Subscript out of range” message is VBA runtime error 9: a macro has asked for an item…
- VBA MsgBox New Line: Add Line Breaks to MessagesTo add a new line in a VBA MsgBox, join the message text with vbCrLf or vbNewLine where…
- VBA Delete Row If Cell Is Blank: Safe MethodsTo delete rows when a cell in a chosen column is blank, loop through the data from the…
- Excel VBA: Find Unique Values in a ColumnTo find unique values in an Excel column with VBA, loop through the source cells, keep the first…
- VBA Array Clear: How to Erase, Reset, and Reuse ArraysTo clear a VBA array, use Erase arrayName. For a dynamic array, Erase releases its allocated storage; for…
- Excel VBA Input Box With a Drop-Down ListExcel’s built-in VBA InputBox cannot display a drop-down list. To let someone choose from fixed options, create a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.