Declaring an array variable

[fisch4bill]

I'm working on a project that involves putting borders around several ranges
of cells. I've been able to condense the code to the following, but, I think
I've seen a one-liner method of declaring and assigning values to an array
variable.

Option Explicit
Sub BorderApplication()
Dim X(5) As XlBordersIndex
Dim N As Integer
X(0) = (xlEdgeLeft)
X(1) = (xlEdgeRight)
X(2) = (xlEdgeTop)
X(3) = (xlEdgeBottom)
X(4) = (xlInsideVertical)
X(5) = (xlInsideHorizontal)
For N = 0 To 5
With Selection.Borders(X(N))
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
Next N
End Sub

Is there a way to assign all these values to X(N) in a single line or am I
confusing this with another language? And on another tack, is there a
different way to loop through the values? Even doing it this way will
streamline my code, but, I'm looking for even more simplicity if it's
available.

Thanks in advance,
Bill

 

[OssieMac]

Hi Bill,

Not sure the following is what you are looking for. Because you said you
want the borders on several ranges I have included code demonstrating the use
of Union for formatting multiple ranges.

Note that the space and underscore at the end of a line is a line break in
an otherwise single line of code.

Sub BorderApplication()
Dim X As Variant
Dim N As Integer

X = Array(xlEdgeLeft, _
xlEdgeRight, _
xlEdgeTop, _
xlEdgeBottom, _
xlInsideVertical, _
xlInsideHorizontal)

Dim rngBorders As Range

Set rngBorders = Union(Range("A1:C10"), _
Range("E1:F10"), _
Range("H1:J10"))

For N = 0 To 5
With rngBorders.Borders(X(N))
.LineStyle = xlContinuous
.ColorIndex = 0
.Weight = xlThin
End With
Next N
End Sub

 

[Rick Rothstein]

Here is another way to structure your code...

Sub AddBorders()
Dim V As Variant
For Each V In Array(xlEdgeLeft, xlEdgeRight, xlEdgeTop, xlEdgeBottom, _
xlInsideVertical, xlInsideHorizontal)
With Selection.Borders(V)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = 0
End With
Next
End Sub

 

[fisch4bill]

Thanks to both OssieMac and Rick. This is exactly what I was looking for.
Bill