ワークシート関数と、マクロ、どっちを使うべきなの？

ジーザス小川です。

「どこまでのことはエクセルの関数とかで、どこからは、マクロを使って処理をするべきものなのですか？」

エクセルVBAについてセミナーで教えていると、よくそんな質問をいただきます。

今日は、その回答から。

例えば、シート上に、以下の値があるとします。

     | A | B | C |
     _____________
1行目|  2|  9|  3|
     _____________
2行目|  5|  8|  9|
     _____________
3行目|  3|  4|  6|
     _____________
4行目|  5|  8|  9|
     _____________
5行目|  2|  0|  7|
     _____________
6行目|  8|  6|  1|
     _____________

そして、あなたは、この合計を、セル「E1」に書き出したい。

あなたは、マクロを覚えたてです。さて、どうするか。

ちなみに、エクセルのワークシート関数だと、セル「E1」に、以下の関数を入れることになります。

エクセルVBAでこの課題を解くと、一例としては、以下の要領になります。

Sub ByForEach()
    Dim r As Range
    Dim c As Long
    For Each r In Range("A1:C6")
        c = c + r.Value
    Next
    Range("E1").Value = c
End Sub

結論としては…。

こういうときに、どっちの手法を採用するかについては、以下のことを軸に考えると良いかと思います。

○基本は、そのときより簡単に採用できる方法で

そのときにより簡単に採用できる方法で実現するのが基本です。

この場合で言えば、明らかに、エクセルのSUM関数を使ったほうが楽ですよね。

○より高度な作り込みをする可能性がありそうか

なんですが。

あとあとのメンテナンスも考慮すると、そうでもない場合があります。

例えば、ここでしたい集計の趣旨がちょっと変わったとします。

「単なる合計」ではなく

「値が5を超えているものだけの合計を取ってくる」
とか
「セルの背景色が赤いものだけの合計を取ってくる」
とか

そういうことを要求された場合です。

エクセルのワークシート関数で実現する方法はちょっと説明が面倒くさいので。。

VBAのコードのほうでそのやり方を紹介します。

値が5を超えているものだけの合計を取ってくるように修正した

Sub ByForEachArrange1()
    Dim r As Range
    Dim c As Long
    For Each r In Range("A1:C6")
        If r.Value >= 5 Then '[1]
            c = c + r.Value
        End If               '[2]
    Next
    Range("E1").Value = c
End Sub

セルの背景色が赤のものだけの合計を取ってくるように修正した

  Sub ByForEachArrange2()
    Dim r As Range
    Dim c As Long
    For Each r In Range("A1:C6")
        If r.Interior.ColorIndex = 3 Then '[1]
            c = c + r.Value
        End If                            '[2]
    Next
    Range("E1").Value = c
End Sub

このとおり、どっちの場合も、VBAだと、2行のコード追加だけで解決します。

後々いろいろと作り込む可能性があるようでしたら、断然、マクロで書いたほうがよいです。

○できれば、今の自分にとって、より勉強になる方法で

それから、さらにもうひとつの考慮点。

極力、そのときの自分にとって、勉強になる方法を採用しましょう。

セミナー等でエクセルVBAを学んで覚えたての状態だったら、知識が定着するまでは、関数のほうが簡単に仕上がると分かっていたとしても、VBAの練習の意味もかねて、なるべくマクロで書くようにするべきです。

「セルの値を合計する」

といったような簡単なことでさえ普段からマクロで実現するように努力をしていると、地力がついてくるので、いざというときに、

「セルの背景色が赤いものだけの合計を取ってくる」

といった多少ややこしいケースになったとしても、問題なくすぐに対応できるんですね。

新しい技術の定着期には、ある程度の集中した練習が必要です。そういう時期には、積極的に、マクロを書くようにしましょう。