如何修改没有循环的单元格范围 [英] How To Modify A Range of Cells Without a Loop

问题描述

我有一个巨大的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

这篇关于如何修改没有循环的单元格范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！