在VBA中同时设置左右单元格边界 [英] Setting Left and Right cell borders at the same time in VBA

问题描述

想知道是否有一种方法可以通过单个语句设置单元格的左右边界吗?与msgBox配置可以组合/添加在一起的方式类似(例如vbYesNo + vbQuestion).我试过了:

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...

这是我想出的:

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
...
...

还有更优雅的方式吗?

这要归功于@ egan-wolf和@robinmackenzie，这是我用来回答上述问题的完整解决方案.按照建议，我创建了一个辅助函数，并将要为其设置边框的单元格以及线条样式&传递给了它.我想让它们成为最重要的东西，将8行代码变成更易读的单行代码:

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

推荐答案

不确定我是否称其为更优雅的方式，但这是不重复代码的选项

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

这篇关于在VBA中同时设置左右单元格边界的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！