[Excel VBA] データをまとめて入れておけるArraylist | VBA プログラミングで社内アプリ開発

今回はちょっと高度な技だけど、使いこなせるととても便利な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を選択してね。