【EXCEL VBA】セル範囲を2次元配列に入れる理由と方法について

こんにちは

普段のVBA業務でこんな風なプログラムを組んだことはありませんか？？

Sub CommandButton1_Click() 'ボタンクリック時
    Dim i As long
    
    With Worksheets("Sheet1")
        For i＝1 to 100
            If .cells(i,1) = "OK" then’もしセル内がOKの場合
               .cells(i,2) = "OKです"
            else
                .cells(i,2) = "NOです"
            end if
        next
    End With

end sub

↑のプログラムはSheet1内のA列１～１００行がOKの時B列はOKです、

それ以外はNOですと入力するプログラムです

100行程度なら処理に問題はありません。

ここでどれくらいの処理時間がかかっているか確認しましょう。

始めと終わりに　「Debug.Print  Time」を入れます

   Debug.Print Time & "←開始時間"
   
   Dim i As Long
   With Worksheets("Sheet1")
       For i = 1 To 100
           If .Cells(i, 1) = "OK" Then'もしセル内がOKの場合
               .Cells(i, 2) = "OKです"
              Else
               .Cells(i, 2) = "NOです"
           End If
       Next
   End With
   
   Debug.Print Time & "←終了時間"

実行すると、イミディエイトウィンドウに時間が表示されます

（イミディエイトウィンドウは”表示””タブ→イミディエイトウィンドウ)

一秒以下で実行されました。早いですね。

しかしここで行数を増やしてみましょう。5万行くらいに

Private Sub CommandButton1_Click()
   Debug.Print Time & "←開始時間"
   
   Dim i As Long
   With Worksheets("Sheet1")
       For i = 1 To 50000'5万行に！
           If .Cells(i, 1) = "OK" Then 'もしセル内がOKの場合
               .Cells(i, 2) = "OKです"
              Else
               .Cells(i, 2) = "NOです"
           End If
       Next
   End With
   
   Debug.Print Time & "←終了時間"
   
End Sub

結果は3秒

先ほどよりも遅くなりましたね。3秒かかりました。

次に以下のプログラムを試してください

   
   Debug.Print Time & "←開始時間"

　　Dim Array_Data As Variant
   With Worksheets("Sheet1")
   Array_Data = .Range("A1", .Cells(50000, 2))
       
       For i = 1 To UBound(Array_Data)
           If Array_Data(i, 1) = "OK" Then
               Array_Data(i, 2) = "OKです"
           Else
               Array_Data(i, 2) = "NOです"
           End If
       Next
       
    .Range("A1", .Cells(50000, 2)) = Array_Data
   
   End With
   
   Debug.Print Time & "←終了時間"

結果は以下のようになりました

先ほどよりも早いです。2秒短縮できています。

こちらのプログラムは配列に一度入れてから処理を行い、配列ごと一気にセル範囲に貼り付けています。

配列に入れて処理をするメリットは処理時間を短縮してVBAを軽くすることができます

各行ごとに必要な解説していきます

Dim Array_Data As Variant

まず配列の要素を定義します。

要素数が変わるような場合の配列の型定義においてVariantは便利です。

今回は要素数は変わりませんが、自分は配列定義の時はVariantをよく使います。（ホントはだめかも)

Array_Data = .Range("A1", .Cells(50000, 2))

ここがキモです。

Variant型で定義した配列にセル範囲を格納しています

こちらの場合、セル"A1"を始点とし、cells(50000,2)→”B50000"セルを終点とした範囲を配列に格納します。

分かりやすく図で解説すると、

のように配列内にデータが格納されています。

もし確認したい場合、VBAのウィンドの左をクリックしてカーソル前の行まで実行する状態にします。

実行後一時停止されたらメニュータブの表示、ローカルウィンドを起動します

以下のように配列データ内を確認すると、データが入っているかどうかわかります

つぎにfor文内処理です

最初のプログラムでは毎回セルに処理アクセスしていましたが、こちらのプログラムでは配列内にアクセスしています。

For i = 1 To UBound(Array_Data)
           If Array_Data(i, 1) = "OK" Then
               Array_Data(i, 2) = "OKです"
           Else
               Array_Data(i, 2) = "NOです"
           End If
       Next

先ほどセル範囲を格納した配列内をFor文でOK、NOの判別をします。

   .Range("A1", .Cells(50000, 2)) = Array_Data

最後に配列データをセル範囲に貼り付けて終了です。

変数をそのままセル範囲に格納しています。

まとめ

セルへのアクセスの際に配列を使うことで処理が早くなります。

今回は例題でセル数は少なかったですが、セル数が多ければ多いほど配列で処理した方が早いです。（経験談）

2次元配列で考えると難しい！ってなるかもしれませんが、圧倒的に配列の方がいいので慣れましょう

以上