今回はVBAの高速化という点で重要な『配列』について解説していきます。
ここでは、細かい配列の話は抜きにして「こうやったら使える！」という実践的な話をしていきますね。
今回行う内容
今回VBAで実行するのは、商品価格がA列にあって、
- 商品価格10%で消費税を計算
- 商品価格と消費税を足した合計を出す
この2つをやっていきたいと思います。
Excelは、こんな感じですね。
手動であれば、A列に対して0.1をかけて消費税を計算。
計算した消費税と商品価格を足して合計を出すという形ですね。
これをVBAで組んでいきたいと思います。
配列を使わないプログラム
比較のために、まずは配列を使わないプログラムになります。
Sub calcTaxAndSum()
Dim i As Long
Dim itemPrice As Long
Dim tax As Long
Dim totalPrice As Long
Range("B2", "C100001") = ""
For i = 2 To 100001
itemPrice = Cells(i, 1)
tax = itemPrice * 0.1
totalPrice = itemPrice + tax
Cells(i, 2) = tax
Cells(i, 3) = totalPrice
Next i
End Sub
for文で2行目から100001行目まで繰り返して、都度セルに値を入力しています。
10万行の処理になっているので、これを実行すると
15～20秒という時間が実行にかかります。
配列を使って高速化
というので、これでは遅すぎるので配列を使って高速化していきたいと思います。
コードはこちらです。
Sub calcTaxAndSumUseArray()
Dim i As Long
Dim itemPrice As Long
Dim tax As Long
Dim totalPrice As Long
Dim inputArray As Variant
Dim arrayRow As Long
arrayRow = 1
Range("B2", "C100001") = ""
inputArray = Range("B2", "C100001")
For i = 2 To 100001
itemPrice = Cells(i, 1)
tax = itemPrice * 0.1
totalPrice = itemPrice + tax
inputArray(arrayRow, 1) = tax
inputArray(arrayRow, 2) = totalPrice
arrayRow = arrayRow + 1
Next i
Range("B2", "C100001") = inputArray
End Sub
配列を使わない時には、for文で繰り返した処理の中にCellsを使ってセルに値を入力していました。
その部分を今回は配列値を入力して、最後に一度だけセルに値を入力という処理にしています。
実際これだけで10万行の処理は
0.52秒という感じで30倍近い速度が出せるようになっています。
配列の使い方
使い方としては、
- Variant型で変数を宣言（vとする）
- 最終的に入力したいセルに入って値を消す
- そのセルを宣言した変数に入れる
- 二次元配列になっているので【v(1,1)=値】の様に値を入れる
- 最後まで値を入れ終わったら入力したいセルの範囲に配列を入力する
具体的にコードで書いていくと
Sub arrayTest()
Dim v As Variant
Range("A1", "B100") = ""
v = Range("A1", "B100")
' for文などの繰り返し処理で値を入力することが多い。
v(1, 1) = 100
v(1, 2) = 200
v(2, 1) = 300
v(2, 3) = 400
' 配列に入れた値をセルに入力
Range("A1", "B100") = v
End Sub
こういった感じですね。
ちなみにv(1, 1)の部分を変数にして、繰り返し処理の中で使うのがかなり多いです。
ということで、簡単にですがVBAで配列を使って高速化する方法について解説しました。
最初のうちは慣れないと配列は難しいのですが、今回のように
セルの範囲を指定してを配列にするこの形でやるとかなり理解しやすいかと思います。
もしこれで配列が分かるようになってきたら、一次元配列なんかも使っていくとプログラミングの幅が広がります。
ただこの方法だけでも、かなり処理速度が速くなるので試してみてくださいね。