编写高效Excel VBA代码的最佳实践

tjz1001

在学习中,感谢

lhsh36183

读后受益匪浅！谢谢！

qczvba

值得一看，慢慢学习。谢楼主。

Zamyi

“没有最好，只有更好”这是我的观点。挑点毛病：“对于要实现的某一功能，如果有Excel的内置函数能够实现，那么就用Excel的内置函数，不需要另外自定义函数，因为自定义的函数总比Excel内置的函数慢。”
application.index、transpose就没有自定义函数快。
再有：下面程序慢的原因是大部分时间用在读写单元格，
Sub AddItSlow()
Dim Start As Double, Finish As Double
Start = Timer
'--------------------------------------
'为了进行测试,我们循环5次
Dim N As Long
For N = 1 To 5
'***************************
Dim Cell As Range
For Each Cell In Worksheets(2).Range("A1:G200")
[a1] = [a1] + Cell.Value
Next Cell
'***************************
Next N
'--------------------------------------
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start
End Sub

看看改成下面能快几倍：
Sub AddItSlow()
Dim Start As Double, Finish As Double
r = Worksheets(2).Range("A1:G200")
Start = Timer
Dim N As Long
For N = 1 To 5
t = 0
For Each c In r
t = t + c
Next
Next
Finish = Timer
MsgBox "本次运行的时间是" & Finish - Start & "和：" & t
End Sub

Zamyi

如果换个角度，已知一个数组a，对其求总和，再看看谁快：
Sub Test()
Dim a(1 To 60000, 1 To 10)
For i = 1 To 60000
For j = 1 To 10
a(i, j) = Int(100 * Rnd()) + 1
Next
Next

t = Timer
For Each c In a
cc = cc + c
Next
MsgBox Timer - t

t = Timer
b = Application.WorksheetFunction.Sum(a)
MsgBox Timer - t
End Sub

AVEL

收藏之，呵呵 感谢楼主分享

zhangxiaobin

谢谢！学习一下

guzi

总能在EH中找到的好的东西，非常感谢各位版主

UUH

谢谢多谢

蓝天云朵

谢谢楼主分享！