【Excel/VBA】クラスを使ってデータの流し込み

例によって、VBAでクラスを使いたがり。

なぜならクラスを使うと「VBAの達人っぽい感じがする」と思ってるから。

レベル低めの人ほど見栄を張りたがるというのはこうゆうことか(‘ω’)ノ

最初に断っておきます。自己満足、自分用記録です。

「もとデータ」シートのデータをkeyをもとにして「データ」シートに流し込む。

VLookUp関数でやることろをVBAでやってみようと。

わかりやすい解説で勝手に師と思っている、いつも隣にITのお仕事のタカハシノリアキ先生の記事を参考にVBAを作成。

A.クラス・コレクションを使ったデータ流し込み

①クラスdataで構造体的なものを作る

②クラスdatainputでデータをコレクションに入れ込むメソッド、および書き出しシートに書き出すメソッドを作成

③標準モジュールでクラスdatainputを動かすコードを作成

Aの場合、流し込みの際に書き出しシートにあり、もとデータにないkeyがあるとエラーになったので、エラーを無視する記述を追加した。

なんかかっこ悪い。なので、クラスでもディクショナリが利用できるかしらと試してみた。

Ｂ.クラス・ディクショナリを使ったデータ流し込み

①クラスdataで構造体的なものを作る（Aと同じ）

②クラスdatainputDICでデータをdictionaryに入れ込むメソッド、および書き出しシートに書き出すメソッドを作成。書き出しの際にdic.existsでkeyの有無を検査、あれば書き出し処理をする。

③標準モジュールでクラスdatainputDICを動かすコードを作成

１０万件のデータでやってみた。

	時刻	所要時間
クラスコレクション利用開始	15:14:15
クラスコレクション利用storeData終了	15:14:18	0:00:03
クラスコレクション利用終了	15:14:37	0:00:19
クラスDIC利用開始	15:15:25
クラスDIC利用storeData終了	15:15:27	0:00:02
クラスDIC利用終了	15:15:46	0:00:19

ほとんど変わらない～。

Vlookupも会社のパソコンに比べて早い。自宅のパソコンはデスクトップパソコンのWin10。会社のはWin8のノートパソコン。会社のやつで10万件のVLookupやったらトイレに行って戻ってきたころやっと終わっている感じ。Win10って早いのかしら。（IT知識低めですみません）

コードです。

Bのディクショナリ利用ではkey値重複チェックなども入れています。

◆クラスdata(Ａ／Ｂ共通)　

Public no As String
Public ken1 As Long
Public ken2 As Long
Public ken3 As Long

Sub setdata(ByVal rng As Range)
    no = rng(1).Value
    ken1 = rng(3).Value
    ken2 = rng(4).Value
    ken3 = rng(5).Value
End Sub

■■■■■　Aのコード：クラスdataをコレクションに入れる　■■■■■

◆クラスdatainput

Private strData As Collection ‘コレクションはクラス内変数にする

Sub storeData(ByVal ws_ As Worksheet, ByVal rowStr As Long)
    ‘データをコレクションに追加
    Set strData = New Collection
    Dim i As Long, rowEnd As Long
    ‘最終行取得
    With ws_
        rowEnd = .Cells(.Rows.Count, 1).End(xlUp).row
    End With
For i = rowStr To rowEnd
        Dim d As data, rng As Range
        Set d = New data                ‘クラスdata実体化
        With ws_
            Set rng = .Range(.Cells(i, 1), .Cells(i, 5))        ‘該当行のA列からE列をrngにセット
        End With
        d.setdata rng                   ‘クラスdataのsetedataメソッド起動
        strData.Add d, d.no ‘コレクションstrDataにクラスdataを追加、keyをnoとする
        Set d = Nothing ‘クラスdataのインスタンスの破棄
    Next i
End Sub

Sub outputData(ByVal ws_ As Worksheet, ByVal rowStr As Long, ByVal colKey As Long, ByVal colStr As Long)
Dim i As Long, rowEnd As Long, vkey As String

    ‘key値がコレクションにないときエラーになるので、エラーを無視する処理
    On Error Resume Next
    With ws_
        rowEnd = .Cells(.Rows.Count, 1).End(xlUp).row       ‘最終行取得
        For i = rowStr To rowEnd
            ‘key値取得
            vkey = .Cells(i, colKey).Value
            ‘key値に対応するコレクション内データをセルに入力する
            .Cells(i, colStr).Value = strData(vkey).ken1
            .Cells(i, colStr + 1).Value = strData(vkey).ken2
            .Cells(i, colStr + 2).Value = strData(vkey).ken3
        Next i
    End With
End Sub

◆標準モジュール

Sub クラスコレクション利用()

    Debug.Print “クラスコレクション利用開始　” & Time　　　　　’時間計測用
    ‘クラスdatainputを使えるようにする
    Dim di As datainput
    Set di = New datainput
    ‘「もとデータ」シートのデータ代入処理
    Dim wsMotoData As Worksheet: Set wsMotoData = Worksheets(“もとデータ”)
    Dim rowStr As Long: rowStr = 2      ‘データ開始行
    ‘クラスdatainputのメソッドstoreData起動(引数は対象シート、データ開始行)
    di.storeData wsMotoData, rowStr
    Debug.Print “クラスコレクション利用storeData終了　” & Time　　‘時間計測用
    ‘「入れるシート」にA列Keyに対応するデータを入れる
    Dim wsData As Worksheet: Set wsData = Worksheets(“入れるシート”)
    Dim rowStrD As Long: rowStrD = 2        ‘データ開始行
    Dim colKey As Long: colKey = 1          ‘Keyのある列番号(A列）
    Dim colStr As Long: colStr = 3          ‘入力スタート列の列番号(C列）
    ‘クラスdatainputのメソッドoutoputData起動
　'(引数は対象シート、データ開始行、Kye列、データ入力開始列）
    di.outputData wsData, rowStrD, colKey, colStr
    Set di = Nothing
    Debug.Print “クラスコレクション利用終了　”& Time　　　　　’時間計測用
End Sub

■■■■■　Bのコード：クラスdataをディクショナリに入れる　■■■■■

◆クラスdatainputDIC（ディクショナリ利用のためScriptingRuntimeを参照設定済）

Public dic As Dictionary

Function storeDataDIC(ByVal ws_ As Worksheet, ByVal rowStr As Long) As String
Set dic = New Dictionary
Dim i As Long, rowEnd As Long
    With ws_
        rowEnd = .Cells(.Rows.Count, 1).End(xlUp).row
    End With
    For i = rowStr To rowEnd
        Dim d As data, rng As Range
        Set d = New data
        With ws_
            Set rng = .Range(.Cells(i, 1), .Cells(i, 5))
        End With
        d.setdata rng
　　’key値重複の場合は注意メッセージを出して処理終了とする

　　If dic.Exists(d.no) Then
storeDataDIC = “「もとデータ」シートKeyに重複有” & vbCrLf & “処理を中止します”
Set d = Nothing

　　　 Exit Function
        Else
            dic.Add d.no, d          ‘dicにクラスdataを追加、keyをnoとする
        End If
        Set d = Nothing
    Next i
End Function

Sub outputDataDIC(ByVal ws_ As Worksheet, ByVal rowStr As Long, ByVal colKey As Long, ByVal colStr As Long)
    Dim i As Long, rowEnd As Long, vkey As String
    With ws_
        rowEnd = .Cells(.Rows.Count, 1).End(xlUp).row
        For i = rowStr To rowEnd
            ‘「いれるシート」のA列のkey値を変数vkeyに代入

vkey = .Cells(i, colKey).Value
            If dic.Exists(vkey) Then            ‘dicにkey値があれば入力処理
                .Cells(i, colStr).Value = dic(vkey).ken1
                .Cells(i, colStr + 1).Value = dic(vkey).ken2
                .Cells(i, colStr + 2).Value = dic(vkey).ken3
            End If
        Next i
    End With
End Sub

◆標準モジュール

Sub クラスDIC利用()

    Debug.Print “クラスDIC利用開始　” & Time　　　　　’時間計測用
    ‘クラスdatainputを使えるようにする
    Dim di As datainputDIC
    Set di = New datainputDIC
    ‘「もとデータ」シートのデータ代入処理
    Dim wsMotoData As Worksheet: Set wsMotoData = Worksheets(“もとデータ”)
    Dim rowStr As Long: rowStr = 2      ‘データ開始行
    ‘クラスdatainputのメソッドstoreData起動(引数は対象シート、データ開始行)
    Dim msg As String
    msg = di.storeDataDIC(wsMotoData, rowStr)
    If msg <> “” Then       ‘戻り値にメッセージがあれば処理中断
        MsgBox msg
        Set di = Nothing
        Exit Sub
    End If
    Debug.Print “クラスDIC利用storeData終了　” & Time　　　　　’時間計測用

    ‘「入れるシート」にA列Keyに対応するデータを入れる
    Dim wsData As Worksheet: Set wsData = Worksheets(“入れるシート”)
    Dim rowStrD As Long: rowStrD = 2        ‘データ開始行
    Dim colKey As Long: colKey = 1          ‘Keyのある列番号(A列）
    Dim colStr As Long: colStr = 3 ‘入力スタート列の列番号(C列）
‘クラスdatainputのメソッドoutoputData起動(引数は対象シート、データ開始行)、Kye列、データ入力開始列）
    di.outputDataDIC wsData, rowStrD, colKey, colStr
    Set di = Nothing
    Debug.Print “クラスDIC利用終了　” & Time　　　　　’時間計測用
End Sub