Set Excel Range Formatting With Array

Question

I have, in the 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...

Answer 1

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

Answer 2

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.