Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing Format Code
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing Format Code
the following consnts are number, you made them strings". Remove the double
quotes. format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight) "baconcow" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing Format Code
Thanks, I didn't realized they were numbers. That makes the rest of what I
was doing work! "Joel" wrote: the following consnts are number, you made them strings". Remove the double quotes. format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight) "baconcow" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing Format Code
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" wrote: Thanks, I didn't realized they were numbers. That makes the rest of what I was doing work! "Joel" wrote: the following consnts are number, you made them strings". Remove the double quotes. format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight) "baconcow" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Condensing Format Code
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. "Joel" wrote: 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" wrote: Thanks, I didn't realized they were numbers. That makes the rest of what I was doing work! "Joel" wrote: the following consnts are number, you made them strings". Remove the double quotes. format_borders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight) "baconcow" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Condensing VBA code | Excel Discussion (Misc queries) | |||
Condensing Code | Excel Programming | |||
Need help condensing with-end with code | Excel Programming | |||
Trouble Condensing Code | Excel Programming | |||
1.)Input Boxes 2.) Condensing a Code | Excel Programming |