セルの値を配列に格納して使う

Excelはセルの値を直接使えば、
わざわざ配列を使わなくても済むということを
以前の記事で書きました。

しかし、配列に入れて使ったほうが
処理が速い場合もあるようです。

最初に
配列の添え字の最小値を1にするために
使用するモジュールの一番上に

Option Base 1

を入力してください。

セルの値を配列に格納して使う方法の基本の流れは次のようです。

1.Variant型の変数と出力用配列を宣言する
2.Variant型の変数にセルの値を入れる(これが配列になる)
3.出力用配列のサイズを指定
4.Variant型の変数に入れた値を処理して出力用配列に入れる
5.出力用配列の値をセルに入れる

まずは１列のデータから１列のデータを出力してみます。

セルのA1からA5に１から５までの数字が入っているとします。
これを２乗する処理を配列を使ってやります。
出力用配列のサイズ指定に注意してください。

ReDim dOutput(UBound(vData))

と指定すると1行5列の配列になってしまいます。

コードはこちら

Sub macro101120a()
'セルを配列に入れて使う
'1列のデータから1列のデータを出力

    Dim i As Long

    '1.Variant型の変数と出力用配列を宣言する
    Dim vData As Variant
    Dim dOutput() As Double
   
    '2.Variant型の変数にセルの値を入れる(これが配列になる)
    vData = Range("A1:A5") '5x1

    '3.出力用の配列のサイズを指定
    ReDim dOutput(UBound(vData), 1) '5x1

    '4.Variant型の変数に入れた値を処理して出力用配列に入れる
    'ここでは2乗している
    For i = 1 To UBound(vData)
        If IsNumeric(vData(i, 1)) Then
            dOutput(i, 1) = vData(i, 1) * vData(i, 1)
        End If
    Next i

    '5.出力用配列の値をセルに入れる
    Range("B1").Resize(UBound(dOutput, 1), 1).Select 'わかりやすく選択する
    Range("B1").Resize(UBound(dOutput, 1), 1) = dOutput '5x1

End Sub

実行前

実行後

B1からB5に2乗された値が入力されました。

次は２列のデータから１列のデータを出力してみます。
上の画像のA1:B5のデータを使って足し算したものを
C1:C5に出力します。

Variant型の配列の最大の添え字を返すUBoundの引数を
上のコードから変更しています。

コードはこちら

Sub macro101120b()
'セルを配列に入れて使う
'2列のデータから1列のデータを出力

    Dim i As Long

    '1.Variant型の変数と出力用配列を宣言する
    Dim vData As Variant
    Dim dOutput() As Double
   
    '2.Variant型の変数にセルの値を入れる(これが配列になる)
    vData = Range("A1:B5") '5x2

    '3.出力用の配列のサイズを指定
    ReDim dOutput(UBound(vData, 1), 1) '5x1

    '4.Variant型の変数に入れた値を処理して出力用配列に入れる
    'ここでは2乗している
    For i = 1 To UBound(vData, 1)
        If IsNumeric(vData(i, 1)) And IsNumeric(vData(i, 2)) Then
            dOutput(i, 1) = vData(i, 1) + vData(i, 2)
        End If
    Next i

    '5.出力用配列の値をセルに入れる
    Range("C1").Resize(UBound(dOutput, 1), 1).Select 'わかりやすく選択する
    Range("C1").Resize(UBound(dOutput, 1), 1) = dOutput '5x1

End Sub

実行後

最後に２列のデータから２列のデータを出力してみます。
前のコードでも使ったA1:B5のデータを使って
足し算と掛け算をしたものを出力します。

出力用の配列のサイズの指定と
出力用の配列をセルに入れるときの範囲の指定のところに
変更を加えました。

コードはこちら

Sub macro101120c()
'セルを配列に入れて使う
'2列のデータから2列のデータを出力

    Dim i As Long

    '1.Variant型の変数と出力用配列を宣言する
    Dim vData As Variant
    Dim dOutput() As Double
   
    '2.Variant型の変数にセルの値を入れる(これが配列になる)
    vData = Range("A1:B5") '5x2

    '3.出力用の配列のサイズを指定
    ReDim dOutput(UBound(vData, 1), 2) '5x2

    '4.Variant型の変数に入れた値を処理して出力用配列に入れる
    'ここでは2乗している
    For i = 1 To UBound(vData, 1)
        If IsNumeric(vData(i, 1)) And IsNumeric(vData(i, 2)) Then
            dOutput(i, 1) = vData(i, 1) + vData(i, 2)
            dOutput(i, 2) = vData(i, 1) * vData(i, 2)
        End If
    Next i

    '5.出力用配列の値をセルに入れる
    Range("C1").Resize(UBound(dOutput, 1), UBound(dOutput, 2)).Select 'わかり

やすく選択する
    Range("C1").Resize(UBound(dOutput, 1), UBound(dOutput, 2)) = dOutput '5x2

End Sub

実行後

これらの例は簡単な計算ですので
ワークシート関数を使ったほうが速いです。

参考URL：Excel 2007 におけるパフォーマンスの改善