Condensing Format Code

[baconcow]

I wanted to shorten the macro-made format code:

I used this hoping to shorten the amount of code needed to format the cells,
but it gives me a type mismatch. I am also hoping to use Arrays with text in
them for many other areas of my code. Is this possible?

Set format_range = Worksheets("Surface").Range("A5",
cell_range.Offset(cell_count1 - 1, 10))
format_borders = Array("xlEdgeLeft", "xlEdgeTop", "xlEdgeBottom",
"xlEdgeRight")

Dim c As Long

For c = 0 To 3
With format_range.Borders(format_borders(c))
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlMedium
End With
Next c


What am I doing wrong? Thanks.

 

[Joel]

the following consnts are number, you made them strings". Remove the double
quotes.

format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)

 

[baconcow]

Thanks, I didn't realized they were numbers. That makes the rest of what I
was doing work!

 

[Joel]

Here is a little know secret.

right click on the VBA window and slect Object Browser. In the top of the
browser there is a search box. Insert xlEdgeLeft and press binoculars.. You
will see all the border constants. Click on each constant and you will see
there value at the bottom of the window Pane.

 

[baconcow]

I've seen those numbers before but I did not know exactly what they
represented in terms of the code. So these are just constants that are built
into VBA? Interesting. Thanks again.