*

【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

【拡張子:accde】Access2007でaccdeの使い道と作成方法

うちの職場ではEUC(End User Computing)として Access2007 を多用して

記事を読む

データベース

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

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

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

【Access】クエリで小数点の切り下げ、切り上げ

Access2007には、roundup、rounddown関数がない。 なので、純粋に関数だ

記事を読む

Microsoft Office

【Outlook】保存フォルダ(archive.pst)が開けない。

急に「保存フォルダ」が開けなくなった。 Outlookのバージョンは、2007のSP3。

記事を読む

Microsoft Office

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

Windows7の64bitでSQL Server 2012接続用のODBCを作成し Access

記事を読む

Microsoft Office

【Excel】エクセルを起動(実行)しても、何も表示されない。

仕事の共有で使用しているエクセルを開くと、Excelの枠だけ表示されて何も表示されない。 壊れ

記事を読む

データベース

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

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

記事を読む

Microsoft Office

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

パワーポイントで、図形や画像等のオブジェクトの細かい位置調整は、 完璧主義のこだわり派には非常にイ

記事を読む

Microsoft Office

【Access】循環参照エラーの回避方法

Accessでクエリを実行しようとすると クエリ定義の SELECT で指定されている別名

記事を読む

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 ↑