EXCELでは、ワークシート or VBAに関わらず、数値計算することが多いですね。
数値計算の中でも、最も多いのは足し算ではないでしょうか。
ワークシートではsum関数が大活躍しますが、VBAでは使えるのでしょうか?
答えはYESです。
とても便利なだけでなく、VBAでは足し算を高速に実行することができます。
本記事では、「VBAで足し算をsum関数で高速実行する方法」と「sum関数における計算範囲の指定方法や変数化の方法」について解説します。
1.EXCELのワークシート上で使うsum関数
こんにちは。エクセルマン・ブリーダーのしもむぎ(@re_znd13)です。
まずはEXCELのワークシート上で使うsum関数をおさらいしておきましょう。
sum関数は以下のような構文で使います。
sum(セル範囲)
sum(セル範囲1, セル範囲2, ・・・)
セル範囲として指定した数値の合計を求めてくれる、というものです。
2.VBAでも使えるsum関数
それでは、VBAではどうやってsum関数を使えばよいでしょうか?
A列の1行目~最終の1048576行目まで、乱数を与えて、その合計を取得するケースを考えてみましょう。
sum関数はワークシートファンクションですから、以下のようなVBAコードになります。
Sub 足し算sum()
Dim total As Single
total = Application.WorksheetFunction.sum(Range("A:A"))
MsgBox total
End Sub
実行結果はこちらです。
3.とても高速なsum関数の計算時間
前述した通り、sum関数は便利なだけでなく、とても高速であるというメリットがあります。
前章と同じくA列の1行目~最終の1048576行目までに与えた乱数の合計を取得するケースで、時間を見てみましょう。
時間計測には、Timer関数を使います。
前述のVBAコードに時間計測を加えたコードは以下です。
Sub 足し算sum_時間計測()
Dim total As Single
Dim st, ft, t As String
st = Timer
total = Application.WorksheetFunction.sum(Range("A:A"))
ft = Timer
t = ft - st
MsgBox "合計" & total & "を計算するのに" & t & "秒かかりました"
End Sub
実行結果はこちら。メッセージボックスに計測した時間を表示させていますが、0.03秒ととても高速であることがわかります。
試しに、For文の繰り返しで合計を出した場合と比較してみましょう。
VBAコードと実行結果は以下です。
2.6秒となり、sum関数の高速さがとてもよくわかる結果となりました。
Sub 足し算_時間計測()
Dim s, total As Single
Dim i As Long
Dim st, ft, t As String
st = Timer
For i = 1 To 1048576
s = Cells(i, 1).Value
total = total + s
Next
ft = Timer
t = ft - st
MsgBox "合計" & total & "を計算するのに" & t & "秒かかりました"
End Sub
4.sum関数における範囲指定の方法
次に、sum関数における範囲指定のいろいろな方法を見てみましょう。
第2章で紹介したVBAコードでは、A列全体を指定していますので、以下のような記述で範囲指定をしました。
RANGE("A:A")
最終行がわかっている場合は以下のように記述します。
RANGE("A1:A1048576")
VBAではセルの記述はCells(1,1)とも書けますから、以下のように記述をしてもよいですね。
RANGE(Cells(1,1), Cells(1048576,1))
少し煩雑な記述に見えますが、範囲が動的な場合には、例えば以下のように記述できるので、便利です。
RANGE(Cells(1,1), Cells(i,1))
また、複数の離れた範囲を指定したい場合は以下のように記述します。
この例ではA1からA100の範囲と、D1からD100の範囲を指定しています。
RANGE("A1:A100","D1:D100")
5.範囲の変数化
前章ではいろいろな範囲指定の方法を紹介しましたが、例えばループさせたい場合など、範囲を変数化できれば便利ですよね。
そこで、本記事の最後になりますが、範囲を変数化するVBAコードの例を挙げておきます。
Sub 足し算_範囲の変数化()
Dim total As Single
Dim rng As Range
Set rng = Range("A1:A1048576")
total = Application.WorksheetFunction.sum(rng)
MsgBox total
End Sub
rngという変数に、A列の1行目~最終の1048576行目までをセットして、それをsum関数に与えています。
Setをお忘れなく。
6.まとめ
本記事では、「VBAで足し算をsum関数で高速実行する方法」と「sum関数における計算範囲の指定方法や変数化の方法」について解説しましたが、いかがでしたか?
sum関数はワークシートファンクションですが、VBAでも使えますし、利点もたくさんありましたね。
うまく使っていきましょうね。