*

【Excel】VBAでセルの書き込みを5倍高速化する方法

Excel VBAで While文やFOR文を使用してループさせ、行、列をカウントアップして、
大量のデータをセルに直接書き込む

この「直接書き込む」を「レコードセット」で書き込むことで、
約5倍速くなったので、検証結果とサンプルコード。

検証した環境

OS Windows 8.1
Office Office 2013
CPU Intel Core i5
メモリ 8GB

検証内容

Excelのセルに「直接書込み」と「レコードセット書込み」を行い、

横(列)を26列(A列~Z列)、縦(行)を10000~100000行にデータを書き込み、

時間を比較する。

検証結果

・検証結果の表

行数 直接書込み レコードセット書込み
10000行 4秒 1秒
20000行 8秒 2秒
30000行 12秒 2秒
40000行 15秒 3秒
50000行 19秒 4秒
100000行 38秒 7秒

 

小数点以下を省略しているため、10000行の時は約4倍程度だが、

100000行の時は、5倍以上の速度差が出ている。

 

・検証結果のグラフ

直接書込みとレコードセット書込み比較グラフ

レコードセットを使って書き込んだ場合、約5倍以上、高速で処理できる。

当然、環境にも左右されるが、セルに大量に書き込みを行う場合、レコードセットを使用して書き込んだ方が圧倒的に効率が良い。

検証に使用したサンプルVBA

レコードセットを使ったデータ書込み

'######################################################
'
' レコードセットを使ったデータの書き込み
'
'   ※参照設定で「Microsoft ActiveX Data Objects 6.1 Library」にチェック
'######################################################
Sub レコードセットを使ったエクセル書き込み()

    '--------------------------------------------------
    ' 変数定義
    '--------------------------------------------------
    Dim ColCnt As Long                      '列カウント用
    Dim RowCnt As Long                      '行カウント用
    Dim i As Long                           'レコードセット作成ループカウント
    Dim StartTime As String                 '開始時間取得

    '--------------------------------------------------
    ' 時間測定開始
    '--------------------------------------------------
    StartTime = Time
    '--------------------------------------------------
    ' スクリーンアップデート OFF
    '--------------------------------------------------
    Application.ScreenUpdating = False
    '--------------------------------------------------
    ' レコードセットの作成
    '--------------------------------------------------
    'レコードセット定義
    Dim Res As ADODB.Recordset          'エクセル書き込み用レコードセット
    'セット
    Set Res = New ADODB.Recordset
    
    'カラムを26個(エクセルシートのA~Z列)を追加
    For i = 1 To 26
        Res.Fields.Append i, adInteger
    Next
    'レコードセットオープン
    Res.Open

    '--------------------------------------------------
    ' 横:26(A~Z)、縦:100000にデータを入力するループ
    '--------------------------------------------------

    '初期値設定
    ColCnt = 1
    RowCnt = 1
    
    '行が100000を超えるまでループ
    Do While RowCnt <= 100000
        Res.AddNew
        '列が26(A~Z)までループ
        Do While ColCnt <= 26
            '--------------------------------------------------
            '本来直接シートに書き込むところをレコードセットへ格納
            '--------------------------------------------------
            Res(ColCnt - 1) = RowCnt & ColCnt
            'Cells(RowCnt, ColCnt).Value = RowCnt & ColCnt
    
            ColCnt = ColCnt + 1
        Loop
    
        '次のレコードへ移動
        Res.MoveNext
        '列カウントのリセット
        ColCnt = 1
        '行のカウントアップ
        RowCnt = RowCnt + 1
    Loop

    '--------------------------------------------------
    ' レコードセットをシートに貼り付け
    '--------------------------------------------------
    'レコードセット先頭へ
    Res.MoveFirst
    
    '貼り付け
    Range("A1").CopyFromRecordset Res

    'レコードセットのクローズ
    Res.Close
    Set Res = Nothing

    '--------------------------------------------------
    ' スクリーンアップデート ON
    '--------------------------------------------------
    Application.ScreenUpdating = True
    '--------------------------------------------------
    ' 終了時間とメッセージ表示
    '--------------------------------------------------
    MsgBox "開始時間:" & StartTime & vbCrLf & _
           "終了時間:" & Time


End Sub

セルに直接データ書込み

'######################################################
'
' セルへ値の直接書き込み
'
'######################################################
Sub エクセル書き込み()

    '--------------------------------------------------
    ' 変数定義
    '--------------------------------------------------
    Dim ColCnt As Long                      '列カウント用
    Dim RowCnt As Long                      '行カウント用
    Dim StartTime As String                 '開始時間取得

    '--------------------------------------------------
    ' 時間測定開始
    '--------------------------------------------------
    StartTime = Time
    '--------------------------------------------------
    ' スクリーンアップデート OFF
    '--------------------------------------------------
    Application.ScreenUpdating = False
    '--------------------------------------------------
    ' 横:26(A~Z)、縦:100000にデータを入力するループ
    '--------------------------------------------------

    '初期値設定
    ColCnt = 1
    RowCnt = 1
    
    '行が100000を超えるまでループ
    Do While RowCnt <= 100000
    
        '列が26(A~Z)を超えるまでループ
        Do While ColCnt <= 26
            Cells(RowCnt, ColCnt).Value = RowCnt & ColCnt
            ColCnt = ColCnt + 1
        Loop
    
        '列カウントのリセット
        ColCnt = 1
        '行のカウントアップ
        RowCnt = RowCnt + 1
    Loop

    '--------------------------------------------------
    ' スクリーンアップデート ON
    '--------------------------------------------------
    Application.ScreenUpdating = True
    '--------------------------------------------------
    ' 終了時間とメッセージ表示
    '--------------------------------------------------
    MsgBox "開始時間:" & StartTime & vbCrLf & _
           "終了時間:" & Time


End Sub

 

Adsense

関連記事

Microsoft Office

【Excel】複数のエクセル(ブック)のシートを一つのブックに結合するVBA

複数のエクセルのシートを一つのエクセルにまとめるVBAを作ってみた。 ソースコードは使い方の下

記事を読む

Microsoft Office

【Outlook】PDFの添付ファイル付のメール送信でフリーズ。

OutlookでPDFファイルを添付して、メール送信するとOutlookがフリーズし、Outlook

記事を読む

Microsoft Office

【Outlook】Gmailを使う時のOutlookの設定方法

Gmailアカウント側の設定を行った上で、Outlookの設定を行います。 Gmailアカウン

記事を読む

Microsoft Office

【Excel】「メモリまたはディスクの空き容量が~」のポップアップで開けない時の対処法

メモリまたはディスクの空き容量が不足しているため、ドキュメントを開いたり、保存したりできません。

記事を読む

Microsoft Office

【Outlook】送信時の0x80040201エラーの対処

発生した環境 OSはWindows7。Outlookは2013。イーモバイルのPocket W

記事を読む

Microsoft Office

【Access】リンクテーブルの一括変換。ODBCを使わずにSQL Serverへ接続

Accessのリンクテーブルの接続先を一括で変更する方法 この方法なら、ODBCを使用していな

記事を読む

Microsoft Office

【Excel】関数を一括でメモ帳などにコピーする方法

  セルに入力されている関数一つのみコピーしたい場合、対象セルを選択して、数式バーか

記事を読む

Microsoft Office

【Outlook】文字化けを直す方法

メールをチェックしているとプレビューウインドに文字化け表示。 メール(Outlook)

記事を読む

Microsoft Office

【Access】削除クエリの「指定されたテーブルから削除できませんでした。」の対処法

削除クエリで「指定されたテーブルから削除できませんでした。」と ポップアップが表示され、クエリが実

記事を読む

Microsoft Office

【Access】「更新可能なクエリであることが必要です」のクエリのエラーポップアップ

Accessでクエリを作成し、実行すると 「更新可能なクエリであることが必要です。」

記事を読む

Adsense

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Adsense

Microsoft Office
【Access】削除クエリの「指定されたテーブルから削除できませんでした。」の対処法

削除クエリで「指定されたテーブルから削除できませんでした。」と ポッ

Microsoft Office
【PowerPoint】表や図形、画像オブジェクトの位置をピッタリ合わせる方法

パワーポイントで、図形や画像等のオブジェクトの細かい位置調整は、 完

データベース
【SQL Server】Excel VBAのレコードセットを使ってテーブルの一括更新

前々回は読み取り専用でレコードセットへ取得し、結果をエクセルへ書き出し

application
【Visual Studio】デバック、ブレークポイントで止まらない。

Visual Studio 2015でVB.netのプログラムをデバッ

データベース
【SQL Server】Excel VBAでSELECT文を実行してレコードセットへ取得

前回は、テーブルのレコードをVBAで直接更新(Insert/updat

→もっと見る

PAGE TOP ↑