有关书典对象速度 解惑？期待狼版等字典高手解答

hyy514

http://club.excelhome.net/viewthread.php?tid=274302&replyID=&skin=0

讨论]多列中查找重复值的最优方案

高手们的讨论精彩绝伦，看完对二个代码的速度不点不解？

狼版代码：

Sub macro111()
Dim arr, i As Long, n As Long, b(1000000) As Byte, t As Single
Application.ScreenUpdating = False
arr = [A1:d60000]
t = Timer
For i = 1 To 60000
b(arr(i, 1)) = 1
Next

For i = 1 To 60000
If b(arr(i, 2)) = 1 Then b(arr(i, 2)) = 2
Next

For i = 1 To 60000
If b(arr(i, 3)) = 2 Then n = n + 1: arr(n, 4) = arr(i, 3)
Next
[d1].Resize(n, 1) = Application.Index(arr, 0, 4)
Application.ScreenUpdating = True
MsgBox Timer - t & "秒!"
End Sub

ldy888代码：

Sub Macro22()
    Dim arr, i As Long, n As Long, b(1000000) As Boolean, c(1000000) As Boolean, t As Single
    Application.ScreenUpdating = False
    Dim d As New Dictionary
'    Set d = CreateObject("Scripting.Dictionary")
    arr = Range("a1:c60000")
    t = Timer
    For i = 1 To 60000
        b(arr(i, 1)) = True
    Next

    For i = 1 To 60000
        If b(arr(i, 2)) Then c(arr(i, 2)) = True
    Next
   
    For i = 1 To 60000
        If c(arr(i, 3)) Then d(arr(i, 3)) = 1
    Next
    [d1].Resize(d.Count, 1) = Application.Transpose(d.Keys)
    Application.ScreenUpdating = True
    MsgBox Timer - t & "秒!" '最快到到 0.0625秒

End Sub
以上二个代码循环道数一样，第二个代码比第一个多了个c(1000000) As Boolean数组和一个书典对象，

从占用内存上和按照访问对象越频繁速度越慢的原则，第二个代码应该更慢才对，但事实上是第二个代码更快，不得其解？水平有限，但学习心切，所以发个贴，望高手不吝解释解释，为谢！

[此贴子已经被作者于2007-11-6 14:18:28编辑过]

northwolves

空间换时间.定义一个空数组用时很短:

Sub macro1()
Dim i As Long, t As Single, c() As Boolean
t = Timer
For i = 1 To 10
ReDim c(1000000)
Next
MsgBox Timer - t & "秒"
End Sub

hyy514

谢谢狼版回复，所谓空间是体现在哪里呢？

第一个代码是在判断b(1000000)序号赋值1,2作为标记

所谓空间是指在增加的数组 c(1000000) As Boolean中赋值，而不在原来的数组b(1000000)中赋值,而加快了速度了吗?还是因为Boolean和 Byte类型的区别影响到速度?

最终比较出的结果用d(arr(i, 3)) =""获取d.Keys输出 和 n = n + 1: arr(n, 4) = arr(i, 3)获取,

Index(arr, 0, 4)输出,速度上应该没什么区别吧?

[此贴子已经被作者于2007-11-6 13:54:04编辑过]

hyy514

狼版，我用你的第一个代码，用字典代替数组输出，而不增加 c(1000000)，我这边测试，速度更优于第二个代码。请问这里数组输出和字典输出为什么会有速度的区别？

可不以针对这个例子解释下空间的概念？

Sub macro111()
Dim arr, i As Long, n As Long, b(1000000) As Byte, t As Single
 Dim d As New Dictionary
Application.ScreenUpdating = False
arr = [A1:d60000]
t = Timer
For i = 1 To 60000
b(arr(i, 1)) = 1
Next

For i = 1 To 60000
If b(arr(i, 2)) = 1 Then b(arr(i, 2)) = 2
Next

For i = 1 To 60000
If b(arr(i, 3)) = 2 Then d(arr(i, 3)) = ""
Next
[d1].Resize(d.Count, 1) = Application.Transpose(d.Keys)
Application.ScreenUpdating = True
MsgBox Timer - t & "秒!"
End Sub

[此贴子已经被作者于2007-11-6 14:12:11编辑过]