エクセルVBA、「配列の宣言」
目次
1 配列のメリット
2 配列の宣言は2つある!
1)静的配列
2)動的配列
3 静的配列、動的配列どちらが便利か?
4 1次元配列と2次元配列
1)2次元配列の宣言(静的配列)
2)2次元配列の宣言(動的配列)
5 セルデータから簡単に配列を作る方法
6 終わりに…
1 配列のメリット
同じ型の変数を宣言しなくてよい!
例を出して説明します。以下のコードをご覧ください↓↓。
Sub study_Array() Dim name1 As String Dim name2 As String name1 = "佐藤" name2 = "鈴木" End Sub
変数nameの数だけ変数宣言します。1~2までしかありませんが、これが100個あったらどうでしょう??100行分の変数を宣言するのか?それは面倒だろ!という話になります。
この面倒さを解消するために「配列」が存在します。
2 配列の宣言は2つある!
1) 静的配列
先ほどの話を例に、100個の配列を宣言するにはどうすればいいのか?それは以下のコードのように宣言します。
Sub 静的配列() Dim myName(99) As String End Sub
なぜ99なのか。理由は「0で始まるから」です。ローカルウィンドウで変数myNameを確認すると、以下の画像の通り。
では、0スタートではなく、1スタートにするにはどう記述すればいいのか。答えは以下のコードの通り。
Sub 静的配列() Dim myName(1 to 100) As String End Sub
以下の画像の通り、1スタートになります。
(「Option Base 1」をモジュールの先頭に記述しておけば、myName(100)で、要素数が100になります!詳細は割愛しますm(_ _"m))
つまり、静的配列とは「最初から宣言する配列要素数が決まっている」ものになります。
では、動的配列とは何なのか?事項で説明します。
2) 動的配列
動的配列とは「あらかじめ要素数が決まっていないもの」。つまり、要素数が不確定なものです。
何を言ってるか分からないですよね(;^ω^)
説明します!
例えば、あなたが何かのセミナーを開催してるとします。セミナーの参加者名簿を作ります。参加者は、日によって異なりますよね。20人の時もあれば、今日は15人だったみたいな。そういう場合に、今回は要素数15、次は20と「変化する要素数を確定させて配列を宣言する」のです。
百聞は一見に如かず。例を出します。
上の画像の通り、
1/25は10人、
1/26は5人、
1/27は3人の参加者がいます。
1/25~1/27の参加者を動的配列で宣言するにはどうすればいいか。以下のコードの通りです。
Sub 動的配列1() Dim ws As Worksheet: Set ws = Worksheets("sheet1") Dim x As Integer, y As Integer, z As Integer Dim myArr() As String '動的配列の宣言 x = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1 '25日 y = ws.Cells(Rows.Count, 2).End(xlUp).Row - 1 '26日 z = ws.Cells(Rows.Count, 3).End(xlUp).Row - 1 '27日 ReDim myArr(1 To x) '10個の配列ができる ReDim myArr(1 To y) '5個の配列ができる ReDim myArr(1 To z) '3個の配列ができる End Sub
ReDimで動的配列myArrを作成します。今回は変数x,y,zに25~27日の生徒数を代入しています。
xは10個の配列が、yは5個の配列が、zは3個の配列ができます。
動的配列に値を代入してみる。
1/25の10個の配列に、生徒コードのデータを代入してみましょう!コードは以下の通りです。
Sub 動的配列2() Dim ws As Worksheet: Set ws = Worksheets("sheet1") Dim x As Integer, y As Integer, z As Integer Dim myArr() As String '動的配列の宣言 x = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1 '25日 ReDim myArr(1 To x) '10個の配列ができる '動的配列に値を代入 Dim i As Integer For i = 1 To x myArr(i) = ws.Cells(i + 1, 1) Next End Sub
このコードを動かして、ローカルウィンドウを見ると、以下の画像の通り動的配列myArrに生徒コードの値が代入できます。
3 静的配列、動的配列どちらが便利か?
私は、動的配列が便利だと思います。理由は「要素数の変化に対応できるから」です。
例えば以下のような商品コードリストがあるとします。
これを静的配列に値を代入するには以下のコードです。
Sub 静的配列3() Dim myName(1 To 10) As String myName(1) = Worksheets("Sheet2").Range("A2").Value myName(2) = Worksheets("Sheet2").Range("A3").Value myName(3) = Worksheets("Sheet2").Range("A4").Value myName(4) = Worksheets("Sheet2").Range("A5").Value myName(5) = Worksheets("Sheet2").Range("A6").Value myName(6) = Worksheets("Sheet2").Range("A7").Value myName(7) = Worksheets("Sheet2").Range("A8").Value myName(8) = Worksheets("Sheet2").Range("A9").Value myName(9) = Worksheets("Sheet2").Range("A10").Value myName(10) = Worksheets("Sheet2").Range("A11").Value End Sub
この処理の難点は何かというと、商品リストは日々増えていくということです。
以下の画像のように、商品リストは日々増加しますよね。
そうなると、静的配列の値代入のコードの書き直しが必要になります。
要素数が13個に変更した場合のコードは以下の通り。
Sub 静的配列3() Dim myName(1 To 13) As String myName(1) = Worksheets("Sheet2").Range("A2").Value myName(2) = Worksheets("Sheet2").Range("A3").Value myName(3) = Worksheets("Sheet2").Range("A4").Value myName(4) = Worksheets("Sheet2").Range("A5").Value myName(5) = Worksheets("Sheet2").Range("A6").Value myName(6) = Worksheets("Sheet2").Range("A7").Value myName(7) = Worksheets("Sheet2").Range("A8").Value myName(8) = Worksheets("Sheet2").Range("A9").Value myName(9) = Worksheets("Sheet2").Range("A10").Value myName(10) = Worksheets("Sheet2").Range("A11").Value myName(11) = Worksheets("Sheet2").Range("A12").Value myName(12) = Worksheets("Sheet2").Range("A13").Value myName(13) = Worksheets("Sheet2").Range("A14").Value End Sub
コードの変更部分は画像の蛍光線部の所です。
静的配列だと、このように要素数が変更する度にコードを追加、変更しなければなりません。これは面倒(;^ω^)
では、動的配列ではどうか。結論「コードの書き換えは不要」です。
<動的配列のコード>
Sub 動的配列3() Dim ws As Worksheet: Set ws = Worksheets("sheet2") Dim x As Integer Dim myArr() As String '動的配列の宣言 x = ws.Cells(Rows.Count, 1).End(xlUp).Row - 1 '最終行の取得 ReDim myArr(1 To x) '変数xの数だけ配列ができる '動的配列に値を代入 Dim i As Integer For i = 1 To x myArr(i) = ws.Cells(i + 1, 1) Next End Sub
<要素数が10の場合>
<要素数が13の場合>
明らかに動的配列が楽です。
しかし、静的配列も曜日のような定数的な値をいれる場合は効果的だと思います。
(例)曜日を静的配列に代入。
sub 静的配列_曜日 Dim Day_of_the_week(6) As String Day_of_the_week(0) = "日" Day_of_the_week(1) = "月" Day_of_the_week(2) = "火" Day_of_the_week(3) = "水" Day_of_the_week(4) = "木" Day_of_the_week(5) = "金" Day_of_the_week(6) = "土" End Sub
4 1次元配列と2次元配列
いままで説明してきたのが1次元配列についてです。ここからは2次元配列について説明していきます。
1)2次元配列の宣言(静的配列)
以下のようなエクセルの表があるとします。
この表を静的配列に代入してみましょう!
<2次元配列を静的配列に代入するコード>
Sub 静的配列_2次元配列1() Dim myProductArr(1 To 11, 1 To 3) As String Dim ws As Worksheet: Set ws = Worksheets("sheet3") Dim myRow As Long Dim myColumn As Long '配列に代入 For myRow = 1 To 11 For myColumn = 1 To 3 myProductArr(myRow, myColumn) = ws.Cells(myRow, myColumn).Value Next Next 'イミディエイトウィンドウに出力する。 Dim i As Integer For i = 1 To 11 Debug.Print myProductArr(i, 1) & " " & myProductArr(i, 2) & " " & myProductArr(i, 3) Next End Sub
処理結果は以下の画像の通り。
ローカルウィンドウを確認すると以下の画像の通り。2次元配列にデータが変数myProductArrにデータが代入されていることがわかります。
2)2次元配列の宣言(動的配列)
<2次元配列を動的配列に代入するコード>
Sub 動的配列_2次元配列1() Dim myProductArr() As String Dim ws As Worksheet: Set ws = Worksheets("sheet3") Dim myRow As Long Dim myColumn As Long Dim lastRow As Long Dim lastColumn As Long lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row lastColumn = ws.Cells(1, Columns.Count).End(xlToLeft).Column ReDim myProductArr(1 To lastRow, 1 To lastColumn) '配列に代入 For myRow = 1 To lastRow For myColumn = 1 To lastColumn myProductArr(myRow, myColumn) = ws.Cells(myRow, myColumn).Value Next Next 'イミディエイトウィンドウに出力する。 Dim i As Integer For i = 1 To 11 Debug.Print myProductArr(i, 1) & " " & myProductArr(i, 2) & " " & myProductArr(i, 3) Next End Sub
処理結果は以下の画像の通り(静的配列と同じ結果です)。
ローカルウィンドウを確認。(静的配列と同じ結果です)。
このように、静的配列は「Dim myProductArr(1 To 11, 1 To 3) As String」で2次元配列を宣言できます。
動的配列は、「Dim myProductArr() As String」で宣言し、「ReDim myProductArr(1 To lastRow, 1 To lastColumn)」で2次元配列を再宣言します。
5 セルデータから簡単に配列を作る方法
結論、配列変数をVariantで宣言してセルの範囲を代入する。
どういうことか分からないですよね(;^ω^)
説明します。
以下の画像の表をデータを配列に取り込みます。
以下のコードをご覧ください。
Sub Variantに配列を代入() Dim ws As Worksheet: Set ws = Worksheets("sheet3") Dim myAyy As Variant myAyy = ws.Range("A1:C11") End Sub
ローカルウィンドウを確認すると、変数myAyyに配列ができていることが確認できます。
配列データをセルに一括代入する。
セルから配列を作れる作れるように、
配列データをセルに一括で代入することができます。
例を出します。以下の画像のような表を用意します。
この表の「金額」を配列変数に取得した後計算し、配列変数ををセルに一括代入します。
<「金額」を求め、セルに配列を代入する処理コード>
Sub Variantに配列を代入2() Dim ws As Worksheet: Set ws = Worksheets("sheet4") Dim lastRow As Long Dim myAyy As Variant lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row myAyy = ws.Range("A1:D" & lastRow) Dim i As Long '列Dの金額を配列に代入する処理 For i = 2 To lastRow myAyy(i, 4) = myAyy(i, 2) * myAyy(i, 3) Next '配列を列に代入する。 ws.Range("A1:D" & lastRow) = myAyy End Sub
コードの実行後の結果は以下の画像の通り。「金額」の値が代入されています!
20万件の計算処理時間は「3秒」です。
ちなみに、配列に入れずに、セル参照をして処理をすると「12秒」かかります。
なんと「1/4」の時間の短縮になります。
配列の便利さを理解頂けましたでしょうか??
6 終わりに…
以上が配列の宣言をざっくりまとめたものです。ReDimは値を保持する「Preserve」を使うパターンもあるのですが、またの機会に記載します。
記事記載しました↓↓
bimori466-1.hatenablog.com
ではでは、この辺で(^^)/~~~