Declaring an array variable

  • Thread starter Thread starter fisch4bill
  • Start date Start date
F

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
 
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
 
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
 
Back
Top