VBA｜「配列で同じ要素が表示されてしまう」対処方法

配列を使っていますか？

今回は、配列で格納した要素をすべてエクセル表に出力しようとした際に、同じ要素しか出力しないときの対処方法について紹介していきます。

VBA配列で同じ要素が表示されてしまう

配列変数に格納した文字列を、左図のように同じ列に「みかん、ぶどう、スイカ」と出力しようとしますが、、、

なぜか右図のように同じ要素が表示されてしまう。。。

それでは、間違ったVBAコードと正しいVBAを説明します。

同じ要素が表示されてしまう【間違ったVBAコード】

Sub 同じ要素が表示されてしまう()
   Dim hensu(2) As Variant 
   hensu(0) = "みかん"  
   hensu(1) = "ぶどう"  
   hensu(2) = "スイカ"  
   Range(Cells(1, 1), Cells(3, 1)).Value = hensu　'セルA1～A3に「みかん～スイカ」を表示させたつもり。
End Sub

これはよくあるエラーというか、ミスです。

Excel VBAでは、６行目のような　セル範囲＝配列変数　と記述し、配列変数にインデックス番号を付けずに要素を全出力させる場合には、下図のように同じ行に表示される仕様になっています。

したがって、インデックス番号を付けずに１次元配列を出力すると、A列はみかん、B列はぶどう、C列はスイカ、、、というように出力されることになります。

上記コードでは、A1～A3セルを指定しているので、「A列＝みかん」が出力されてしまうのです。

同じ要素が表示されてしまう【正しいVBAコード】

正しいVBAコードは以下のようになります。

Sub 正しいコード()
   Dim hensu(2) As Variant  
   hensu(0) = "みかん"  
   hensu(1) = "ぶどう"  
   hensu(2) = "スイカ"  
   'Range(Cells(1, 1), Cells(3, 1)).Value = hensu　'セルA1～A3に「みかん～スイカ」を表示させたつもり。
   Cells(1, 1) = hensu(0)  'インデックス番号を指定して出力させる
   Cells(2, 1) = hensu(1)
   Cells(3, 1) = hensu(2)
End Sub

めんどくさいですが、同じ列に要素を出力する場合には、インデックス番号の指定が必要になります。

いちいち指定するのが面倒な場合は、Forステートメントを利用すると以下のようになります。

Sub Forを使った正しいコード()
   Dim hensu(2) As Variant
   Dim i As Long
   
   hensu(0) = "みかん"
   hensu(1) = "ぶどう"
   hensu(2) = "スイカ"
   
   For i = 0 To 2
     Cells((i + 1), 1) = hensu(i)
   Next i
End Sub

出力する際に、いちいちインデックス番号を指定するのならば、Forステートメントを使って簡潔に記述する方法が一般的です。