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

--
Regards,

OssieMac

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

--
Rick (MVP - Excel)


"fisch4bill" <(E-Mail Removed)> wrote in message
news:16E03E08-E5D6-4823-87EB-(E-Mail Removed)...
> 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

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

"Rick Rothstein" wrote:

> 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
>
> --
> Rick (MVP - Excel)
>
>
> "fisch4bill" <(E-Mail Removed)> wrote in message
> news:16E03E08-E5D6-4823-87EB-(E-Mail Removed)...
> > 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
>
> .
>