Geek Answers Handbook

Setting Excel range formatting using an array

In the past, I used a variant array to populate a range of several Excel cells.

I am wondering if there is a way to do the same with cell formatting? I would prefer not to go cell by cell, and it would be nice to minimize the number of calls to get the Excel range ...

+5
source share
3 answers

No, you cannot do each cell separately, although you can assign one format to the entire range.

Range property for destination - .NumberFormat. If you create a variant of an array of strings for assignment as a format, then assign it to the range, only the first element is applied (and it applies to all cells in the range).

So best of all you can do a 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
+3
source

Mostly I do what Lance offers. However, there are some cases when I create a separate hidden worksheet with the formats that I want to customize. Then I

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

This will take care of it in one fell swoop. But you have the overhead of a hidden sheet.

+4
source

, , . , , .

, , , , , .

Here is a function that can do this. In tests (Excel 2003), this is done 8x-10x faster than always, setting the format, and when screen refresh is 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

Name it as follows:

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

More articles:


All Articles