*

【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

【Access】リンクテーブルが接続(表示)できない。

Accessを使用していて、リンクテーブルが表示(接続)できない。 あまりないけど、困った時に

記事を読む

Microsoft Office

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

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

記事を読む

データベース

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

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

記事を読む

Microsoft Office

【Access】クエリ実行時に「引数が違います」と表示され実行できないときの対処法

Accessのクエリを実行すると 引数が違います とポップアップが表示され、クエリが実行できない

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

【Access】VBAを使わずにマクロでリンクテーブルの変更(変換)

以前にVBAを使ってリンクテーブルの変換をしましたが、 今回はVBAを使わずにマクロでリンクテーブ

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

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

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

記事を読む

Microsoft Office

【Access2007】ODBCを使ってSQL Server 2012に接続

Windows7のAccess2007をデータソース(ODBC)を使って、SQL Server 20

記事を読む

Microsoft Office

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

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

記事を読む

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 ↑