CODEWITHSUNDEEP
vbaexcelborder
data-monkey
data-monkey

Reputation: 1705

Excel VBA: Repeatedly Add Outside Borders

I need to add outside borders to some ranges in multiple worksheets, so I have written the following test code (which works):

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

However, my real workbook contains many more sheets and I need to perform more complicated tasks. So the question is, how do I add borders without selecting a range first? For example I want something as neat as this (it doesn't work):

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

More generally, how do i call another function defined on a range within a sub?

Upvotes: 0

Views: 6643

Answers (3)

EVz
EVz

Reputation: 11

It also turns out that there's a range method to do what your AddOutsideBorders sub does (and eliminates having to specify each of the 4 outside borders) - the .BorderAround method. In this case, you would implement with:

wb.Worksheets("Sheet1").Range("B2:D10").BorderAround LineStyle:=xlContinuous

You can simultaneously set other parameters, as well. Here's the link at Microsoft docs.

Upvotes: 1

Carmelid
Carmelid

Reputation: 228

Turn AddOutsideBorders into a function like this:

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

Upvotes: 1

RGA
RGA

Reputation: 2607

The method you attempted is almost valid (and good on you for trying to avoid select), you just called the procedure in an incorrect way. Instead, it should be called as:

Call AddOutsideBorders(wb.Worksheets("Sheet1").Range("B2:D10"))
Call AddOutsideBorders(wb.Worksheets("Sheet2").Range("B2:D10"))
Call AddOutsideBorders(wb.Worksheets("Sheet3").Range("B2:D10"))

Upvotes: 1

Related Questions