VBA – RangeのValueを一次元配列に変換する効果的な方法

Excel VBAを使用して大量のデータを処理する際、セル範囲のデータを一次元配列に変換することは一般的です。この記事では、複数のセル範囲からなるデータを一次元配列に変換する効果的で柔軟なVBAコードに焦点を当てます。これにより、データ処理や分析の際により簡潔なコードを記述できます。

実装

Sub test()
    Dim VV As Variant
    Dim V As Variant
    
    ' 複数のセル範囲から一次元配列に変換
    VV = RangeToAry(Selection, Range("e3:e4"), Range("c18,d19,g4"))
    
    ' 変換された一次元配列の要素を表示
    For Each V In VV
        Debug.Print V
    Next
End Sub

' RangeのValueを一次元配列に変換
Function RangeToAry(Rng1 As Range, ParamArray RngOther()) As Variant
    Dim RR As Range
    Dim Ary() As Variant
    Dim C As Long
    Dim i As Long
    
    ' 各セル範囲からデータを抽出し、一次元配列に追加
    RangeToArySub ToUsedRange(Rng1), Ary, C
    
    For i = LBound(RngOther) To UBound(RngOther)
        RangeToArySub ToUsedRange(RngOther(i)), Ary, C
    Next
    
    ' 最終的な一次元配列を返す
    RangeToAry = Ary
End Function

' 行または列全体の時は使用範囲内に絞る
Private Function ToUsedRange(ByVal RR As Range) As Range
    If RR.Rows.Count = RR.Worksheet.Rows.Count Then
        Set RR = Intersect(RR, RR.Worksheet.UsedRange)
    End If
    If RR.Columns.Count = RR.Worksheet.Columns.Count Then
        Set RR = Intersect(RR, RR.Worksheet.UsedRange)
    End If
    Set ToUsedRange = RR
End Function

Private Sub RangeToArySub(ByVal Rng As Range, ByRef Ary() As Variant, ByRef C As Long)
    Dim Are As Range
    Dim VV As Variant
    Dim V As Variant
    Dim N As Long
    
    ' 各エリアからデータを抽出し、一次元配列に追加
    For Each Are In Rng.Areas
        N = Are.Cells.Count
        ReDim Preserve Ary(1 To C + N)
        
        If N = 1 Then
            C = C + 1
            Ary(C) = Are.Value
        Else
            VV = Are.Value
            For Each V In VV
                C = C + 1
                Ary(C) = V
            Next
        End If
    Next
End Sub

test サブルーチン: テスト用のコード。複数のセル範囲から一次元配列に変換し、その結果をデバッグウィンドウに表示します。
RangeToAry 関数: メインの関数。引数として与えられたセル範囲を一次元配列に変換します。
ToUsedRange 関数: 行または列全体の場合、使用範囲内に絞り込むための関数。
RangeToArySub サブルーチン: 各セル範囲またはエリアからデータを抽出し、一次元配列に追加するためのサブルーチン。

解説

Sub test()
    Dim VV As Variant
    Dim V As Variant
    
    ' 複数のセル範囲から一次元配列に変換
    VV = RangeToAry(Selection, Range("e3:e4"), Range("c18,d19,g4"))
    
    ' 変換された一次元配列の要素を表示
    For Each V In VV
        Debug.Print V
    Next
End Sub

VV = RangeToAry(Selection, Range("e3:e4"), Range("c18,d19,g4")): RangeToAry 関数を使用して、複数のセル範囲からデータを一次元配列に変換します。
For Each V In VV: 変換された一次元配列の各要素を取得し、Debug.Print を使用してデバッグウィンドウに表示します。

Function RangeToAry(Rng1 As Range, ParamArray RngOther()) As Variant
    Dim RR As Range
    Dim Ary() As Variant
    Dim C As Long
    Dim i As Long
    
    ' 各セル範囲からデータを抽出し、一次元配列に追加
    RangeToArySub ToUsedRange(Rng1), Ary, C
    
    For i = LBound(RngOther) To UBound(RngOther)
        RangeToArySub ToUsedRange(RngOther(i)), Ary, C
    Next
    
    ' 最終的な一次元配列を返す
    RangeToAry = Ary
End Function

RangeToArySub ToUsedRange(Rng1), Ary, C: RangeToArySub サブルーチンを呼び出して、各セル範囲からデータを一次元配列に追加します。
For i = LBound(RngOther) To UBound(RngOther): ParamArray を使用して可変数の引数を受け取り、それぞれのセル範囲からデータを一次元配列に追加します。
RangeToAry = Ary: 最終的な一次元配列を返します。

Private Function ToUsedRange(ByVal RR As Range) As Range
    If RR.Rows.Count = RR.Worksheet.Rows.Count Then
        Set RR = Intersect(RR, RR.Worksheet.UsedRange)
    End If
    If RR.Columns.Count = RR.Worksheet.Columns.Count Then
        Set RR = Intersect(RR, RR.Worksheet.UsedRange)
    End If
    Set ToUsedRange = RR
End Function

If RR.Rows.Count = RR.Worksheet.Rows.Count: もしセル範囲が行全体を表している場合、使用範囲内に絞り込みます。
If RR.Columns.Count = RR.Worksheet.Columns.Count: もしセル範囲が列全体を表している場合、使用範囲内に絞り込みます。

Private Sub RangeToArySub(ByVal Rng As Range, ByRef Ary() As Variant, ByRef C As Long)
    Dim Are As Range
    Dim VV As Variant
    Dim V As Variant
    Dim N As Long
    
    ' 各エリアからデータを抽出し、一次元配列に追加
    For Each Are In Rng.Areas
        N = Are.Cells.Count
        ReDim Preserve Ary(1 To C + N)
        
        If N = 1 Then
            C = C + 1
            Ary(C) = Are.Value
        Else
            VV = Are.Value
            For Each V In VV
                C = C + 1
                Ary(C) = V
            Next
        End If
    Next
End Sub

For Each Are In Rng.Areas: セル範囲を構成する各エリアについて処理を行います。
N = Are.Cells.Count: エリア内のセル数を取得します。
ReDim Preserve Ary(1 To C + N): 配列 Ary のサイズを拡張します。
If N = 1 Then: エリア内に単一のセルしかない場合、その値を直接一次元配列に追加します。
VV = Are.Value: エリア内の値を VV 配列に取得します。
For Each V In VV: VV 配列から各要素を取得し、一次元配列に追加します。

まとめ

この記事では、Excel VBAを使用して複数のセル範囲からデータを一次元配列に変換する効果的な方法に焦点を当てました。以下は、この記事で解説された主なポイントです。

テストサブルーチンの使用: test サブルーチンでは、RangeToAry 関数を使用して複数のセル範囲から一次元配列に変換し、その結果をデバッグウィンドウに表示しました。
RangeToAry 関数: メインの関数で、ToUsedRange 関数を呼び出して各セル範囲からデータを抽出し、可変長の配列に追加します。ParamArray を使用して可変数の引数を受け取り、それぞれのセル範囲からデータを一次元配列に追加します。
ToUsedRange 関数: 行または列全体の場合、使用範囲内に絞り込むための関数。セル範囲が行または列全体を表している場合、Intersect を使用して使用範囲内に絞り込みます。
RangeToArySub サブルーチン: 各セル範囲またはエリアからデータを抽出し、一次元配列に追加するサブルーチン。エリア内が単一のセルか複数のセルかによって処理が異なります。