Excel VBA：程式碼優化，數值計算

前幾天幫FB網友看一個數值正規化問題，他是一個Excel VBA新手，上社團求助看看是否能幫他將程式碼進行調整，因為數值資料多，執行時間長，希望能優化程式碼。
他的原始程式跑了2個for迴圈，所以時間上有拉長，我將他的範例改成產生30000個隨機亂數來計算，比較程式碼優化前後的差異。
程式碼優化前

Const column As String = "A,B,C"

Sub 優化前()
    Dim t As Long
    t = Timer
    Sheets(1).Cells.Clear
    亂數產生器 30000
    NormalizeData_優化前
    MsgBox Format(Timer - t, "0.00") & "秒"
End Sub

Sub 亂數產生器(n As Integer)
    Dim i As Integer, col
    Randomize
    For Each col In Split(column, ",")
        For i = 1 To n
            Sheets(1).Cells(i, col) = Application.Round(Rnd() * 2000, 4)
        Next
    Next
End Sub

Sub NormalizeData_優化前()
    Dim maxes As Variant
    Dim i As Long, j As Long, cs As Long, rs As Long    
    cs = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).column
    rs = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).row    
    Application.ScreenUpdating = False
    For i = 1 To cs
        maxes = Application.WorksheetFunction.Max(ActiveSheet.Columns(i))
        For j = 1 To rs
           ActiveSheet.Cells(j, i) = Format(ActiveSheet.Cells(j, i) / maxes, "0.0000")
        Next j
    Next i
    Application.ScreenUpdating = True
End Sub

執行時間

程式碼優化後

Const column As String = "A,B,C"

Sub 優化後()
    Dim t As Long
    t = Timer
    Sheets(1).Cells.Clear
    亂數產生器 30000
    NormalizeData_優化後
    MsgBox Format(Timer - t, "0.00") & "秒"
End Sub

Sub 亂數產生器(n As Integer)
    Dim i As Integer, col
    Randomize
    For Each col In Split(column, ",")
        For i = 1 To n
            Sheets(1).Cells(i, col) = Application.Round(Rnd() * 2000, 4)
        Next
    Next
End Sub

Sub NormalizeData_優化後()
    Dim maxes As Double
    Dim i As Integer, row As Integer, col As Integer
    Application.ScreenUpdating = False
    With Sheets(1)
        row = Sheets(1).Cells(65535, 1).End(xlUp).row
        col = Sheets(1).Cells(1, 3000).End(xlToLeft).column
        For i = 1 To col
            maxes = Application.WorksheetFunction.Max(.Columns(i))
            .Cells(1, col + 1).Resize(row).FormulaArray = "=" & .Cells(1, i).Resize(row).Address & "/" & maxes
            .Cells(1, i).Resize(row).Value = .Cells(1, col + 1).Resize(row).Value
            .Cells(1, i).Resize(row).NumberFormatLocal = "0.0000"
        Next
        .Cells(1, col + 1).Resize(row).Clear
    End With
    Application.ScreenUpdating = True
End Sub

執行時間

Excel VBA程式碼相關優化方法，可參考「Excel VBA 實戰技巧｜金融數據x網路爬蟲」基礎篇 「05 Excel VBA 程式碼優化」