我需要在多个工作表中的某些范围中添加外部边框,所以我编写了以下测试代码(有效):
Sub TestFun()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("TestBook.xlsm")
wb.Worksheets("Sheet1").Select
Range("B2:D10").Select
AddOutsideBorders
wb.Worksheets("Sheet2").Select
Range("B2:D10").Select
AddOutsideBorders
wb.Worksheets("Sheet3").Select
Range("B2:D10").Select
AddOutsideBorders
End Sub
Sub AddOutsideBorders()
With Selection
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
End Sub
但是,我的真实工作簿包含更多工作表,我需要执行更复杂的任务。所以问题是,如何在不先选择范围的情况下添加边框?例如,我想要这样整洁的东西(它不起作用):
Sub TestFun()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = Workbooks("TestBook.xlsm")
wb.Worksheets("Sheet1").Range("B2:D10").AddOutsideBorders
wb.Worksheets("Sheet2").Range("B2:D10").AddOutsideBorders
wb.Worksheets("Sheet3").Range("B2:D10").AddOutsideBorders
End Sub
Sub AddOutsideBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
End Sub
更一般地说,如何调用在 sub 中的范围内定义的另一个函数?
您尝试的方法几乎是有效的(并且对于您尝试避免选择有好处),您只是以不正确的方式调用了该过程。 相反,它应该被称为:
Call AddOutsideBorders(wb.Worksheets("Sheet1").Range("B2:D10"))
Call AddOutsideBorders(wb.Worksheets("Sheet2").Range("B2:D10"))
Call AddOutsideBorders(wb.Worksheets("Sheet3").Range("B2:D10"))
将 AddOutsideBorders 转换为如下所示的函数:
Sub TestFunc()
Dim wb As Workbook
Set wb = Workbooks("TestBook.xlsm")
AddOutsideBorders wb.Worksheets("Sheet1").Range("B2:D10")
AddOutsideBorders wb.Worksheets("Sheet2").Range("B2:D10")
AddOutsideBorders wb.Worksheets("Sheet3").Range("B2:D10")
End Sub
Public Function AddOutsideBorders(rng As Range)
With rng
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
End With
End Function
事实证明,有一个范围方法可以执行 AddOutsideBorders 子所做的事情(并且无需指定 4 个外部边框中的每一个) - .边框方法。 在这种情况下,您将使用以下命令实现:
wb.Worksheets("Sheet1").Range("B2:D10").BorderAround LineStyle:=xlContinuous
您也可以同时设置其他参数。 这是Microsoft文档的链接。