问题描述
我有一个巨大的Excel模板,它存在性能问题.我想优化用于修改单元格的VBA代码,使其更像是一次全部"的方法.我有几个使用循环修改值的基本功能.这是一个示例:
I have a massive Excel template that's having performance issues. I'd like to optimize the VBA code I'm using to modify cells to be more of an "all at once" approach. I have several basic functions using loops to modify values. Here's an example:
Dim aCell as Range
For Each aCell In Range("A1:A9999").Cells
'appends prefix to value of each cell
aCell.Value = "CC_" & aCell.Value
Next aCell
尽管这可行,但它的缺点是它会导致多次重新计算和更新,从而使模板变慢.我熟悉打开和关闭计算/屏幕更新的功能,但是出于我不愿讨论的原因,这不是一种选择.
While this works, the drawback of this is that it causes several recalculations and updates that slows down the template. I'm familiar with turning calculations/screen updating on and off, but for reasons I won't go into, that's not an option.
下面的代码不有效,但这是我正在寻找的方法.有没有办法使用数组或我不认为的其他工具进行此类更改,以最大程度地减少模板计算更新?
This code below does NOT work, but it's the approach I'm looking for. Is there a way to make such a change using an array or some other tool I'm not thinking of that would minimize the templates calculation updates?
Range("A1:A9999").Value = "CC_" & Range("A1:A9999").Value
谢谢!
推荐答案
读取/写入工作表需要很多时间.在VBA阵列中进行修改,然后将其写回.
Reading/writing to/from the worksheet takes a lot of time. Do the modifications within a VBA array, then write it back.
Dim myRange As Range, myArr As Variant
Set myRange = Range("A1:A9999")
myArr = myRange
For i = 1 To UBound(myArr, 1)
myArr(i, 1) = "CC_" & myArr(i, 1)
Next i
myRange = myArr
这篇关于如何修改没有循环的单元格范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!