相信有不少朋友也跟筆者一樣遇過下面的情況
明明沒有寫多少EXCEL VBA 腳本,
但卻很容易遇到瘋狂轉圈圈就當掉的情況

以下是一段使用For 迴圈寫的腳本
Sub Reset()
    Dim G93 As Integer
    Worksheets("Summary").Columns("J:BG").ColumnWidth = 10.13
    Worksheets("Summary").Range("J1:BG50").Clear
    
    G93 = 11
    For i = 1 To 6
            Worksheets("6.21").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.22").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.23").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.31").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.32").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.41").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.42").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.43").Cells(3, G93).MergeArea.ClearContents
            Worksheets("6.51").Cells(3, G93).MergeArea.ClearContents
            G93 = G93 + 1
    Next i
    
    Worksheets("6.21").Cells(56, 3).MergeArea.ClearContents
    Worksheets("6.21").Cells(58, 3).MergeArea.ClearContents
    Worksheets("6.21").Cells(61, 3).MergeArea.ClearContents
    Worksheets("6.21").Cells(63, 3).MergeArea.ClearContents
    
   
    G93 = 67
        For i = 0 To 4
            Worksheets("6.22").Cells(G93, 13 + i).MergeArea.ClearContents
            Worksheets("6.22").Cells(G93 + 1, 13 + i).MergeArea.ClearContents
        Next i
    
    G93 = 68
    For j = 1 To 2
        For i = 1 To 7
            Worksheets("6.23").Cells(G93, 3).MergeArea.ClearContents
            G93 = G93 + 1
        Next i
        G93 = 76
    Next j
    Worksheets("6.31").Range("H111:K127").Clear
    Worksheets("6.31").Range("N111:Q124").Clear
    
    G93 = 93
    For j = 1 To 3:
        For i = 1 To 4:
            Worksheets("6.32").Range("G" & G93 & ":R" & G93).Clear
            If i = 4 Then
                Exit For
            End If
            G93 = G93 + 2
        Next i
        G93 = G93 + 5
    Next j    
    MsgBox "All data Clear", vbOKOnly + vbInformation, "Clear"
End Sub

明明程式的結構很簡單, 為什麼會掛呢?

筆者在不斷實驗後, 發現以下幾個情況會掛掉

1. 太常使用For 跟 For each
2. 寫的數學公式太複雜
3. 讓excel頁面的狀態不斷更新

解決方案:

1. 不要瘋狂用For 迴圈, 請改用其他寫法
2. 關閉狀態自動更新的功能
   • 簡單的說 就是不要讓Excel 瘋狂去刷新目前的狀態, 只在需要刷新狀態的時候再切換設定值即可

Application.ScreenUpdating = False

3. 關閉自動計算的功能
   • 意思是 當計算完後就將目前的數值寫入變數後關閉自動計算

Dim calc_configure as Long
calc_configure = Application.Calculation
Application.Calculation = xlCalculationManual
**write your code here **
Application.Calculation = calc_configure

結論:
使用這些做法都可以加速Excel活頁簿中巨集執行時的工作效率及讓腳本程式的穩定度提高

參考資料:

• emoji text faces
• Excel Macro sample code