VBA再入門
ワークシートの関数を使う(WorksheetFunction)

マクロが覚えられないという初心者向けに理屈抜きのやさしい解説
最終更新日:2020-09-26

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


マクロVBAに用意されている関数を一般にはVBA関数と呼びます。
VBA関数は150程度あり便利に使えます。


しかしエクセルには、ワークシートで使える400以上の豊富な関数があります、
これをマクロVBAでも使えれば、さらに便利になります。

マクロVBAでのワークシート関数の使い方

VBAでワークシート関数を使う場合は、ちょっとした書き方の決まりがあります。
呪文みたいな文を書いてから、.(ピリオド)に続けて関数名を書くことでワークシートの関数が使えるようになります。
worksheetfunctionと書いてから、.(ピリオド)を打つと、使える関数が候補表示されます。

Excel VBA サンプル

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

・文字列関係
・日付関数


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

長いスペルの単語を簡単に入れる方法

worksheetfunction
長くて入力が大変ですよね。
VBEには便利な機能があります。
VBEで、
Ctrl+Space

VBA マクロ worksheetfunction

このように候補表示が出てきますので、
入れたい単語の先頭文字から入れていくことで目的の単語が出てきます。
シートの関数を=から入れていく時と同じですね。

VBA マクロ worksheetfunction

woまで入れると、worksheetfunctionが2番目に出てくるので、これを選択してTabキーで確定してください。
Enterキーで確定してしまうと改行されてしまうので、必ずTabキーを使うようにしてください。

ワークシート関数を使ってみよう

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

Excel VBA サンプル表

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

Excel VBA コード

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

これは実行してもらえれば分かる通りで、シート上と全く同じです。
そうなんです、
シートで関数が使えれば、それをVBAで使う事は簡単です。
他の関数も全く同様です。
引数の指定も同じなので、使い方に困ることはありません。
逆に言えば、シートで関数が使えなければVBAでも使えません。
もし、もしも、VLOOKUP関数が分からないとしたら・・・
マクロVBAの前にシート関数をしっかりとマスターすることをお勧めします。
エクセル入門
・ショートカットキー ・基本操作 ・表示形式 ・セルの書式設定 ・入力規則 ・数式・関数 ・数式・新関数

ワークシート関数を使う時の注意点、エラー対策

これなら、いつでもワークシート関数が使える・・・
なのですが、ちょっと注意点と言いますか、問題点があります。

ワークシート関数の結果がエラー値(#○○)となる場合です。
例えばこのVlookup関数の場合は、検索値(第1引数)が範囲(第2引数)に存在しない場合にエラーになります。
ワークシート上では、エラー値(#N/A)が表示されますが、
マクロVBAでは、実行がエラーになり停止してしまいます。

Excel VBA 問題

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

Excel VBA エラーメッセージ

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

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

この二つは使用頻度の高い関数であり、かつ、エラー対策が必要な関数です。

ワークシート関数のエラー対策1

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

Excel VBA コード

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

ワークシート関数のエラー対策2

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

Excel VBA コード

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の後に.に続けて関数を書く方法です。
使える関数が、WorksheetFunctionの中とApplicationの中に二つあるという事です。

Excel VBA コード

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

ExcelマクロVBA入門等の対応ページ

第87回.WorksheetFunction(ワークシート関数を使う)
・ワークシート関数の使い方 ・WorksheetFunctionで使用できる関数 ・個別の関数の使い方 ・関数の結果(戻り値) ・WorksheetFunctionの使用例. ・検索系の関数での日付の扱い ・WorksheetFunctionのエラー対処 ・最後に

第60回.エラー処理(On Error)
・マクロVBAのエラー発生例 ・エラー処理のステートメント ・実行時エラー関連記事
第61回.「On Error GoTo」と「Exit Sub」
・On Error GoTo 行ラベル ・Exit Sub ・On Error の有効範囲とその動作について ・最後に
第62回.「On Error Resume Next」とErrオブジェクト
・On Error Resume Next ・Errオブジェクト ・On Error Resume Next の使用例 ・「On Error Resume Next」の最後に

VBEの使い方:ショートカットキーとコード編集
・VBEショートカット一覧 ・ショートカットキーの具体的な使い方 ・コメントアウト ・ショートカットとコード編集の最後に



同じテーマ「マクロVBA再入門」の記事

第7回.セルの値によって計算を変える(Ifステートメント)
第8回.表範囲をまとめて消去する(OffsetとClearContents)
第9回.関数という便利な道具(VBA関数)
第10回.ワークシートの関数を使う(WorksheetFunction)
第11回.分からない事はエクセルに聞く(マクロの記録)
第12回.エクセルの言葉を理解する(オブジェクト、プロパティ、メソッド)
第13回.セルのコピペ方法を知る(CopyとPaste、さらに)
第14回.セルの書式を設定する(NumberFormatLocal,Font,Borders,Interior)
第15回.手作業で出来なければマクロは書けない
第16回.エクセルの機能を上手に使う
第17回.セルにブック・シートを指定する(Workbooks,Worksheets,With,Set)


新着記事NEW ・・・新着記事一覧を見る

抜けている数値を探せ|エクセル雑感(2022-07-01)
.Net FrameworkのSystem.Collectionsを利用|VBA技術解説(2022-06-29)
迷路ネコが影分身の術を体得したら…|エクセル雑感(2022-06-27)
迷路にネコが挑戦したら、どうなるかな…|エクセル雑感(2022-06-26)
サロゲートペアに対応した自作関数(Len,Left,Mid,Right)|エクセル雑感(2022-06-24)
「マクロの登録」で登録できないプロシージャーは?|エクセル雑感(2022-06-23)
オブジェクトのByRef、ByVal、Variant|エクセル雑感(2022-06-22)
コメントから特定形式の年月を取り出す|エクセル雑感(2022-06-19)
4,9を使わない連番作成|エクセル雑感(2022-06-17)
連番を折り返して出力|エクセル雑感(2022-06-16)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.マクロって何?VBAって何?|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ