【ExcelVBA】配列の操作

配列の要素指定

これまで静的配列と動的配列について紹介してきました。
配列を使うことで複数の値を格納できるようになり、インデックス番号を指定することで値を格納したり取り出したりすることができます。
このインデックス番号は数値を直接指定するだけでなく、以下のように変数で指定することもできます。

Sub Sample()
    Dim score(3) As Integer
    Dim i As Integer
    i = 0
     
    score(0) = 90
    score(1) = 81
    score(2) = 92
     
    MsgBox "最初の値は" & score(i)
End Sub

実行結果は最初の値は90　となります。iの中の値を変更すれば表示される内容も変わります。

For Nextステートメントとの組み合わせ

配列の中の値をすべて順番に表示させたり取り出したりする場合、1つずつ取り出して処理を書いていると手間がかかります。
例えば以下のようなコードです。

Sub Sample()
    Dim score() As Integer
    Dim sum As Integer
     
    ReDim score(2)
     
    score(0) = 90
    score(1) = 81
    score(2) = 92
     
    sum = score(0) + score(1) + score(2)
         
    MsgBox "合計値は" & sum
     
End Sub

今回は3つの値を足し算するだけですが、要素数が増えてくると足し算している部分のコードが長くなってしまいます。

1	`sum = score(0) + score(1) + score(2) + score(3)・・・`

こういった際に、以前にでてきたFor Nextステートメントを利用することで以下のように書き換えることができます。

Sub Sample()
    Dim score() As Integer
    Dim sum As Integer
    sum = 0
    ReDim score(2)
     
    score(0) = 90
    score(1) = 81
    score(2) = 92
     
    For i = 0 To 2
        sum = sum + score(i)
    Next
         
    MsgBox "合計値は" & sum
     
End Sub

この場合要素が100個あったとしても足し算している箇所のコードに変更はありません。
これが変数に値を格納していた場合、100個の変数を足し算していく処理を書いていく必要があります。
こうした使い方ができるのも配列のメリットとなります。

サンプルコード

ではシートに記載されているスコアの合計値を表示させるコードを見てみましょう。
シートには以下のように値が入力されています。

B2セルに参加人数が記載されており、A4から下に人数分のスコアが記入されているとします。
今回は8人ですが、日によって参加人数が不定とします。
平均値以上のスコアをメッセージボックスに表示させるとします。

Sub Sample()
    Dim score() As Integer
    Dim sum As Integer
    Dim avg As Double
    Dim message As String
    sum = 0
    num = Cells(2, 2).Value     '人数を読み取り(B2の値)
    ReDim score(num) As Integer         '読み取った人数の分で配列の要素数を指定
    
　　'A4から下の列の値を順番に配列scoreに格納　
    For i = 0 To num - 1                
        score(i) = Cells(4 + i, 1)　　　
        sum = sum + Cells(4 + i, 1)
    Next
     
    avg = sum / num                     '平均値を算出
     
    '配列の中身が平均以上の値だけを取り出して文字を結合
    For i = 0 To num - 1
        If score(i) >= avg Then                  '平均以上の場合のみmessageの文字列に配列scoreの値が結合される
            message = message & score(i) & " "
        End If
    Next
     
    MsgBox "平均以上のスコアは" & message
        
End Sub

動的配列を今回は使用しています。人数を読み取り変数numに格納し、ReDimで要素数をしてする際の要素数としてnumを使っています。
静的配列は要素数が決まっていないといけないため、人数を読み取ったとしても以下のようには書けません。

1	`Dim` `score(num)` `As` `Integer`

コードを少し見てみましょう
最初のFor NextステートメントはA4から下の列の値を順番に配列scoreに格納し、加算していく処理となります。
最初はiが0となるため、score(0)にセル(4,1)つまりA4の値を格納しています。またsumにA4の値を加算しています。
numには8が格納されているため　iが0の次は1,2,3,4,5,6,7と増やしながら処理を続けていきます。
つまりscore(1)にはセル(4+1,1)の値、score(2)にはセル(4+2,1)の値というような処理となります。
次のFor Nextステートメントは平均値以上の値が入っているところだけの値を取り出す処理となります。
実行結果は　平均以上のスコアは90 92 100 90　となります。