【VBA】セルの値を取得して二次元配列へと入れる方法(セルが1つの場合にも対応)

2021年10月19日2024年9月8日

シート上のデータを取得する時、どのような方法を使っていますか？

もし、For文を使って1個1個取得しているなら、この記事を読んでみてください

Forでネストされた読み辛いコードを書かなくても、二次元配列で一気にデータを取得することができます。

セルの値を取得して二次元配列へと入れる方法

例えばこのようなデータがある時、For文でデータを取得すると次のようなコードになります。
（二次元配列のarrにシート上のデータを格納しています）

Sub GetCellData()
'For文でデータを取得

    Dim lastRow As Long
    Dim lastCol As Long
    lastRow = Cells(Rows.count, 1).End(xlUp).row '最終行の取得
    lastCol = Cells(1, Columns.count).End(xlToLeft).Column '最終列の取得

    Dim arr()
    ReDim arr(1 To lastRow, 1 To lastCol)

    Dim row_i As Long, col_i As Long
    For row_i = 1 To lastRow
        For col_i = 1 To lastCol
            arr(row_i, col_i) = Cells(row_i, col_i)
        Next
    Next

End Sub

For文で二重にネストされているので、読みにくいし長いですよね

この長いコードが二次元配列へ一気に格納する方法を使えば、次のように簡単になります。

Sub GetCellData()
'二次元配列へ一気に格納

    Dim lastRow As Long
    Dim lastCol As Long
    lastRow = Cells(Rows.count, 1).End(xlUp).row '最終行の取得
    lastCol = Cells(1, Columns.count).End(xlToLeft).Column '最終列の取得

    Dim arr
    arr = Cells(1, 1).Resize(lastRow, lastCol)

End Sub

Variant型変数=Rangeオブジェクトとすると、セルの値を一気に配列の形で取得することができます。（このコードでは、下記の部分です。）

arr = Cells(1, 1).Resize(lastRow, lastCol)

Variant型変数であるarrにCells(1, 1).Resize(lastRow, lastCol)の値を取得しています。

実行してみるとarrが二次元配列となっていることが分かります。

注意：セルが1つだと二次元配列でなく値になる

このやり方をする時、注意して欲しいのはセルが1つだけの時にはarrが二次元配列とならないことです。

先ほどの例では列が複数あるので1つだけにはなりませんが、例えば名前だけ取得するコードにすると1つになる可能性が出てきます。(値を取得する列をB列に限定しています)

Sub GetCellData()
'二次元配列へ一気に格納

    Dim lastRow As Long
    lastRow = Cells(Rows.count, 1).End(xlUp).row '最終行の取得

    Dim arr
    arr = Cells(1, 2).Resize(lastRow)

End Sub

上の画像のように名前が空欄の状態でこのマクロを実行すると、Cells(1, 2).Resize(lastRow)の部分はB1セルだけになります。

この場合、arrは二次元配列でなく、1つの値となります。

値を取得した後に、UBound(arr)などを行うとエラーとなるため注意してください。

自作関数を使えばセルが1つの場合にも対応できる

セルが1つの場合でもエラーにしないためには、自作関数を使うのがオススメです。

私は次のような自作関数GetArrFromRangeを使っています。

Sub test()
'テストコード
    Dim arr As Variant
    arr = GetArrFromRange(Cells(1, 1))
End Sub

Function GetArrFromRange(rng As Range) As Variant
'Rangeオブジェクトを受け取り、二次元配列で返す

    Dim oneArr(1 To 1, 1 To 1)
    If rng.count = 1 Then
        oneArr(1, 1) = rng.Value
        GetArrFromRange = oneArr
    Else: GetArrFromRange = rng.Value
    End If

End Function

この自作関数は値を取得したいRangeオブジェクトを引数として受け取り、二次元配列として返します。

If文を用いてRangeオブジェクトが1つのセルの場合でも、二次元配列で返すように工夫しています。

この関数をコピペしてプロジェクト内に置いておいて、セルの値を取得したいところでテストコードのように使用してみてください。

《配列から値を取り出す方法はこちらをご覧ください》