0

## 請大大幫忙優化一個簡單的excel vba sub

wsj 2019-12-06 09:17:491677 瀏覽

Sub wsj()
Application.EnableEvents = False
Application.Interactive = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Dim i, j, k As Integer
k = 1
With Worksheets("眾數-1")
For i = 1 To 300
For j = 30 To 400 - i
.Range("h2") = i
.Range("l2") = j
If .Range("m13") < 3 And .Range("g13") > 9 Then
k = k + 1
.Range("bd" & (k)).Value =.Range("h2")
.Range("be" & (k)).Value =.Range("l2")
.Range("bf" & (k)).Value = .Range("m13")
End If
Next j
Next i
End With
Application.EnableEvents = True
Application.Interactive = True
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
End Sub

### 3 個回答

2
paicheng0111
iT邦高手 1 級 ‧ 2019-12-06 16:13:24

``````Sub wsj2()
Dim i As Long, j As Long
Dim ary As Variant

With Worksheets("眾數-1")
For i = 1 To 300
.Range("h2") = i

For j = 30 To 400 - i
.Range("l2") = j
If .Range("m13") < 3 And .Range("g13") > 9 Then
If IsArray(ary) Then
ReDim Preserve ary(2, Ubound(ary, 2) + 1) As Variant
Else
ReDim ary(2, 0) As Variant
End If

ary(0, Ubound(ary, 2)) = .Range("h2")
ary(1, Ubound(ary, 2)) = .Range("l2")
ary(2, Ubound(ary, 2)) = .Range("m13")
End If
Next j
Next i
.Range("bd2").ReSize(Ubound(ary, 2) + 1, 3).Value = Application.Transpose(ary)
End With
End Sub
``````

ckp6250 iT邦好手 1 級 ‧ 2019-12-06 16:20:29 檢舉

.Range("m13") 和 .Range("g13") 和迴圈沒有任何關係，更應該拉到迴圈外，我剛又修了一下，請您也看一下。

`.Range("m13")``.Range("g13")` 和迴圈沒有任何關係，更應該拉到迴圈外，這下省跑好幾萬回。

ckp6250 iT邦好手 1 級 ‧ 2019-12-06 16:25:35 檢舉

ok

wsj iT邦新手 5 級 ‧ 2019-12-07 21:18:22 檢舉

2

iT邦大神 1 級 ‧ 2019-12-06 11:15:12

m13 和 g13 是用什麼`公式`

`把該公式整個寫到 VBA 裡面來計算`

`以此可以推測關鍵在於 m13,g13 的公式複雜度`

1
ckp6250
iT邦好手 1 級 ‧ 2019-12-06 11:36:34
``````m = .Range("m13")
g = .Range("g13")
For i = 1 To 300
.Range("h2") = i
For j = 30 To 400 - i
.Range("l2") = j
If m < 3 And g > 9 Then
k = k + 1
.Range("bd" & (k)).Value = i
.Range("be" & (k)).Value = j
.Range("bf" & (k)).Value = m
End If
Next j
Next i
``````

.Range("m13") 和 .Range("g13") 和迴圈沒有任何關係，更應該拉到迴圈外，這下省跑好幾萬回。

.Range() 是物件，i 是變數，存取物件比較耗費資源，

Franky Chen iT邦研究生 3 級 ‧ 2019-12-06 15:41:40 檢舉

`.Range("m13")``.Range("g13")` 和迴圈沒有任何關係，更應該拉到迴圈外，這下省跑好幾萬回。