VBA - 将范围的格式复制到数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17397071/
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
VBA - Copy Formatting of Range to Array
提问by Abe Gold
I'm creating a report with multiple columns. What I need is that the columns that show only whole numbers, no decimals, should be rounded to the whole number (so that it not only shows a rounded number, it actually equals a round number). The columns that show the numbers with two numbers after the decimal should not be rounded.
我正在创建一个包含多列的报告。我需要的是只显示整数而不显示小数的列应该四舍五入到整数（这样它不仅显示一个四舍五入的数字，它实际上等于一个整数）。显示小数点后有两个数字的列不应四舍五入。
What I can do is:
我能做的是：
If c.NumberFormat = "#,##0_);(#,##0)" Then
c.Formula = "=round(" & Right(strFormula, Len(strFormula) - 1) & ",0)"
End If
However, I have the entire report in an array, and I would like to just paste the whole array into the sheet rather than pasting one cell at a time. I would also rather not process and round each cell based on the cell formatting, rather I would like to copy the formatting of the range where the report will go into an array, and work from the array. I believe this will cut a few seconds off the process.
但是，我将整个报告放在一个数组中，我只想将整个数组粘贴到工作表中，而不是一次粘贴一个单元格。我也不想根据单元格格式处理和舍入每个单元格，而是想复制报告将进入数组的范围的格式，并从数组中工作。我相信这会减少几秒钟的过程。
Is there a way to copy the formatting of a range into an array?
有没有办法将范围的格式复制到数组中？
回答by chuff
Is there a way to copy the formatting of a range into an array?
有没有办法将范围的格式复制到数组中？
Focusing on the question as posed, yes, that can be done. Unfortunately, it can't done in a one-liner, in the way that a range's values can be assigned to an array with myArray = Range("A2:F25"), for example. (See, also, brettdj's comment.) Instead, you'd need something like:
专注于提出的问题，是的，可以做到。不幸的是，它不能在单行中完成myArray = Range("A2:F25")，例如可以将范围的值分配给数组。（另请参阅 brettdj 的评论。）相反，您需要以下内容：
Dim rng as Range
Dim formatArray() As String
Dim i as Long, j as Long
Set rng = Range(A2:F20) 'or whatever the range is
Redim formatArray(1 to rng.Rows.Count, 1 to rng.Columns.Count)
For i = 1 to rng.Rows.Count
For j = 1 to rng.Columns.Count
formatArray(i, j) = rng.Cells(i, j).NumberFormat
Next
Next
...
A couple of observations, though:
不过，有几点意见：
You actually only need to know the formatting for a single row of the range since, presumably, the number formatting in a column will not change mid-column.
您实际上只需要知道范围内单行的格式，因为大概列中的数字格式不会更改列中。
That would simplify the code to:
这会将代码简化为：
...
Redim formatArray(1 to rng.Columns.Count)
For i = 1 to rng.Columns.Count
formatArray(i) = rng.Cells(1, i).NumberFormat
Next
...
assuming, for sake of example, that row 1 of the range has the necessary number formats.
例如，假设范围的第 1 行具有必要的数字格式。
I am curious why you would want to modify a formula on the worksheet so that it will round, since you could presumably do the calculation in your code, with rounding, and then write the resulting value back to the sheet for your report.
我很好奇您为什么要修改工作表上的公式以使其四舍五入，因为您大概可以在代码中进行计算，并进行四舍五入，然后将结果值写回报告表中。
If you need to apply number formats to the values you are writing to the worksheet (not just modify formulas so that they will produce whole numbers), that can be done to whole columns within the range at once, i.e.,
如果您需要将数字格式应用于要写入工作表的值（不仅仅是修改公式以生成整数），则可以一次对范围内的整列执行此操作，即，
...
For i = 1 to rng.Columns.Count
rng.Columns(i).NumberFormat = formatArray(i)
Next
...
If you need to convert the results of a cell's formula to a rounded value, you can do that with something like
如果您需要将单元格公式的结果转换为四舍五入的值，您可以使用类似
rng.Cells(2, 5).Value = WorksheetFunction.Round(rng.Cells(2, 5).Value, 0)
to give an example for a single cell. This assumes, of course, that the data feeding into the formula are already in the sheet and that the formula has been recalculated.
以单个单元格为例。当然，这假设输入公式的数据已经在工作表中并且公式已经重新计算。
