このサイトの記事内では「アフィリエイト広告」などの広告を掲載している場合があります。
消費者庁が問題としている「誇大な宣伝や表現」とならないよう配慮しコンテンツを制作しておりますのでご安心ください。
問題のある表現が見つかりましたらお問い合わせよりご一報いただけますと幸いです。

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

2024年9月8日

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

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

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

VBAが難しいと感じたら
ココナラにてVBAの作成依頼を受け付けています。
ご依頼・ご相談はこちらから

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

例えばこのようなデータがある時、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上級者を目指したい人にはパーフェクトExcel VBA一択です。
この本を読み切れば間違いなくVBA上級者になれます。

created by Rinker
技術評論社
¥3,608 (2024/12/02 20:25:22時点 Amazon調べ-詳細)
VBAが難しいと感じたら
ココナラにてVBAの作成依頼を受け付けています。
ご依頼・ご相談はこちらから

VBA

Posted by やろまい