エクセルマクロ配列で変数は使えますか

質問

エクセル2013です。
初めて配列を使います。
以下のように作成し思ったようにできました。

Sub 計算()
'成功
Dim a As Integer
Dim c As Integer
Dim b(5) As Integer
Dim 最終行
Dim 値列　

値列 = 17
最終行 = Cells(Rows.Count, 1).End(xlUp).Row
For 処理業 = 1 To 最終行
For a = 1 To 5
b(a - 1) = Cells(1, 値列)
値列 = 値列 + 1
Next
値列 = 17
For a = 1 To (22 - 値列)
c = c + b(a - 1)
Next
Cells(処理業, 30) = c
a = 0
c = 0
Next 処理業

End Sub

ただ計算する列の範囲をインプットボックスで入力した値
にしたい為以下のように改造しました。
Dim b(対象列) As Integerでエラーになります
配列には変数は使用できないのでしょうか？
よろしくお願いします。

Sub 計算()
'失敗
Dim a As Integer
Dim c As Integer
Dim b(対象列) As Integer’★ここでERRになる
Dim 最終行
Dim 対象列
Dim 値列　

対象列 = 22'インプットボックスで入力した値
値列 = 17

最終行 = Cells(Rows.Count, 1).End(xlUp).Row
For 処理業 = 1 To 最終行
For a = 1 To (対象列 - 17)
b(a - 1) = Cells(1, 値列)
値列 = 値列 + 1
Next
値列 = 17
For a = 1 To (22 - 値列)
c = c + b(a - 1)
Next
Cells(処理業, 30) = c
a = 0
c = 0
Next 処理業

End Sub

回答No.1

kybo · ベストアンサー

以下の★印のように修正・追加してみてください。

Sub 計算()
Dim a As Integer
Dim c As Integer
Dim b() As Integer '★
Dim 最終行
Dim 対象列
Dim 値列

対象列 = 22 'インプットボックスで入力した値
値列 = 17
ReDim b(対象列) As Integer '★

最終行 = Cells(Rows.Count, 1).End(xlUp).Row
For 処理業 = 1 To 最終行
For a = 1 To (対象列 - 17)
b(a - 1) = Cells(1, 値列)
値列 = 値列 + 1
Next
値列 = 17
For a = 1 To (22 - 値列)
c = c + b(a - 1)
Next
Cells(処理業, 30) = c
a = 0
c = 0
Next 処理業

End Sub

お礼

教えていただいた方法でうまくできました。
ただ
a = 0
c = 0
の後に
ReDim b(対象列) As Integer '★
を入れないと正しく動作しないのがわかりました。
どうもありがとうございました。

回答No.3

High_Score

For 処理業=1 To 最終行 のループが1回終わる度に配列bを初期化したいようですね。c=0の下にReDimを入れるとそのように動作します。そうしたいのならば、For 処理業=1 To 最終行の上にあるReDimをFor 処理業～の直下に移動すれば、c=0の下は不要です。

お礼

はい。配列に格納した値がそのままでは
計算がおかしくなりました。
ありがとうございました。

回答No.2

mitarashi

http://okwave.jp/qa/q8698624.htmlで回答したものですが、もう少し詳しく説明いたします。

'A1:C1000000で、A列とB列の値の合計をC列に求めてみる
'一旦配列に取込、配列上で演算し、一括でセルに書き戻す：1810msec
Sub test()
Dim sh As Worksheet
Dim targetRange As Range
Dim myArray1 As Variant, myArray2 As Variant
Dim i As Long
Dim startTime As Long

startTime = GetTickCount
Set sh = ActiveSheet
With sh
Set targetRange = .Range(.Range("A1"), .Range("B" & .Rows.Count).End(xlUp))
End With
'対象セルを全て配列に取り込む
myArray1 = targetRange.Value
'出力先の配列サイズ決定、一括で書き戻すためには1列でも二次元の配列を宣言する必要がある
ReDim myArray2(1 To UBound(myArray1, 1), 1 To 1)
'メモリー上で演算（Cellアクセスしない）
For i = LBound(myArray1, 1) To UBound(myArray1, 1)
myArray2(i, 1) = myArray1(i, 1) + myArray1(i, 2)
Next i
'一括してCellに書き戻す
'書き戻して1400msec位かかる
'Cell一個当たりは1.4μsecなのだが...
sh.Range("C1").Resize(UBound(myArray2, 1), 1).Value = myArray2
Debug.Print GetTickCount - startTime
End Sub

'普通にCellで演算する場合
'27160msec 15倍かかる
Sub test2()
Dim sh As Worksheet
Dim targetRange As Range
Dim i As Long
Dim startTime As Long

startTime = GetTickCount
Application.ScreenUpdating = False
Set sh = ActiveSheet
With sh
For i = 1 To 1000000
.Cells(i, 3).Value = .Cells(i, 1).Value + .Cells(i, 2).Value
Next i
End With
Application.ScreenUpdating = True
Debug.Print GetTickCount - startTime
End Sub

http://okwave.jp/qa/q8698624.htmlに比べると、速度差は15倍に縮小してしまいましたが、セルに100万個いっぺんに書き戻す部分で時間がかかっています。駄目元でScreenUpdatingや、Calculationを設定してみても効果はありませんでした。
ご参考まで。

お礼

わざわざありがとうございます。