More Concise Way To Assign Borders?

[(PeteCresswell)]

As a total noob, working from MS Access VBA,
I find myself doing a lot of this:

-------------------------------------------
2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
..Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

2040 With .Borders(xlLeft)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlRight)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlTop)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2040 With .Borders(xlBottom)
2041 .Weight = xlMedium
2042 .LineStyle = xlSolid
2049 End With

2049 End With
2999 End With

 

[NickHK]

Pete, A couple of ways:

With Range("A1")
With .Borders
.Weight = xlMedium
End With
End With

Range("D1").BorderAround xlSolid, xlMedium

NickHK

 

[Guest]

You could put the border contstants in an array then loop through the array.
The for loop would go inside of your With statement. Example below. Or, you
might write a separate sub and pass arguments to it for LineStyle and Weight
and the border constants (passing an array for the borders since they could
vary or maybe use a parameter array).


Dim arrBorders as variant
Dim i as long

arrBorders = Array(xlLeft, xlRight, xlTop xlBottom)

2010 With theWS
' ----------------------------------------------
' Place Deal/Tranche name centered at top with
' borders and colored background

2020 With .Range(.Cells(mRowNum_Header1, mColNum_First),
..Cells(mRowNum_Header1, mColNum_Last))
2021 .Merge
2022 .Value = theDealName & "-" & theTrancheNumber
2023 .HorizontalAlignment = xlCenter
2029 .Interior.ColorIndex = gExcelColor_MediumBlue

for i = lbound(arrborders) to ubound(arrborders)
With .Borders(arrBorders(i))
.Weight = xlMedium
.LineStyle = xlSolid
End With
next i

2049 End With
2999 End With

 

[Peter T]

I tried .BordersAround, but Excel didn't buy it.

It wouldn't, it's a typo, see below

Sub test()

With ActiveSheet
With .Range(.Cells(3, 2), .Cells(3, 6))
With .Cells(1, 1)
.Value = "my text or value"
.HorizontalAlignment = xlCenter
'gExcelColor_MediumBlue, guessing -
'will match to nearest colour in the palette
.Interior.Color = RGB(153, 204, 255)
' or asumming a default palette
'.Interior.ColorIndex = 37
End With
.Merge
.BorderAround xlSolid, xlMedium
' or if using late binding
'.BorderAround 1, -4138
End With
End With
End Sub

Regards,
Peter T

 

[Peter T]

Just to add, probably better to use constants like xlEdgeLeft rather than
xlLeft.

If looping the outside borders could do it this way

With rng
For i = 7 To 10
.Borders(i).Weight = xlMedium
.Borders(i).LineStyle = xlSolid
Next
End With

To include inside borders, whether or not there are any, simply apply to
..Borders in one go.

..Borders.Weight = xlMedium
..Borders.LineStyle = xlSolid

Regards,
Peter T