VBA中函数返回大型数组的效率探究及疑问

问题背景 #

正在开发Excel VBA信号处理项目，需处理千万级Double类型数据点的大型数组。为提升代码可读性，偏好使用链式调用（如dResult = Scale(Normalize(Smooth(Integrate(MyBigData))), 2)），但传统函数实现（ScaleData1）理论上存在两次不必要的数组复制。为此设计了三种优化方案，但基准测试结果与预期不符，需解析原因。

三种实现方案 #

1. ScaleData1：临时数组返回（理论两次复制） #

通过创建临时数组存储计算结果，最后返回该数组，理论上存在临时数组赋值和函数返回赋值两次数组复制：

Function ScaleData1(dSource() As Double, dFactor As Double) As Double()
    Dim dTmp() As Double, i As Long
    ReDim dTmp(UBound(dSource))
    For i = 0 To UBound(dSource)
        dTmp(i) = dSource(i) * dFactor
    Next
    ScaleData1 = dTmp
End Function

2. ScaleData2及ScaleData2_ip：减少复制+支持链式调用 #

通过函数返回源数组的引用，再调用原地修改子过程，理论上仅在修改时触发一次复制：

Function ScaleData2(dSource() As Double, dFactor As Double) As Double()
    ScaleData2 = dSource
    ScaleData2_ip ScaleData2, dFactor
End Function

Sub ScaleData2_ip(dSource() As Double, dFactor As Double)
    Dim i As Long
    For i = 0 To UBound(dSource)
        dSource(i) = dSource(i) * dFactor
    Next
End Sub

3. ScaleData3：无复制但不支持链式调用 #

直接将计算结果写入输出参数数组，理论上完全避免额外数组复制：

Sub ScaleData3(dSource() As Double, dFactor As Double, dScaled() As Double)
    Dim i As Long
    ReDim dScaled(UBound(dSource))
    For i = 0 To UBound(dSource)
        dScaled(i) = dSource(i) * dFactor
    Next
End Sub

基准测试结果 #

测试千万级数据点数组，耗时结果（单位：ms）：

• ScaleData1: 6210.938
• ScaleData2: 6744.141
• ScaleData3: 6306.641

预期为ScaleData1最慢、ScaleData3最快，但实际结果与预期相悖。

异常结果原因解析 #

1. VBA数组的写时复制（COW）优化 #

VBA中数组赋值（如ScaleData2 = dSource）默认是指针引用（浅拷贝），而非立即复制整个数组。只有当代码修改引用数组的元素时，才会触发写时复制——此时才会完整复制数组内存块。因此ScaleData2的实际复制次数仅为1次，和ScaleData1的理论两次复制存在差异：

• ScaleData1中，ReDim dTmp后循环赋值是主动复制，而函数返回ScaleData1 = dTmp时，VBA可能对临时数组做了所有权转移优化，避免了第二次复制（临时数组即将销毁，直接将其内存指针交给返回值），实际仅1次复制。
• ScaleData2中，ScaleData2 = dSource是引用，直到ScaleData2_ip修改元素时才触发复制，实际也是1次复制，但复制时机晚于ScaleData1，可能伴随内存缓存命中率下降，导致耗时略高。

2. 数组操作开销被计算开销掩盖 #

千万级Double数组的元素计算（每个元素乘因子）是CPU密集型操作，耗时占比远高于数组复制。80MB左右的数组复制（千万级Double，每个8字节）在现代CPU上仅需数十毫秒，相对于循环计算的数秒耗时，复制开销占比极低，导致三种方案的耗时差异被稀释，甚至出现反直觉结果。

3. ScaleData3的额外开销 #

ScaleData3虽然无额外复制，但ReDim dScaled会触发新内存块的分配，而VBA的内存分配器在处理大数组时，可能出现内存碎片化或分配延迟。同时，循环写入新分配的数组时，缓存局部性可能不如ScaleData1中连续写入临时数组的情况，导致耗时略高于ScaleData1。

大数据场景内存管理建议 #

• 如果允许修改源数组，优先使用原地修改的子过程（如ScaleData2_ip），避免任何数组复制，同时减少中间数组的内存占用。
• 链式调用会产生多个中间数组，需注意VBA的垃圾回收机制：大数组的频繁创建/销毁可能导致内存碎片化，可手动将中间数组设为Nothing触发回收。
• 若必须使用链式调用，ScaleData1的实现反而在实际测试中更高效，可保留该方案，无需刻意优化成ScaleData2。

内容的提问来源于stack exchange，提问作者j74nilsson