VBAでこのオブジェクトを使えば、配列より簡単にデータ管理できると言われても、シートに書き出せばいいだけなんだが ー Collectionオブジェクト(VBA)

コレクションを回した結果

　　　　

今まで、VBAの手法の中では、今一使い道の分からないのがコレクションです。

同種のオブジェクトを複数まとめたものと解説されても、さっぱり分からんわ。

色々とネットで探してみても、ただ、VBAのコードのサンプルを提示するだけ。

別に、コードを書いてメモリーの中に配列をため込んでも、あまり意味がない。

シートに書き出せば、その羅列が理路整然と一目稜線に見え分かるだけなんだ。

なので、使い道を探す必要がありますが、その一つは重複しないリストの作成。

Sub 重複しないリスト作成()　’A列目が元データ

    Dim Collctn As New Collection, i As Long

    On Error Resume Next

    For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row

        Collctn.Add Cells(i, 1), Cells(i, 1)

    Next i

    On Error GoTo 0

    For i = 1 To A.Count　’B列目に重複しないデータ列挙

        Cells(i + 1, 2) = A(i)

    Next i

End Sub

これは、一列目のデータ範囲をループさせ、重複のないリストをメモリー格納。

この結果を可視化できるように、二列目に書き出すという簡単なコードなんだ。

コレクションは、”キー”と”値”をペアで管理しますが、キーは重複できません。

例えば、キーに値を割り振ると、既に割り振られた値になればエラーになるの。

その時、エラー発生は無視して処理を続け、エラーの無効化でループを続ける。

この処理に、On Error Resume Next、On Error GoTo 0、を使っていますな。

まあ、これが具体的な使い道だと思いますが、自分の仕事でも使ってみました。

それは、会社の電算処理で使われる注文番号して、何やら暗号みたいなコード。

例えば、”EV03P8-01”のように六桁番号の後ろにハイフンで枝番が付けてある。

一回の注文で複数の製品が注文されるわけで、その場合は連番を割り振ります。

そして、枝番の割り振った製品が複数台なら、電算処理は同一回数の登録です。

つまり、”EV03P8-01”の受注製品が三台の注文ならば、枝番付きで三回登録だ。

こうなると、親番の注文番号を重複させず、枝番の複数台注文も重複させない。

つまり、要素だけを抽出したら、枝番の登場回数（台数）もカウントさせたい。

ただ、同じデータ範囲で別々のプロシージャでコレクションにすると回せない。

コードを実行したら、作成済みとエラーが出てしまって、少し工夫が必要だな。

なので、プロシージャを一つにまとめてコードを書いたら、あら、動きました。

後は、枝番の登場回数は、シート関数の”CountIf”で、サクッとまとめて終了だ。

というわけで、コレクション二回を回してみた、実用的なコードの紹介でした。

ネットで紹介されるコードは、分けの分からない、全く実用性を見出せない解説ばかりで、これだったら、多少なりともコレクションの有用性を分かってもらえると思ったのでした。

※サンプルコード

Sub PO_Info() 'A列の重複しない要素の取得・各要素の重複数取得

Dim i As Integer, j As Integer

Dim A As Collection, B As New Collection

  Set A = New Collection

    Set B = New Collection

      On Error Resume Next 'エラーが出ても次行から再開

For i = 1 To Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

  A.Add Left(Worksheets("Sheet1").Cells(i, 1), 6), Left(Worksheets("Sheet1").Cells(i, 1), 6) 'A列:親注文番号のみコレクション

  B.Add Worksheets("Sheet1").Cells(i, 1), Worksheets("Sheet1").Cells(i, 1)

Next i     'A列:枝番付き注文番号でコレクション

  On Error GoTo 0  'わざとエラーで無視させる設定

    For i = 1 To A.Count  'A列：親注文番号のコレクション結果をB列に列挙

      Worksheets("Sheet1").Range("B" & i) = A(i)

    Next i

  For i = 1 To B.Count  'A列:枝番付き注文番号のコレクションをC列に列挙

    Worksheets("Sheet1").Range("C" & i) = B(i)

  Next i

For i = 1 To Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row

 'C列の要素から、C列の同一要素個数を取得

  j = WorksheetFunction.CountIf(Worksheets("Sheet1").Range("A:A"),   Worksheets("Sheet1").Range("C" & i).Value)

  Worksheets("Sheet1").Range("D" & i).Value = j   '要素の重複数をQ列に列挙

Next i

  Set B = Nothing

  Set A = Nothing

End Sub

いいねと思ったら、三つポチっとね！
  
にほんブログ村    にほんブログ村      にほんブログ村