Excel在对大量单元格(VBA)进行舍入时速度较慢

Question

我给出了一个任务，用带有双数字的119col*176Rows = 20944(可能更多(单元格对表进行四舍五入。这来自于一台具有大量测量和计算的测量机。我的第一个想法是制作一个简单的vba，在循环中用四舍五入的数字逐个更新该范围内的所有单元格，如下面的代码所示：。

但是。。。这就是SoooooooooSloww，即使是Excel应用程序也会崩溃。

任何变通方法都将非常感谢伙计们

Sub Round_numbers_click()
Dim rng As Range
Dim rCell As Range

Dim valOriginal As Double
Dim valRounded As Double

Range("E15").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Set rng = Application.Selection

For Each rCell In rng.Cells
valOriginal = col.Cells.Value
valRounded = Round(valOriginal, 3)
rCell.Value = valRounded
Next rCell
End Sub

Answer 1

您可以尝试使用Evaluate。

Sub RoundToThree()
Dim rng As Range
Dim res As Variant
Set rng = Range("E15")

Set rng = Range(rng, rng.End(xlDown))

Set rng = Range(rng, rng.End(xlToRight))

res = Evaluate("ROUND(" & rng.Address & ",3)")

rng.Value = res

End Sub

Answer 2

将范围保存到变量数组，处理数组并将数组保存回范围。

Sub Round_numbers_click()
Dim rng As Range
Dim rCell As Range
Dim vArr As Variant
Dim iRow As Long, iCol As Long

Dim valOriginal As Double
Dim valRounded As Double

Range("E15").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Set rng = Application.Selection
vArr = rng
For iRow = 1 To UBound(vArr, 1)
For iCol = 1 To UBound(vArr, 2)
vArr(iRow, iCol) = Round(Val(vArr(iRow, iCol)), 3)
Next
Next
rng = vArr
End Sub