Condensing Format Code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

baconcow - 29 May 2008 15:58 GMT

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.

Reply to this Message

Joel - 29 May 2008 16:18 GMT

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

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

> I wanted to shorten the macro-made format code:
>
[quoted text clipped - 19 lines]
>
> What am I doing wrong? Thanks.

Reply to this Message

baconcow - 29 May 2008 16:26 GMT

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

> the following consnts are number, you made them strings". Remove the double
> quotes.
[quoted text clipped - 24 lines]
> >
> > What am I doing wrong? Thanks.

Reply to this Message

Joel - 29 May 2008 16:35 GMT

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.

> Thanks, I didn't realized they were numbers. That makes the rest of what I
> was doing work!
[quoted text clipped - 27 lines]
> > >
> > > What am I doing wrong? Thanks.

Reply to this Message

baconcow - 29 May 2008 16:39 GMT

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.

> Here is a little know secret.
>
[quoted text clipped - 34 lines]
> > > >
> > > > What am I doing wrong? Thanks.

Reply to this Message