VBA关于数组Dim,ReDim Preserve运行速度对比思考

夸不得

VBA关于数组Dim,ReDim Preserve运行速度对比思考

以前看教程经常有人说ReDim Preserve动态定义数组运行速度非常慢,特做测试分析是否真的想网友说的ReDim Preserve真的不堪重用
经测试得出一下结论:
1 、ReDim Preserve的速度快慢对redim的次数比较敏感。
循环100万次，10万次，1万次，对单元格赋值，dim数组，用字典的count次数定义数组维度，及ReDim Preserve动态定义数组，运行时间如下：

大家会发现动态数组运行起来每增加10倍次数，时间却增加100倍，
但是如果是for i = 1000000 to 1 step -1时，100W次也就2秒左右，
综上所述，直接定义一个较大维度的数组写代码比较简单，速度也能接受，但是10W次一次的循环直接ReDim Preserve也可以接受
先赋值给字典，然后用字典的count计数再定义字典，写起来稍微麻烦，但速度快。
测试代码如下：

Sub 测试用时()
Dim n&, i&, sr$, A$, B$, C$, Mypath$, brr()
Application.ScreenUpdating = False
Rem 单元格操作用时
iRow = 10000
Range("A1:D" & iRow).ClearContents
t = Timer
Mypath = "C:\Users\DDN\Desktop\test\*.*"
For i = 1 To iRow
    Cells(i, 1) = i
    Cells(i, 2) = i * 2
    Cells(i, 3) = i * 4
    Cells(i, 4) = i * 6
Next
[G1].Value = Format(Timer - t, "0.00")
i = 0


Rem 数组操作用时定义一个很大维度
Range("A1:D" & iRow).ClearContents
t = Timer
Dim arr(1 To 10000, 1 To 4)
For i = 1 To iRow
    arr(i, 1) = i '序号
    arr(i, 2) = i * 2
    arr(i, 3) = i * 4
    arr(i, 4) = i * 6
Next
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
[G2].Value = Format(Timer - t, "0.00")
i = 0


Rem 用字典count重定义数组维度
Range("A1:D" & iRow).ClearContents
t = Timer
Set Mydic = CreateObject("Scripting.Dictionary") '创建一个字典放结果
For i = 1 To iRow
        A = i * 2
        B = i * 4
        C = i * 6
        Mydic(n) = A & "|" & B & "|" & C
Next
i = 0
ReDim myarr(1 To Mydic.Count, 1 To 4)
For i = 1 To Mydic.Count
    myarr(i, 1) = i
    myarr(i, 2) = Split(Mydic(n), "|")(0)
    myarr(i, 3) = Split(Mydic(n), "|")(1)
    myarr(i, 4) = Split(Mydic(n), "|")(2)
Next
Range("A1").Resize(UBound(myarr), UBound(myarr, 2)) = myarr
[G3].Value = Format(Timer - t, "0.00")
i = 0


Rem ReDim Preserve arr
Range("A1:D" & iRow).ClearContents
t = Timer
For i = 1 To iRow
        ReDim Preserve brr(1 To 4, 1 To i)
        brr(1, i) = i
        brr(2, i) = i * 2
        brr(3, i) = i * 4
        brr(4, i) = i * 6
Next
Range("A1").Resize(i, 4) = Application.Transpose(brr)
[G4].Value = Format(Timer - t, "0.00")
Application.ScreenUpdating = True
End Sub

DevilW

你的数组+字典是什么鬼，运行过吗？

夸不得

DevilW 发表于 2019-7-23 22:39
你的数组+字典是什么鬼，运行过吗？

运行过啊，有时候不知道数组该定义多大的维度，先用字典储存数据，然后用循环把数据从字典转到数组，数组的维度就是字典的。count计数啊

boy8199

夸不得 发表于 2019-7-24 08:50
运行过啊，有时候不知道数组该定义多大的维度，先用字典储存数据，然后用循环把数据从字典转到数组，数组 ...

这样真的会快?

excelhomesnake

慢是肯定的，数组是连续的内存块，你要扩大范围，需要重新找一块连续的内存块，这过程慢在数据需要从原有的块中复制到新的块。

其实vba完全可以参考其他平台的列表结构，即在加入元素时发现已满，则把内容扩充2倍(其他系数)，这可以在时间与空间上取得平衡

夸不得

boy8199 发表于 2019-7-24 09:43
这样真的会快?

实际测试一下，速度还是会快，而且不用操心定义数组要定义多大的范围才不会超出上标

夸不得

excelhomesnake 发表于 2019-7-24 09:59
慢是肯定的，数组是连续的内存块，你要扩大范围，需要重新找一块连续的内存块，这过程慢在数据需要从原有的 ...

我以前也觉得这个很慢，一直没用，测试一下发现，10W次以内的循环还是不用担心速度的，而且写代码也方便些，如果超过10W次的循环就需要考虑一下速度了，vba很多地方都没有新的语言功能简单方便，但是唯一的优势就是对excel操作最方便了

boy8199

夸不得 发表于 2019-7-24 10:12
实际测试一下，速度还是会快，而且不用操心定义数组要定义多大的范围才不会超出上标

我的意思是,你先搞一下到字典里面,然后再搞到 数组,这不是浪费时间?
同时也浪费了内存.  当然 现在 内存 基本不用考虑,不过 这是个 编程习惯的问题.

DevilW

字典+数组也不见得比单独数组运行得快，1楼字典+数组代码先算出每一行的结果，然后合并，再拆开，最后赋值，比单独数组多了两个步骤，执行次数多了效率反而降低，字典只有1条记录（这样速度能不快吗），想要达到最快的速度，需要优化代码，尽可能减少代码执行次数，数据从数组再输出到单元格，占用的时间达到95%以上，如以下的代码百万行级的数据输出时间大约11秒左右，计算用时也才0.25秒不到

1. Sub test()
   
2.     r = 1000000
   
3.     [a:d].Clear
   
4.     t = Timer
   
5.     ReDim ar(1 To r, 1 To 4)
   
6.     For i = 1 To r
   
7.         ar(i, 1) = i
   
8.         ar(i, 2) = 2 * i
   
9.         ar(i, 3) = 4 * i
   
10.         ar(i, 4) = 6 * i
    
11.     Next
    
12.     [a1].Resize(r, 4) = ar
    
13.     [g1] = Timer - t
    
14. End Sub

复制代码

excelhomesnake

夸不得 发表于 2019-7-24 10:14
我以前也觉得这个很慢，一直没用，测试一下发现，10W次以内的循环还是不用担心速度的，而且写代码也方便 ...

其实这玩意在vba可以通过类模块做到通用化，只是国内社区没有人愿意做，大家都是几十年写着一样的遍历代码就满足了。