10613

Setting Left and Right cell borders at the same time in VBA

Question:

Wondering if there's a way to set both the left and right borders of a cell with a single statement? Something akin to the way msgBox configurations can be combined/added together (e.g. vbYesNo + vbQuestion). I tried:

Cells(j, i).Borders(xlEdgeLeft + xlEdgeRight)

Which leads to an error for me. It's a bit duplicative to code each border individually...

Here what I've come up with:

For i = 1 To 10 For j = 2 To 6 + numAcft Cells(j, i) = "Week Start Date" With Cells(j, i).Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlMedium End With With Cells(j, i).Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlMedium End With ... ...

Is there a more elegant way?

<hr />

With full credit to @egan-wolf and @robinmackenzie here is the full solution I used to answer the above question. As suggested I created a helper function and passed it the cell I want to set the borders for and the line style & weight I'd like them to be, turning 8 lines of code into a much more readable single line:

setLeftAndRightEdges Cells(j, i), xlContinuous, xlMedium

Private Sub setLeftAndRightEdges(ByVal cell As Range, ByVal lineStyle As Long, ByVal weight As Long) Dim edges(1) As Variant Dim edge As Variant edges(0) = xlEdgeLeft edges(1) = xlEdgeRight For Each edge In edges cell.Borders(edge).LineStyle = lineStyle cell.Borders(edge).weight = weight Next edge End Sub

Answer1:

Not sure if I would call it more elegant way, but this is the option to not duplicate code

Dim edges(1) As Variant edges(0) = xlEdgeLeft edges(1) = xlEdgeRight For Each edge In edges ActiveCell.Borders(edge).LineStyle = xlContinuous Next edge

Answer2:

How about:

With Cells(j, i) .Borders(xlEdgeLeft).LineStyle = xlContinuous .Borders(xlEdgeRight).LineStyle = xlContinuous End With

Using this you don't need an additional for loop.

Answer3:

There are many ways to skin a cat, as they say.. how about:

Dim edge As Variant For edge = xlEdgeLeft To xlEdgeRight Step xlEdgeRight - xlEdgeLeft Cells(j, i).Borders(edge).LineStyle = xlContinuous Next

Answer4:

Here's a slightly hacky way to do it as a one-liner where rng is a Range object we have already defined:

rng.Offset(0, -1).Resize(1, 3).Borders(xlInsideVertical).LineStyle = xlContinuous

The trick is that you want to get a range that includes the left-hand and right-hand cells to the target range and then for that group of cells, set the inside vertical border. This has the effect of setting the left-hand and right-hand border of the original cell.

<ul><li>Offset(0, -1) goes to the left-hand cell of the target cell</li> <li>Resize(1, 3) extends the range to 3 columns on the row of the target cell</li> <li>Borders(xlInsideVertical).LineStyle... sets the format of the inner verticals of this range of 3 cells.</li> </ul>

Sample code:

Option Explicit Sub Test() Dim ws As Worksheet Dim rng As Range 'sheet Set ws = ThisWorkbook.Worksheets("Sheet1") 'target range Set rng = ws.Range("B8") 'one-liner to set left and right borders rng.Offset(0, -1).Resize(1, 3).Borders(xlInsideVertical).LineStyle = xlContinuous End Sub

Edit: as pointed out by @EganWolf this won't work for cells in column A. Or, for that matter, it won't work in the right-handmost column of the sheet. Additional coding will be required for these 'edge' cases.

Recommend

  • Type “ViewController” does not conform to protocol “UITableViewDataSource”
  • Is there any benefit to defining a utility function directly on the jQuery object?
  • CodeIgniter - Autoload
  • Cakephp Form Helper
  • Are there algorithms for putting a digest into the file being digested?
  • Play Framework nested form errors missing
  • Criterion causing memory consumption to explode, no CAFs in sight
  • How to assign byte[] as a pointer in C#
  • Not able to aggregate on nested fields in elasticsearch
  • Calling Worksheet functions from vba in foreign language versions of Excel
  • Do I need to reset a Perl hash index?
  • How to define and use opencv mat of user type
  • Word Open XML Mail Merge
  • How do I alternate colors in Flat List (React Native)
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Bad request using file_get_contents for PUT request in PHP
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • Python CGI os.system causing malformed header
  • Spring Data JPA custom method causing PropertyReferenceException
  • FB SDK and cURL: Unknown SSL protocol error in connection to graph.facebook.com:443
  • Using $this when not in object context
  • How do I fake an specific browser client when using Java's Net library?
  • How reduce the height of an mschart by breaking up the y-axis
  • Incrementing object id automatically JS constructor (static method and variable)
  • javascript inside java/jsp code
  • Perl system calls when running as another user using sudo
  • jquery mobile loadPage not working
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Circular dependency while pushing http interceptor
  • Unanticipated behavior
  • Linker errors when using intrinsic function via function pointer
  • log4net write single file for each call to log.info
  • Free memory of cv::Mat loaded using FileStorage API
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • unknown Exception android
  • Binding checkboxes to object values in AngularJs
  • Net Present Value in Excel for Grouped Recurring CF
  • jQuery Masonry / Isotope and fluid images: Momentary overlap on window resize
  • How to load view controller without button in storyboard?