[Excel VBA] データをまとめて入れておけるArraylist

今回はちょっと高度な技だけど、使いこなせるととても便利なArraylistというものを教えるよ。

例えば10列あるデータを取得して、別のシートに加工して貼り付けるとしたら、その10列分の変数を用意して、1つの列ごとに値を代入してそれをさらに貼り付ける時にセルごとに値を渡していったら大変だし、コードしては見づらいものになるよね。

そんな時にはデータをまとめて入れておける配列変数というものがあって、Excel VBAの中で使いやすい配列変数の1つにArraylistというものがあります。

今回のプログラム内で行っていることは、商品コード、数量、備考1、 備考2、 備考3という項目があって、数量分、それぞれ行を分けて別シートに記載していくというもの。例えば商品コードSHORO-Aで数量が3の場合、別シートに商品コードSHIRO-Aで数量1の行を3行記入するという感じだよ。

実際に使うかどうかはその業務次第だけど、これはArraylistの感じを掴むためのサンプルと思ってね。

それではどんな感じかまずはコードを張り付けて実行してみましょう。

Option Explicit

'数量の列
Public Const QtyCol As Integer = 2

Sub LineSplit()

    Dim DataList As Object
    Dim RowCount As Integer
    Dim WriteCnt As Integer
    Dim WriteRowCount As Integer
    Dim ColCount As Integer
    Set DataList = CreateObject("System.Collections.ArrayList")
    
    RowCount = 2
    WriteRowCount = 2

    '分割結果シートクリア
    Call ResultSheetClear

    Do Until Sheets("データ元").Cells(RowCount, 1) = ""
    
        DataList.Clear
    
        '5列分をArrayListに登録
        For ColCount = 1 To 5
            DataList.Add Sheets("データ元").Cells(RowCount, ColCount)
        Next
        
        'データ元シートに記載された数量分書き込み
        For WriteCnt = 1 To Sheets("データ元").Cells(RowCount, QtyCol)
        
            Call WriteValToSheet(DataList, WriteRowCount)
            WriteRowCount = WriteRowCount + 1
        
        Next
        
        RowCount = RowCount + 1
    Loop

End Sub

Sub WriteValToSheet(DataList, WriteRowCnt)

    Dim ColCount As Integer
    
    For ColCount = 1 To 5
    
        '書き込む列が数量列と同じ場所の場合は1を書き込む
        If ColCount = QtyCol Then
            Sheets("分割結果").Cells(WriteRowCnt, ColCount) = 1
        Else
            Sheets("分割結果").Cells(WriteRowCnt, ColCount) = DataList(ColCount - 1)
        End If
    Next

End Sub

'分割結果シートクリア
Sub ResultSheetClear()

    Sheets("分割結果").Range("A2:E100000") = ""

End Sub

Excel データ元のシート – シート名は「データ元」

ボタンを追加したら、マクロの登録でLineSplitを選択してね。

Excel データ出力先のシート - シート名は「分割結果」

このソースコードでは、DataListという名前でArrayListを定義しているよ。

ある行の5列分の情報をまとめてDataListに追加して、そのまWriteValToSheetというサブプロシージャへ渡しているよ。

値を取り出すときは、DataList(x)のように登録した順番で0からの数値で指定するようにしているよ。この場合、1列目の情報はDataList(0)に入っていることになるよ。

今回のように1度に取得したい情報が少ない場合は、列分全てをそれぞれ変数にしてそこに代入してもいいと思うけど、例えばこれが10列以上の情報だったりすると、全てに変数を用意するとコードが見づらくなるので、そんなときにはArrayListがおススメです。

コードはとにかくシンプルで分かりやすくね。

今回の内容はものすごく簡単。 ループ処理が長すぎたり、間違って無限ループに陥って固まってしまったら、Ctrl + Pauseを押すと...