ワークシートの関数を使う（WorksheetFunction）

最終更新日：2016-03-29

第10回.ワークシートの関数を使う（WorksheetFunction）

マクロVBAに用意されている関数を一般にはVBA関数と呼びます、

VBA関数は150程度あり便利に使えます、

しかしエクセルには、ワークシートで使える豊富な関数があります、

これをマクロVBAでも使えれば、さらに便利になります。

VBAでワークシート関数を使う場合は、ちょっとした書き方の決まりがあります。

呪文みたいな文を書いてから、.(ピリオド)に続けて関数名を書くことでワークシートの関数が使えるようになります。

worksheetfunctionと書いてから、.(ピリオド)を打つと、つかえる関数が候補表示されます。

ただし、全部のワークシート関数がVBAで使えるわけではありません、一部使えない関数もあります。

・文字列関係
・日付関数

このあたりは、VBA関数にある同名の関数が用意されているため、ワークシート関数として使えません
例えば、候補表示された中から、LEFT関数を探しても見つかりません。
VBA関数にLeft関数があるので、そちらを使ってくださいという事なのでしょう。

以下の表で実際に使ってみましょう。

まずは、単純に、B列に、D:EからVLOOKUPで商品名を取得してみます。

改行は、適当に見やすいと思う位置で入れて下さい、上記は画像の都合も考慮して改行しています。
また、FalseはVBAでも0と記述出来ます。

これは実行してもらえれば分かる通りで、シート上と全く同じです。
そうなんです、シートで関数が使えれば、それをVBAで使う事は簡単です。
他の関数も全く同様です、引数の指定も同じなので、使い方に困ることはありません。
逆に言えば、シートで関数が使えなければVBAでも使えません。
もし、もしも、VLOOKUP関数が分からないとしたら・・・
マクロVBAの前にシート関数をしっかりとマスターしてからにしましょう。

これなら、いつでもワークシート関数が使える・・・
なんですが、ちょっと注意点と言いますか、問題点があります。
ワークシート関数の結果がエラー値（#○○）となる場合です。
つまり、このVlookupの場合は、検索値（第1引数）が範囲（第2引数）に存在しない場合にエラーになります。
ワークシート上では、エラー値（#○○）が表示されますが、
マクロVBAでは、実行がエラーとして停止してしまいます。

3行目のA006がD:Eに存在していないので、
先の練習13を実行すると、

このようなメッセージが表示されてマクロ実行がストップしてしまいます。

マクロ実行がストップしてしまっては困りますので、なんらかの対処が必要になります。
良く使う関数で、この対策が必要な関数としては、
WorksheetFunction.Vlookup
WorksheetFunction.Match
この二つは使用頻度の高い関数ですが、エラー対策が必要になります。

・対策１
事前に存在確認をしてから当該の関数を使う方法です。

ちょっと面倒ですが、Countifで>0つまり存在しているときだけ関数を実行します。

・対策2
エラーを無視して先に進む方法です。

On Error Resume Next
これを書いた後は、エラーが発生して無視して先に進んでくれます。
On Error GoTo 0
これは、エラーになったら、元のように停止する状態に戻すものです。
上記の場合は、もうお終いなので書く必要はないのですが、合わせて紹介する意味で記載しました。

・上記以外のエラー
例えば0除算の#DIV/0!などの場合も、対策が必要になります。
VBAでは、「0で除算しました。」のメッセージが出ます。
単純に分母が0かどうかを判定しましょう。
セル = 分子 / 分母
この場合なら､
If 分母 <> 0 Then
　　セル = 分子 / 分母
End If
分母が0の時は値を0にするなら、Elseで0を入れて下さい。

※エラー対処のために昔の記述を使う方法
WorksheetFunctionではなく、Applicationにあるワークシート関数を使うことが出来ます。

これを使った場合は、エラー値（#○○）が返されるので、マクロはストップしません。
しかし、これは昔々の記述方法が残されているものです。
ですので、Application.と入力しても候補には表示されません。
また、エラー値がセルにあると、そのエラー値のセルをマクロで扱う時にかなりの注意が必要になります。
使っても良いのですが、あまり積極的に使うものではありません。