Set Excel Range Formatting With Array

I have, in t开发者_运维百科he past, used a variant array to populate a range of multiple Excel cells.

I'm wondering, is there a way to do the same thing with cell formatting? I'd rather not go cell by cell, and it'd be nice to minimize the number of calls to get an Excel range...

I mostly do what Lance suggests. However, there are some cases where I will make a separate, hidden worksheet with the formats I want set up. Then I'll

wshHidden.Range("A1:D100").Copy
wshReport.Range("A1:D100").PasteSpecial xlPasteFormats

That takes care of it in one fell swoop. But you do have the overhead of the hidden sheet.

@ExcelHero has pointed out to me how to get this done, so here's how.

If your range is horizontal, then just feed it an array built of Format strings:

[a1:c1].NumberFormat = Array("hh:mm", "General", "$#,##0.00")   

If your range is vertical, then transpose that array, since Excel considers Arrays to be horizontal:

[a1:a3].NumberFormat = WorksheetFunction.Transpose(Array("hh:mm", "General", "$#,##0.00"))

Old Answer:

No, you can't do each cell separately, though you can bulk assign one format to an entire range.

The property of a Range to assign to is .NumberFormat. If you create a variant array of strings to assign as a format, then assign it to the range, only the first element gets applied (and it gets applied to all cells of the range).

So the best you can do is loop:

Dim r As Range
Dim v(1 To 3) As Variant
Dim i As Integer

Set r = Range("A1:A3")
v(1) = "hh:mm:ss"
v(2) = "General"
v(3) = "$#,##0.00_);[Red]($#,##0.00)"

For i = 1 to 3
  r(i).NumberFormat = v(i)
Next i

Hopefully I can safely presume you are doing this for performance reasons. As answered above, its not really possible the same way you can do with cell contents.

However, if the formatting of cells is often the same as last time you formatted it, it is much faster to first check if the format needs to change, and only then change it.

Here is a function that can do it. In tests (Excel 2003), this runs 8x-10x faster than always setting the format, and that is with screen updating turned off.

Sub SetProperty(ByRef obj As Object, propname, newvalue)
    If CallByName(obj, propname, VbGet) <> newvalue Then
        Call CallByName(obj, propname, VbLet, newvalue)
    End If
End Sub

Call it like this:

Call SetProperty(Cells(1,1).Font, "ColorIndex", 27) 
Call SetProperty(Cells(1,1).Borders, "Weight", xlMedium)
etc