你可能不知道的VBA加速技巧

kxh83527

对哦，不是提问者要达到的效果哦~~~

ldy888

感谢楼上的把这么个好帖顶上来,顺便说一下我的理解.

这个宏是表格VS数组 逐一赋值,都在表上操作,速度当然变化不大。

Sub Test2()
Dim i As Long, j As Long, buf As Long, C As Variant
Application.ScreenUpdating = False
Sheets(2).Cells.ClearContents
t1 = Time
C = Range("A1:CV10000")
For i = 1 To 10000
For j = 1 To 100
Worksheets(2).Cells(i, j).Value = C(i, j)
Next j
Next i
Application.ScreenUpdating = True
MsgBox Minute(Time - t1) & "分" & Second(Time - t1) & "秒"
End Sub

这个宏是数组VS数组 也是逐一赋值,但都在数组中操作,最后再把结果整体赋值到表上

Sub Test3()
Dim i As Long, j As Long, buf As Long, C As Variant
Application.ScreenUpdating = False
Sheets(2).Cells.ClearContents
t1 = Time
C = Range("A1:CV10000")

b = Worksheets(2).Range("A1:CV10000") '再搞个空数组
For i = 1 To 10000
For j = 1 To 100
b(i, j) = C(i, j) '数组ＶＳ数组，当然快了
Next j
Next i
' 运行到这,数组赋值完毕,用时1秒
Worksheets(2).Range("A1:CV10000") = b '把已得到值的数组整体填到表上,就这句用时要2秒.
Application.ScreenUpdating = True
MsgBox Minute(Time - t1) & "分" & Second(Time - t1) & "秒"
End Sub

text2 用时９７秒，text3 用时３秒．

这样比较应该合要求了．

[此贴子已经被作者于2006-6-16 19:27:33编辑过]

auteet

以下是引用[I]ldy888[/I]在2006-6-16 19:27:07的发言：[BR]

感谢楼上的把这么个好帖顶上来,顺便说一下我的理解.

这个宏是表格VS数组 逐一赋值,都在表上操作,速度当然变化不大。

Sub Test2()
Dim i As Long, j As Long, buf As Long, C As Variant
Application.ScreenUpdating = False
Sheets(2).Cells.ClearContents
t1 = Time
C = Range("A1:CV10000")
For i = 1 To 10000
For j = 1 To 100
Worksheets(2).Cells(i, j).Value = C(i, j)
Next j
Next i
Application.ScreenUpdating = True
MsgBox Minute(Time - t1) & "分" & Second(Time - t1) & "秒"
End Sub

这个宏是数组VS数组 也是逐一赋值,但都在数组中操作,最后再把结果整体赋值到表上

Sub Test3()
Dim i As Long, j As Long, buf As Long, C As Variant
Application.ScreenUpdating = False
Sheets(2).Cells.ClearContents
t1 = Time
C = Range("A1:CV10000")

b = Worksheets(2).Range("A1:CV10000") '再搞个空数组
For i = 1 To 10000
For j = 1 To 100
b(i, j) = C(i, j) '数组ＶＳ数组，当然快了
Next j
Next i
' 运行到这,数组赋值完毕,用时1秒
Worksheets(2).Range("A1:CV10000") = b '把已得到值的数组整体填到表上,就这句用时要2秒.
Application.ScreenUpdating = True
MsgBox Minute(Time - t1) & "分" & Second(Time - t1) & "秒"
End Sub

text2 用时９７秒，text3 用时３秒．

这样比较应该合要求了．

学习！

爱歌学习

以下是引用[I]ldy888[/I]在2006-6-16 19:27:07的发言： C = Range("A1:CV10000")

b = Worksheets(2).Range("A1:CV10000") '再搞个空数组
For i = 1 To 10000
For j = 1 To 100
b(i, j) = C(i, j) '数组ＶＳ数组，当然快了
Next j
Next i
' 运行到这,数组赋值完毕,用时1秒
Worksheets(2).Range("A1:CV10000") = b '把已得到值的数组整体填到表上,就这句用时要2秒.

为什么要搞空数组呢？用42楼的直接赋数组值给单元格不是更快？

以下是引用[I]ghx321[/I]在2005-11-22 21:11:13的发言：[BR]

赋值的话，用这样的方法可能更快。

Sub Test2() Dim i As Long, j As Long, buf As Long, C As Variant Application.ScreenUpdating = False Sheets(2).Cells.ClearContents t1 = Timer C = Range("A1:CV10000") Worksheets(2).Range("A1:CV10000") = C Application.ScreenUpdating = True t1 = Timer - t1 MsgBox t1 End Sub

andysky

Sheets(2).Range("a1:cv10000").Value = Sheets(1).Range("a1:cv10000").Value

用这一句就行了，4-6秒钟。

不过要讨论数组用法又当别论了。

Laolangsex

第一讲：在VBA中使用Range()绝对比cell()快很多，速度取决单元格的数据类型

supper-ST

time的计时方法如何精确到毫秒？用秒计算单位太大了

huaguofen

[em09]

kailee

强帖，决不能让它沉下！

cfanwang

MARK

学习