【1行で書ける】Excel VBAでデータを配列に入れる2つの方法

VBAでデータを配列として取得する

今日はExcel VBAでデータを配列に入れる方法を教えるよ。
今回は少し上級者の内容だね!頑張って覚えます!
こんな方におすすめ
  • Excel VBAでデータ分析をしている方
  • 配列などのプログラミングの基礎を理解している方
目次目次[閉じる]

今回使うデータ

今回使うデータは以下のような気温のデータをVBAで配列に入れる方法をお伝えします。 このデータがあるシートの名前は「data」としています。

データのサンプル(気象データ)

また、説明をわかりやすくするため、データの個数は少なめにしています。

"CurrentRegion"でデータを取得する

この方法は以下のようなケースで有効的です。

  • 1つのシート内にリストデータが1つしかない。
  • とりあず手取り早くデータを配列化したい。

以下のコードでデータを配列(arr)内に入れることができます。

    Dim arr As Variant
    arr = ThisWorkbook.Sheets("data").Range("A1").CurrentRegion

1行目でデータを入れる配列である「arr」を定義しています。定義型はVariant型にしてください。

2行目のたった1行でデータを配列に入れてます。簡単ですね。

少しプログラムの解説をします。

まず、ThisWorkbook.Sheets("data")でブックとシートの指定をしています。

今開いているこのブック(ThisWorkbook)の「data」という名前のシート (sheets("data"))について、という意味です。

次に、Range("A1").CurrentRegionの意味ですが、A1セルを選択中に「CTRL + A」で全選択(Macはcommand + A)をしたときの範囲を表しています。

まとめると、「このブックの「data」というシートのA1セルを選択中に全選択した範囲をarrという配列に入れる」になります。

試しにDebug.Printで取得したデータを確認してみましょう。以下のコードでループさせながら書き出していきます。

    For Each Value In data
        Debug.Print Value
    Next

結果は以下の通りです。確かにきちんとデータが取得できていますね。

CurrentRegionで取得したデータのアウトプット

テーブルからデータを取得する

こんなときにおすすめ

  • 取り出したいデータ(テーブル)が1シートに複数存在する
  • データの中身だけを取り出したい
  • 特定のカラム(列)だけ配列として取り出したい

テーブルの全データの取得方法

テーブルのデータは以下のコードで配列として取得できます。

    Dim arr As Variant
    arr = ThisWorkbook.Sheets("data").ListObjects("temp_data").DataBodyRange

こちらも簡単にコードを紹介します。

1行目と2行目のarr = ThisWorkbook.Sheets("data")までは先ほどと同じですね。

テーブルの場合、.ListObjects("temp_data")を使ってテーブルを指定しています。

今回の気象データのテーブルは「temp_data」という名前をつけています。

テーブルに名前をつける

そして、.DataBodyRangeでテーブル内のデータを取得しています。

実際にデータを取得できているかDebug.Printで確認してみましょう。

テーブルから取得したデータのアウトプット

おや、「CurrentRegion」を使ったとは出力結果が違いますね。カラム名が取得されていません。

そうです。この方法では1行目のカラム名を無視したデータの中身だけが配列として取得されるので注意しましょう。

テーブルの特定のカラム(列)だけ取得する方法

テーブルからデータを取得するメリットとして、特定のカラム(列)だけ配列として取得することができます

それでは例として平均気温のデータだけを配列として取り出してみましょう。

以下のコードで特定のカラムだけ取り出すことができます。

    Dim arr As Variant
    arr = ThisWorkbook.Sheets("data").ListObjects("temp_data").ListColumns("平均気温(℃)").DataBodyRange

結果を見てみましょう。

テーブルのカラムから取り出したデータのアウトプット

このコードでは新たに.ListColumns("平均気温(℃)")が追加されてます。

これによって指定したテーブルのどのカラム(列)かを指定して.DataBodyRangeでデータを取得しています。

カラム名を指定して取得するメリットは、取りしたいカラム(列)が何番目かを意識しなくていいということです。

また、カラム名ではなく、.ListColumns(1)のように列番号を指定してその列を取り出すことも可能です。

以上、いかがでしたでしょうか。

Excelで配列を自由に操作できるようになるとできることの幅が広がります。

ぜひ配列操作ぜひお試しください。