vba 使用数组设置 Excel 范围格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3856065/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set Excel Range Formatting With Array
提问by Jon Fournier
I have, in the past, used a variant array to populate a range of multiple Excel cells.
过去，我曾使用变体数组来填充一系列多个 Excel 单元格。
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...
我想知道，有没有办法对单元格格式做同样的事情？我宁愿不逐个单元格地进行，并且尽量减少获取 Excel 范围的调用次数会很好...
采纳答案by Lance Roberts
@ExcelHero has pointed out to me how to get this done, so here's how.
@ExcelHero 已经向我指出了如何完成这项工作，所以这就是方法。
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:
如果您的范围是垂直的，则转置该数组，因为 Excel 认为数组是水平的：
[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).
要分配给的 Range 的属性是.NumberFormat。如果您创建一个字符串的变体数组以分配为格式，然后将其分配给范围，则仅应用第一个元素（并且它会应用于该范围的所有单元格）。
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
回答by Dick Kusleika
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.
一举解决它。但是您确实有隐藏工作表的开销。
回答by Bbb
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.
这是一个可以做到的功能。在测试 (Excel 2003) 中，这比始终设置格式的运行速度快 8 到 10 倍，即关闭屏幕更新。
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
