セル範囲データを1次元配列化する関数

コードコピー

Function zzzセル範囲1次元配列化(ByVal zzhセル範囲 As Range, Optional zzhText取得 As Boolean = False) As Variant
    Dim zz動的配列() As Variant, zzセル_Loop As Range, zz配列要素数 As Long
    For Each zzセル_Loop In zzhセル範囲
        If zzセル_Loop <> "" Then
            ReDim Preserve zz動的配列(zz配列要素数)
            If zzhText取得 = False Then
                zz動的配列(zz配列要素数) = zzセル_Loop.Value
            Else
                zz動的配列(zz配列要素数) = zzセル_Loop.Text
            End If
            zz配列要素数 = zz配列要素数 + 1
        Else: End If
    Next zzセル_Loop
    zz配列要素数 = 0
    zzzセル範囲1次元配列化 = zz動的配列
End Function

引数に指定したセル範囲のデータを1次元配列に取得させる関数です

通常、セル範囲を配列に取得させると2次元配列になります
これは1列や1行しか指定しなかったとしても、必ず2次元配列になります
なぜならワークシートが2次元のデータだからです

この画像は、配列にセル範囲をそのまま代入させた場合の配列データです
左のウィンドウにあるのが配列データです
+の記号を展開してもデータは1つしかありませんが、展開をしないとデータが見れません
これが2次元配列になっている状態である、ということです

この配列データでは、セル範囲データをリスト化などする際にデータの参照が少し煩雑になるし、この配列データに行データが1次元目なので、Preserveキーワードを使用して行方向にデータを追加することが出来ません

そんな色々と制約のある多次元配列では困ってしまう場合に使用するのがこの関数です

「zzhセル範囲」は、配列データとするセル範囲の指定です
ここに指定したセル範囲が対象となります、セル範囲なので複数列や複数行もどちらも含むような範囲指定でも問題はありません

「zzhText取得」は、セルデータを値として取得するか、文字列として取得するかの指定になります
というのも、セルは表示形式という設定があり、値ではなく表示された見えている状態のデータを取得したい場合があります
その際に使用する設定です、ここにTrueを指定すると文字列として取得します
省略可能で、省略した場合はFalseが指定され値として取得を行います

コードの使用方法

コードコピー

Dim zz1次元 As Variant
zz1次元 = zzzセル範囲1次元配列化(Selection)

この関数が配列を返す関数なので、代入先はValiant型の変数を指定します
1行目の変数宣言の部分は、その変数を宣言しております

2行目で関数を使用して選択範囲を1次元配列化しています

画像の左側のウィンドウの配列データを上の画像と比べてみてもらうと分かりますが、+記号が無く、全てのデータが確認することが出来ることが分かると思います

なお、日付データの場合VBA上では画像の様に「#」で囲まれた状態になります
これが日付データであるという表現方法になります
また、3列目にDate型になっていることからも日付データとして取得できていることが確認できます

なお、この関数はデータをリスト化するなどの目的に使用することを前提としているため、空白のセルは無視します
画像では24行目まで選択されていますが、配列データが22番までとなっています
配列は0番から始まっているのでデータの総数は23個ということになります

選択範囲のセル数より少ない要素数になっているのはこのためです
これ以降にデータが存在しなければ、たとえA列全てを選択して100万セルを指定したとしても配列の要素数は23個というのが変わりません

コードコピー

Dim zz1次元 As Variant
zz1次元 = zzzセル範囲1次元配列化(Selection, True)

次に引数2つ目のzzhText取得にTrueを指定して処理を実行してみます

画像の左側のウィンドウの配列データを確認してください
指定セル範囲は、上記のものと全く同じです
配列の個数も23個というのは同じです

ですが、取得しているデータには「#」が付いていません
代わりに「”」で囲まれています、これにより文字列で取得されていることが確認できます
同じように3列目を確認してみると、String型になっていることからも文字列として取得されていることが分かります

この文字列取得は、単純にデータを数値ではなく文字列で取得する
ということではなく、VBAのRangeオブジェクトのTextプロパティは表示された内容を文字列で取得する動きになります

例えば、金額の表示形式を設定すると「,」が3桁ごとに入力されます
「1500」は「1,500」という表示形式になります、表示形式なのでValueプロパティは「1500」を取得しますが、Textプロパティでは「1,500」という文字列を取得します

案外この表示形式後のデータで取得したい場合はあるので、その場合は引数2つ目をTrueに指定して関数を実行してください

コード解説

Function zzzセル範囲1次元配列化(ByVal zzhセル範囲 As Range, Optional zzhText取得 As Boolean = False) As Variant

～～　中略　～～

End Function

Functionプロシージャの処理範囲の始まりと終わりのコードです
Functionプロシージャは戻り値を持つプロシージャで、今回は配列データを返しますので、プロシージャの型はValiant型で宣言しています

この関数では、すでに解説したように2つの引数が指定できます
引数の内容に関してはすでに解説しましたので、割愛します
キーワードの解説のみ行います

1つ目の引数はByValキーワードが設定されています
これは引数の変数自体を値として受け取ることを意味しています
なので、このプロシージャ中でこの引数名の変数を変更しても呼び出し元に影響を与えない形になります、今回変更はしていないので省略しても構いません
また、セル範囲を指定してもらいたいので型はRange型になっています

2つ目の引数はOptionalキーワードが設定されています
これは引数が省略可能なことを意味しています
省略した場合は、Falseが指定されます
TrueとFalseの違いによる処理の分岐は後述します
2択の選択肢になるのでBoolean型になっています

    Dim zz動的配列() As Variant, zzセル_Loop As Range, zz配列要素数 As Long

使用する変数の宣言です
使用する変数は合計3つになります

「zz動的配列() As Variant」は、作成する配列データです
指定されたセル範囲にあるデータの個数で要素数が変化するので動的配列としています
またセル自体が何でも代入できるものなので、変数の型はValiant型で宣言します

「zzセル_Loop As Range」は、指定されたセル範囲をForループで使用するためのRange型の変数です

「zz配列要素数 As Long」は、動的配列の要素数を再定義する際に使用する整数です
この数字を1ずつ加算して要素数を増やしていきます

    For Each zzセル_Loop In zzhセル範囲

～～　中略　～～

    Next zzセル_Loop

引数に指定されたセル範囲を1セルずつループして検証を行います
全てのセルを参照するので、セルの個数が増えればそれだけ処理時間が伸びるので注意が必要ですが、そんなに大量なリストを作成する前提は考えていなので適宜対応を考えましょう

        If zzセル_Loop <> "" Then

～～　中略　～～

        Else: End If

この関数では空白はセル範囲に指定されていたとしても取得をしないようにしています
それがここのIf分岐になります
セルのデータが空白でなければデータを取得します

なお、関数などで空白が返されていた場合も取得は行いません
当然ですがデータリストに関数を取得させる必要が無いからです
関数は引数があって初めてデータとして成立するため、その関数構文を取得することに意味がありません

            ReDim Preserve zz動的配列(zz配列要素数)

動的配列の要素数の再定義です
取得するデータは空白を無視する仕様から、実際に全てセルの検証を終了してからしか要素数が分かりません

その為、動的配列でかつ都度再定義が必要になります
それをここで行っているわけです

「ReDim Preserve」は要素数の再定義を行い、その際取得済みのデータを保持するコードになります
要素数には整数値を指定しますが、Long型の変数を使用しています
Long型は初期値が0なので、そのまま変数を利用できます

            If zzhText取得 = False Then
                zz動的配列(zz配列要素数) = zzセル_Loop.Value
            Else
                zz動的配列(zz配列要素数) = zzセル_Loop.Text
            End If

ここが実際のデータの配列への取得箇所です
前提として、取得にはセルの値か表示形式の適用された見えているデータかを選択できるようにしていました
なので、ここでその選択によってIf分岐を使用して取得内容を変更しています

zzhText取得が省略されたり、Falseが指定されていれば値の取得を行います
なので、RangeオブジェクトのValueプロパティで取得します

逆にTrueが指定されていれば、RangeオブジェクトのTextプロパティを取得します
なお、このプロパティによって取得されるものは必ず文字列となります
数値であっても文字列になるのですが、その目的のための設定なので特にその動きで問題は無いはずです

また、この設定を混在させたいような複雑な処理は汎用化に向いていませんので、この処理を改造して作成してみてください

            zz配列要素数 = zz配列要素数 + 1

ここで要素数の再定義用のLong型変数を更新しています
配列データなので普通に1を加算しているだけです

    zz配列要素数 = 0
    zzzセル範囲1次元配列化 = zz動的配列

ループが終了したら、要素数再定義用変数は役割を終えているので初期値に戻しておきます
この後使うわけでもないので、無くてもいいです

そしてこの処理はFunctionプロシージャなので最後にプロシージャに作成した配列データを取得させます
これで、この関数の戻り値が確定したことになります

この後に使用した配列も初期化してもいいですね、しなくてもいいですけど
なお、Range型の変数はForループ終了時点で初期化されています
これはForループの仕様です

途中でも少し触れましたが、この関数はビッグデータを想定していません
せいぜい数千セル程度までを想定しています
1列全ての100万セルなら明らかに待ちます
さらに言うなら要素数の定義用変数がLong型なので21億程の範囲を超えるセル数ならエラー発生です

そんな意地悪なセル範囲は指定しないでね