ExcelVBA高速化の手法の一つに、セルに代入する時は1セルずつ代入するのではなく、代入する値を配列に入れて一度に代入するという手法がある。

GPT-4にExcelVBAを書いてもらうと、だいたい初めは1セルずつ代入するマクロを提示してくる。処理する件数が少なければそれでも特に問題はないが、処理が遅いと感じたら「高速化してください」と依頼し配列に書き直してもらおう。

例

「セル範囲A1:E10に、各セルの列番号と行番号を掛けた値を入力するマクロを書いてください。」と依頼すると書いてくれるマクロがこちら。この程度の件数なら何も問題ないが、セルへの書き出しは1セルごとに行っている。

Sub FillMultiplicationTable()
    Dim ws As Worksheet
    Dim rowNum As Long, colNum As Long

    ' 現在のアクティブシートを取得
    Set ws = ActiveSheet

    ' A1:E10の範囲をループ
    For rowNum = 1 To 10
        For colNum = 1 To 5
            ' セルに列番号と行番号の積を設定
            ws.Cells(rowNum, colNum).Value = rowNum * colNum
        Next colNum
    Next rowNum

    ' 完了メッセージ
    MsgBox "A1:E10の範囲に値を入力しました。", vbInformation
End Sub

「より高速に動作するように改良できますか？」と要望すると、画面描画の停止に加え、配列で一度に出力するよう書き直してくれる。最初からこちらで書いてほしい気もするが、可読性を優先しているのかもしれない。

Sub FillCellsWithProductOptimized()
    Dim ws As Worksheet
    Dim rowNum As Long
    Dim colNum As Long
    Dim dataArray(1 To 10, 1 To 5) As Long
    
    ' アクティブシートを対象
    Set ws = ActiveSheet
    
    ' 高速化設定
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' 配列に計算結果を格納
    For rowNum = 1 To 10
        For colNum = 1 To 5
            dataArray(rowNum, colNum) = rowNum * colNum
        Next colNum
    Next rowNum
    
    ' 配列を一括でセルに反映
    ws.Range("A1:E10").Value = dataArray
    
    ' 高速化設定を解除
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub