最新消息

[公告2014/05/30] 如有需要將部落格中,任何一篇文章的程式碼使用在商業用途,請與我聯繫。

[公告2015/04/26] Line版的 iInfo程式與投資應用 群組已上線想加入的朋友們,請先查看 "入群須知" 再與我聯繫 Line : aminwhite5168,加入請告知身分與回答 "入群須知" 的問題。

[公告2018/04/22] 台北 Python + Excel VBA 金融資訊爬蟲課程,課程如網頁內容 金融資訊爬蟲班:台北班 Python 金融資訊爬蟲、EXCEL VBA 金融資訊爬蟲

[公告2019/01/08] 請注意:我再次重申,部落格文章的程式碼,是要提供各位參考與學習,一旦網頁改版請自行修改,別要求東要求西要我主動修改,你們用我寫東西賺錢了、交差了,請問有分我一杯羹嗎?既然賺錢沒分我,請問有什麼理由要求我修改,如果沒能力改,就花錢來找我上課。

[公告2019/12/01] 若各位有 Excel VBA 案子開發需求,歡迎與我聯繫,可接案處理。

[公告2020/05/22] 頁面載入速度慢,起因為部分JS來源(alexgorbatchev.com)失效導致頁面載入變慢,目前已做調整,請多見諒。

2019年9月25日 星期三

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 程式碼優化」

沒有留言:

張貼留言