如何使用VBA在Excel中乘以标量变量的值范围?

Question

我已经实现了这个方法，将每个数组元素乘以一个变量中保存的数字。它非常慢。

有公认的"最快"吗?如何将一个范围内的每个元素乘以一个常数?或者至少是一个不那么慢的?我必须做10次，这需要几分钟。

MultFactor = 10
For Each cell In Sheet1.Range("B3:B902")
cell.Value = cell.Value * MultFactor
Next cell

在整个范围乘以值引用的解决方案?乘以一个常数(不是变量)。如果我使用这段代码(将范围从"A1:B10"到"B3:B902")，我得到一个毫无意义的答案。

Dim rngData As Range
Set rngData = Sheet12.Range("B3:B902")
rngData = Evaluate(rngData.Address & "*2")

我在B3:B902中的原始值在前100个元素左右是零，然后增加一点，最后减少一点，并有另一个零运行，但在我的范围内结束的是一系列数字，打击了我范围内的所有内容。它从-224.5开始，一直减少0.5，直到最后一个单元格。

-224.5
-224.0
-223.5
etc.

即使工作，我如何修改它来使用变量multifactor ?

Answer 1

这将是数百到数千倍的速度。不同之处在于，所有的计算都是对VBA数组进行的，而不是直接对工作表单元格逐一进行的。一旦数组被更新，它就会一次写回工作表。这将工作表交互减少到只有两个实例，读取和写入数组。减少VBA代码触及工作表一侧的实例数量对执行速度至关重要。

Sub Mozdzen()
Const FACTOR = 10
Const SOURCE = "B3:B902"

Dim i&, v
v = Sheet1.Range(SOURCE)
For i = 1 To UBound(v)
v(i, 1) = v(i, 1) * FACTOR
Next
Sheet1.Range(SOURCE) = v

End Sub

---

基于上述思想，管理代码的更好方法是用专用函数封装数组乘法:

Sub Mozdzen()    
Const FACTOR = 10
Const SOURCE = "B3:B902"

With Sheet2.Range(SOURCE)
.Value2 = ArrayMultiply(.Value2, FACTOR)
End With        
End Sub
Function ArrayMultiply(a, multfactor#)
Dim i&
For i = 1 To UBound(a)
a(i, 1) = a(i, 1) * multfactor
Next
ArrayMultiply = a
End Function

Answer 2

您需要:

rngData = Sheet12.Evaluate(rngData.Address & "*2")

因为地址属性默认不包括工作表名称(所以你的公式是在活动工作表的范围B3:B902的上下文中计算的)

则需要:

rngData = Sheet12.Evaluate(rngData.Address & "*" & MultFactor)