vba 在VBA中同时设置左右单元格边框

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

提问by Zephyr Mays

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 msgBoxconfigurations can be combined/added together (e.g. vbYesNo + vbQuestion). I tried:

想知道是否有办法用一条语句同时设置单元格的左右边框？类似于可以将msgBox配置组合/添加在一起的方式（例如vbYesNo + vbQuestion）。我试过：

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?

有没有更优雅的方式？

---

---

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:

完全归功于@egan-wolf 和@robinmackenzie，这里是我用来回答上述问题的完整解决方案。正如建议的那样，我创建了一个辅助函数并将它传递给我想要设置边框的单元格以及我希望它们成为的线条样式和粗细，将 8 行代码变成更易读的单行：

setLeftAndRightEdges Cells(j, i), xlContinuous, xlMedium

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

采纳答案by Egan Wolf

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

回答by Wujaszkun

How about:

怎么样：

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

Using this you don't need an additional forloop.

使用它你不需要额外的for循环。

回答by Robin Mackenzie

Here's a slightly hacky way to do it as a one-liner where rngis a Rangeobject we have already defined:

这是一种稍微有点hacky的方法，可以将其作为单行方式来实现，其中rng是Range我们已经定义的对象：

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

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.

诀窍是您希望获得一个范围，其中包括目标范围的左侧和右侧单元格，然后为该组单元格设置内部垂直边框。这具有设置原始单元格的左右边框的效果。

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

• Offset(0, -1)转到目标单元格的左侧单元格
• Resize(1, 3)将范围扩展到目标单元格所在行的 3 列
• Borders(xlInsideVertical).LineStyle...设置此 3 个单元格范围的内部垂直线的格式。

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.

编辑：正如@EganWolf 所指出的，这不适用于 A 列中的单元格。或者，就此而言，它不适用于工作表的最右侧列。这些“边缘”情况需要额外的编码。

回答by CLR

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