エクセルがともだち

会社で一番のともだちがエクセル・・・のアラフィフパート事務員のつれづれ帳。備忘録もかねてエクセルのことやVBAのことを書いていくつもりです。

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

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

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

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

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

 

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

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

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

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

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

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

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

 

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

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

 

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

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

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

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

 

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

  時刻 所要時間
クラスコレクション利用開始 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(A/B共通) 
 
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