*

【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】エクセル。シートの名前を変更しようとしたらエラーポップアップ

エクセルでシート名を変更しようとしたときに… シートの名前をほかのシート、Visual B

記事を読む

Microsoft Office

【Microsoft Office】Access 2007でMDBファイルが開けない

こんばんわ。今日、ハマッたことを書きたいと思います。 会社の内線がなり「共有フォルダ上のAcces

記事を読む

Microsoft Office

【Outlook】開封メッセージ送信を先送りする技。

朝一でOutlookを起動したら、開封確認のメッセージ。 すごく嫌。個人的には、非常に嫌い。

記事を読む

Microsoft Office

【Excel】グラフ(オブジェクト)の名前変更方法

Excel2013でのお話。 とりあえず、僕が探したところ三つありました。 【1つ目】名前ボ

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

【Office】Access2007のピボットテーブルとExcel連携

仕事でAccess2007でEXCELみたいにピボットテーブルを使えますか? と質問を頂いた。

記事を読む

Microsoft Office

【Excel】複数項目を指定した昇順、降順の並べ替え方法

Excel 2013でのお話。 なぜかわからないが、Excelのオートフィルターを使うと単一の

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

【Outlook】プレビューウインドウ(閲覧ウインドウ)で表示したら、メール既読のオフ設定

前回、「Outlookの開封メッセージ送信を先送りにする方法」で、少し書きましたが、 私用のメ

記事を読む

Microsoft Office

【Access2007】ODBCの罠。Windows7のコントロールパネルから作成したODBCがない。

Windows7の64bitでSQL Server 2012接続用のODBCを作成し 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 ↑