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

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

もし、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つのセルの場合でも、二次元配列で返すように工夫しています。

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

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

《VBA初心者におすすめの本》
VBA初心者には、株式会社すごい改善さんの本が分かりやすくておすすめです。

created by Rinker
技術評論社
¥2,178 (2021/12/01 14:22:42時点 Amazon調べ-詳細)

動画で学びたい人には、すごい改善さんのUdemy講座をおすすめします。
【累計36万部著者が教える】たった1日で!まったくの初心者でも最短でExcel VBAを仕事で活用できるようになる講座

他の「VBA」の記事はこちらからどうぞ

VBA

Posted by やろまい