お知らせこの記事は初心者用マクロ学習講座｜勉強範囲を限定＆12本の動画で早く効果を実感の関連記事です
ワークシート関数の使い方を勉強しよう
早速結論から入ります。マクロでも関数が使えます。イチからコードを書いて関数と同じような結果を得ようとすると非常に複雑なコードを書かないといけないです。
しかしマクロでも関数が使えるならこの問題は解決したも同然です。積極的に使っていきましょう。
- コードがシンプルになるのでコードの量が減ります
- コードがロジカルに単純化されるので間違いが減ります
このように基本的には良い事しか無いです。
コードを書くうえで多少のデメリットもあります 後半の解説記事内で紹介しますね
動画では「関数と言えば？」でお馴染みのVLOOKUP関数を紹介しています。セルに書くタイプの関数とVBAの中で使うVLOOKUP関数を比較出来る様に用意してみました。
セルに書く関数もVBAの中で使う関数も多少違いはありますが建付けは同じだという事が良く分かる動画になっています。是非ご覧ください。
加えて関数にはエラーが付き物です。セルに書くタイプの関数では#N/Aの様にエラーが表示されるのですがVBAの場合はエラーが表示されるとコードが止まってしまいます。
エラーをエラーのままにしない様な対策をとる事でコードが止まってしまうのを回避出来ます。動画には入ってないですが記事内でエラー対策もしっかり用意しておきました。
では勉強を始めていきましょう。
記事の難易度
このシリーズのMAXは3なので一番難しいという事になります。今回と次回は3で設定しています。
オンラインミーティング
私独学で勉強してVBAを習得しました。現在は個人でVBAの講師をしております。興味ありましたらリンク先よりお問い合わせください。
前回記事
前の記事VBAIfステートメントとメッセージボックス２つの機能を組み合わせる
この記事を読むとわかる事
VBAでワークシート関数が使える様になります。
- ワークシート関数の使い方がわかります
- 関数のエラー対策がわかります
- VLOOKUP関数の使い方がよりわかる様になります
マクロ
動画ではVLOOKUP関数を使っていますが他の関数でも使い方は同じです。最後にVLOOKUP関数のエラー対策について勉強します。
２分動画
セルに書くタイプの関数とVBAのワークシート関数の両方でVLOOKUP関数を書いて実行してみました
動画の中からさらに要点を絞って解説していきます。
ワークシート関数
Microsoftさんの資料を読んでいきますと2022年11月現在では387個のメソッドが紹介されています。
いきなりメソッドの話をしましたがVBAでワークシート関数を使う際はWorksheetFunctionオブジェクトに紐付く各関数名のメソッドを利用します。
書き方
基本はWorksheetFunction.関数（引数）の型です。メソッドの使い方と同じです。オブジェクトの直下にメソッドを書いてかっこ内で引数を指定するという型です。
引数の種類、内容もセルに書くタイプの関数と同じですがVBAの作法でコードを用意する必要があります。
動画内の画像を使って関数を確認
画像を用意しました。動画内で使ったものと同じです。関数の書き方を確認していきましょう。ここではVLOOKUP関数を使ってセルに書く関数とワークシート関数を比較しています。
セルに配置された情報と使った関数は以下の通りです。セルF8には関数を直書きしています。セルF9にはVBAのワークシート関数を使って戻り値を記入しています。
セルF8、F9共に「横浜市」が返ってきます。
画像にある2つの関数は書き方が微妙に違うだけで内容に差は無いです。
VLOOKUP関数
不要かもしれませんが関数自体の説明をしておきます。画像の内容が見易くなります。
VLOOKUP（検索値,範囲,列番号,[検索方法]）
| 番号 | 引数 | 詳細 |
|---|---|---|
| 1 | 検索値 | 検索の対象となる値 |
| 2 | 範囲 | 検索範囲 |
| 3 | 列番号 | 検索範囲の中で戻り値として値を返してほしい列 |
| 4 | 検索方法 | 検索方法 近似一致か完全一致かを選択 |
例外
ワークシート関数と同じ様にVBAにも専用の関数が用意されています。VBA関数です。前回記事のメッセージボックス（MsgBox）などもVBA関数の一部になります。
他にもMID関数、Rnd関数、Int関数などはVBA関数です。これらの関数を使う際はWorksheetFunctionの記述が要らないです。メッセージボックスの記事でも使ってなかったですよね。
関数の分別方法
ではどれがワークシート関数でどれがVBA関数なのか・・・これは考えない様にしましょう。理由は「多すぎて分からないから」です。
ワークシート関数は約400個あります。VBA関数は全部分からないのですがおそらく100以上あるのでかなり多いです。
とはいえ考えないと書けないですよね。これがベストではないですが考えずにコードを書く方法を紹介します。
VBAのコード内でWorksheetFunction.と書くと入力候補が表示されます。そこで表示される関数はワークシート関数です。
入力候補に表示されないものはVBA関数ですのでWorksheetFunctionを書かずに使いましょう。
ここから「コードを書く時のデメリット」までは少し難しい話をします。必要に応じて読んでください。
画像を用意してみました。余談ですがWorksheetFunctionオブジェクトの上位モジュールをあえて記入しています。
WorksheetFunctionオブジェクトを遡ると最終的にはExcelライブラリに属しています。変わってVBA関数のMidメソッドはVBAライブラリに属しています。
MsgBox関数はまた違う場所に所属しています。ワークシート関数以外でも関数と呼ばれる機能が複数存在します。
何故紹介したのかという理由ですがワークシート関数とVBA関数は所属が違うという事を説明する為に書きました。成り立ちが違うので書き方が違うという事です。
続いてコードを書く時の注意事項です。頭からコードを書くと長いので大変です。VBAではコードを省略して書いても通じるケースがありますのでこれを使いましょう。
簡単なところだとRangeオブジェクトのValueプロパティは使用時に書かなくてもエラーになりませんよね。
ワークシート関数はWorksheetFunctionから書き始める事が可能です。VBA関数はMid・・・の様にメソッドから書き始める事が出来ます。必要に応じてコードを書きましょう。
コードを書く時のデメリット
VBAでワークシート関数を使う時は関数の引数まで全て把握してないとコードが書けません。理由はコードを書いている時に引数の詳細が表示されない為です。
VLOOKUP関数は引数が4つあるのですが以下画像の様にArg1,Arg2,Arg3[Arg4]という表示になってしまいます。引数の数は分かるのですが詳細までは分かりません。
よってセルに書く関数が分かってないと実質使えないという事になるのですがそもそも関数の建付けが分かってない人は関数が使えないしコードも書けないです。
全部覚えてない人も書く時に調べれば解決しますのでデメリットを感じる人は少ないかもしれませんが紹介させていただきました。
エラー対策
画像にはセルF8に書かれた関数が表示されています。結論から申し上げますとこのVLOOKUP関数の戻り値はエラーになります。理由は「東京都は検索範囲に居ない為」です。
セルF8には「#N/A」が表示されます。
VBAで同じ事をやろうとするとセルに値は入りません。セルに戻り値が入力される前にエラーが出てコードの実行が中断します。
良く出来ている・・・というべきか融通が利かないというべきか賛否両論なのですがとにかくコードは止まってしまいます。
こんな感じでエラーメッセージが出ます。自分が使うだけなら検索値がない時はエラーが出ると分かっているのですが自分以外の人が使うと「大変だ」・・・となってしまいます。
余談ですが「実行時エラー」というのはコードの書き方に間違いは無いけど実行出来ないというエラーです。エラーの種類についても別途記事を書く予定です。
話を戻しましょう。エラーを回避する事を考えます。エラー自体は無くせないのでエラーが出る前に落しどころを作ってあげましょう。
具体的には以下のようにコードを用意します。
画像のコードを用意しました。コピペで使えますよ。
コードはWクリックで選択出来ます。その後「Ctrl+C」でコピー、「Ctrl+V」で貼付けです。
Sub VLOOKUP関数()
If WorksheetFunction.CountIf(Range("B4:B6"), Range("E4")) = 1 Then
Range("F9") = WorksheetFunction.VLookup(Range("E4"), Range("B4:C6"), 2, False)
Else
MsgBox "検索値が検索範囲に居ません", , "お知らせ"
Exit Sub
End If
End Sub
エラー対策のコードを解説
まずは何をやっているのか紹介しておきます。シリーズを見てきた人なら分かるかもしれません。今まで勉強してきた事をフルに使いますよ。
まずはワークシート関数のCountIfメソッドを使います。事前に検索範囲に検索値が居るのか調べます。
検索範囲に検索値が1つ存在したらワークシート関数のVLOOKUPメソッドで戻り値を取得します。
検索値が検索範囲に居なかったらメッセージボックスでエラー内容をお知らせしプロシージャから抜けます。
こうする事でエラーをエラーのままにせず何らかの落しどころが用意出来ます。検索範囲がもっと多い場合は検索値が複数存在する事も想定されます。
そんな時は以下画像のようなメッセージボックスを出してあげると良いかもしれません。
Ifステートメントのネストを利用するともっと複雑に結果を用意できると思いますが最初のうちはこれで十分です。
やらないといけない事は「エラーでコードの実行が止まらない様にする事」です。
まとめ
ワークシート関数の使い方について紹介しました。最後はコードが難しくなりましたね。しかし実務をVBAに置き換えるにはこれぐらいのコードは書けるようにしたいものです。
- ワークシート関数の使い方
- エラーへの対策方法
- エラー対策を含めた関数の書き方
マクロ
動画を見て記事を読んで「はい出来た」という人は絶対に居ません。何回か自分でコードを書いて試行錯誤して初めて知識を身に付けることが出来ます。
私はあくまでVBA習得に向けた最短距離を提示しているだけです。必ず量をこなさないといけない時が出てきますのでそこはしっかり時間をかけて勉強してください。
次回が最後になります。繰り返し処理とIfステートメントを組み合わせて使います。動画も今回までで11本、計22分を消化しました。もう少しお付き合いください。
