[EXCEL VBA]给VBA加速之Integer和Long的使用技巧

xgy0214dxy

有点将VBA学术研究了，推荐VBA版的DataBus的精华贴

xdlly

Sub Doint()

Const myMAX As Integer = 32767
Const myMIN As Integer = -32768
Dim i As Integer
Dim 加法 As Integer
Dim 减法 As Integer
Dim 乘法 As Integer
Dim 除法 As Integer
Dim 求余 As Integer
Dim cnt As Integer
Dim start As Double
start = Timer

'循环100次
For cnt = 1 To 1000
For i = myMIN + 1 To myMAX - 1
加法 = i + 1
减法 = i - 1
乘法 = i * 1
除法 = i / 1
求余 = i Mod 1
Next i
Next cnt
MsgBox "程序运行时间为：   " & Format(Timer - start, "0.00") & "  秒"
End Sub

Sub Dolong()
Const myMAX As Long = 32767
Const myMIN As Long = -32768
Dim i As Long
Dim 加法 As Long
Dim 减法 As Long
Dim 乘法 As Long
Dim 除法 As Long
Dim 求余 As Long
Dim cnt As Long
Dim start As Double
start = Timer


'循环100次
For cnt = 1 To 1000
For i = myMIN - 1 To myMAX - 1
加法 = i + 1
减法 = i - 1
乘法 = i * 1
除法 = i / 1
求余 = i Mod 1
Next i
Next cnt
MsgBox "程序运行时间为：   " & Format(Timer - start, "0.00") & "  秒"

End Sub
本人实测在64位Win8情况下运行1000次分别为3.22秒和3.19秒，区别不大

kkaiashen

win7-64  office-64 lint/long=1.5左右

mooncold

Sub Doint()

Const myMAX As Integer = 32767
Const myMIN As Integer = -32768
Dim i As Integer
Dim 加法 As Integer
Dim 减法 As Integer
Dim 乘法 As Integer
Dim 除法 As Integer
Dim 求余 As Integer
Dim cnt As Integer
Dim start As Double
start = Timer

'循环100次
For cnt = 1 To 1000
For i = myMIN + 1 To myMAX - 1
加法 = i + 1
减法 = i - 1
乘法 = i * 1
除法 = i / 1
求余 = i Mod 1
Next i
Next cnt
MsgBox "程序运行时间为：   " & Format(Timer - start, "0.00") & "  秒"
End Sub

Sub Dolong()
Const myMAX As Long = 32767
Const myMIN As Long = -32768
Dim i As Long
Dim 加法 As Long
Dim 减法 As Long
Dim 乘法 As Long
Dim 除法 As Long
Dim 求余 As Long
Dim cnt As Long
Dim start As Double
start = Timer


'循环100次
For cnt = 1 To 1000
For i = myMIN - 1 To myMAX - 1
加法 = i + 1
减法 = i - 1
乘法 = i * 1
除法 = i / 1
求余 = i Mod 1
Next i
Next cnt
MsgBox "程序运行时间为：   " & Format(Timer - start, "0.00") & "  秒"

End Sub

=============================================
win10 64位， office 365 32位，运行的结果：
Int ： 3.96sec
Long： 3.47sec
差别不明显啊，14%左右吧。

moyang

win7-64  office-32
1000
Int = 2.66sec
long=2.76sec
10000
Int = 26.09sec
long=27.35sec

nk0769

xpsp3 office2010
Doint 2.34
Dolong 2.45
Donone 2.63

Sub Doint()

Const myMAX As Integer = 32767
Const myMIN As Integer = -32768
Dim i As Integer
Dim 加法 As Integer
Dim 减法 As Integer
Dim 乘法 As Integer
Dim 除法 As Integer
Dim 求余 As Integer
Dim cnt As Integer
Dim start As Double
start = Timer

'循环100次
For cnt = 1 To 1000
For i = myMIN + 1 To myMAX - 1
加法 = i + 1
减法 = i - 1
乘法 = i * 1
除法 = i / 1
求余 = i Mod 1
Next i
Next cnt
MsgBox "程序运行时间为：   " & Format(Timer - start, "0.00") & "  秒"
End Sub

Sub Dolong()
Const myMAX As Long = 32767
Const myMIN As Long = -32768
Dim i As Long
Dim 加法 As Long
Dim 减法 As Long
Dim 乘法 As Long
Dim 除法 As Long
Dim 求余 As Long
Dim cnt As Long
Dim start As Double
start = Timer


'循环100次
For cnt = 1 To 1000
For i = myMIN - 1 To myMAX - 1
加法 = i + 1
减法 = i - 1
乘法 = i * 1
除法 = i / 1
求余 = i Mod 1
Next i
Next cnt
MsgBox "程序运行时间为：   " & Format(Timer - start, "0.00") & "  秒"

End Sub


Sub Donone()
myMAX = 32767
myMIN = -32768
Dim i As Long
Dim 加法 As Long
Dim 减法 As Long
Dim 乘法 As Long
Dim 除法 As Long
Dim 求余 As Long
Dim cnt As Long
Dim start As Double
start = Timer


'循环100次
For cnt = 1 To 1000
For i = myMIN - 1 To myMAX - 1
加法 = i + 1
减法 = i - 1
乘法 = i * 1
除法 = i / 1
求余 = i Mod 1
Next i
Next cnt
MsgBox "程序运行时间为：   " & Format(Timer - start, "0.00") & "  秒"

End Sub

joy.008@163.com

hqswww 发表于 2012-7-9 19:47
Private Sub CommandButton1_Click()
Const ma As Long = 30000
Const mi As Long = -30000

Dim p%
感觉这个变量会溢出呢

ning84

VB 中 integer 范围是-32768 ~ 32767

1. '''
   
2. Sub deldel11()
   
3.    
   
4. 
   
5.    Dim oNum As Integer, oSum As Integer
   
6.    
   
7.    Dim ii, jj, Kk
   
8.    Dim TabRow, TabCol, sTabRow, sTabCol     ''
   
9.         sTabRow = 1
   
10.         sTabCol = 1
    
11.         TabRow = 20
    
12.         TabCol = 2
    
13.         oSum = sTabCol
    
14.         ''
    
15.         ''
    
16.         For Kk = 1 To 60
    
17.             For ii = sTabRow To sTabRow + TabRow
    
18.                  For jj = sTabCol To TabCol + sTabCol
    
19.                        oNum = oNum + 1
    
20.                        oSum = oSum + oNum
    
21.                        Debug.Print oNum, Kk, ii, jj, oSum
    
22.                  Next jj
    
23.             Next ii
    
24.         Next Kk
    
25. 
    
26. End Sub

复制代码