エクセルのワークシートで使う関数とマクロの関数は別です
マクロを構築するにあたり関数はとても重要な機能です。
セルに書く関数を使った事がある方ならコードを書く際も関数を使いたいと考える人はいるはずです。
今回はマクロの中で関数を使ってみます。
関数は使用方法から2つに分ける事が出来ます。混乱しない様に分けて考える必要があります。
まず関数の定義を確認しておきましょう。
続いてワークシート関数を使った事例を紹介させていただきます。
前回の記事
繰り返し処理について紹介させていただきました。
ここまでくるとプログラミングをやっているなという感じが出てきますよね。
本記事での課題
今回の課題です。
2つの関数の違いについて理解しよう
関数の定義を確認
関数は大きく分けて以下の様に分ける事が出来ます。
- 1つのマクロを関数として扱う時・・・関数
- ワークシート関数を使う時・・・ワークシート関数
1つずつ確認していきます。
CASE1：1つのマクロを関数として扱う時
Sub ～ End Sub までが１つのプログラムだと紹介してきました。
プログラムというと伝わらない事があるかもしれませんのでここで呼び名を直します。
正式にはプロシージャと言います。「１つのサブプロシージャが・・・」という感じで使います。
例えば最終行や最終列を認識するコードはプロシージャ毎に書くと面倒だしコード自体も長くなります。
そんな時は別で最終行と最終列を確認するプロシージャを用意して都度参照させるような建付けにしてます。
イメージとして上記画像を用意しました。
緑の部分が今自分が書いているコードで青の矢印方向に命令が進むと仮定します。
上から順にコードが実行されると黄色の範囲内にあるA地点とB地点を通過する事になります。
黄色枠の範囲内では仕事の対象となる表の最終行と最終列を認識する予定です。
その際各地点の命令は外出しした赤の箱の中にある銀色の箱「最終行」と「最終列」に任せる事にします。
各箱にはそれぞれの要素を導き出す為のサブプロシージャが居ます。
結果を緑のプロシージャの黄色枠内のAとBに返します。
この赤い箱自体を「関数」と呼びます。
セルに書く関数からすると非常に大掛かりなものに見えます。
ただし引数（ひきすう）を与えて結果を返すという観点からするとこれも関数です。
効果としては都度使うコードは複数プロシージャ間で共用する事が出来ます。
今回の事例からすると最終行と列を確認するコードは毎回書かなくて良いです。
結果を返すという意味では関数ですが1つのプロシージャになります。
ワークシート関数と同じ様に考えるとちょっと違和感ありますが慣れてください。
具体的にどうやって使うのかというのは初心者の勉強範囲から外れますのでここまでにします。
関連記事
以下リンクはコードの書き方について解説した記事です。
今回の1～14のシリーズ内では説明できない事を書いています。基本的には読み飛ばして下さい。
少し難しいので勉強が進んだ方はチャレンジしてみてください。
関連記事マクロはどのモジュールに書くのかコード次第なのか？違いを教えて
あくまで書籍等情報を仕入れる際混乱しない様にこんな考え方もあるという説明をしました。
興味ある方は調べてみてください。
CASE2：ワークシート関数として使う時
次にワークシート関数です。これは御馴染みのセルに書く関数とほぼ同じです。
コードの先頭に WorksheetFunction. と書くと使える関数が出てきます。
以下はVLOOKUP関数を探している最中の画像です。
このようにセルに書く関数もいくつかは使える様になってます。
ただし以下のような決まりがあります。
- 書き方はVBAの作法で
- ワークシート関数の中でも使えない関数もある
- VBA側でも用意された関数がありそれにはWorkSheetFunction.は書かない
またいつもの例外ばっかですね。
でも1つ1つ使いながら覚えていくしかないです。
持久戦だと思って対応してください。
ワークシート関数を使った例を紹介
VLOOKUP関数をVBAで書くと以下のようになります。
型（形）はセルに書いている関数と同じですが作法はVBAです。
ちょっと混乱しますがゆっくり見ていただけると分かると思います。
このVLOOKUP関数を書いたコードは以下のシートに対して指示をしています。
どうやって稼働しているのか見ていきましょう。
ワークシートの画面は以下のようなレイアウトです。
個人情報風のデータは「なんちゃって個人情報」で生成したダミーデータを使っています。
A6からの表ですがB列に名前が居るのでB6を一番左の列にとったリストを第2引数にしています。
先程のVLOOKUP関数が示しているのはこのような内容です。
「B2の値がB列にいたらいたらC2にI列の部署を持ってきてください」というコードです。
結果は「経理」となりますね。
最終行はA列で認識して最終列は6行目で認識という感じです。
仕上げで実行ボタンやC2の値をクリアするボタン、B7からI列の最終行までを削除するボタンを用意します。
これで簡易的な検索ツールが出来ます。
これならVLOOKUP関数が分からない方でも使えますね。
残された課題
ただし残問題があります。
VLOOKUP関数をシートで使った際 #N/A が表示された事ありませんか。
VLOOKUP関数を書ける人なら誰もが1度は見た事あると思います。
マクロでこれを出してしまうとコードがエラーで止まります。
以下のようなメッセージボックスが出ます。
実行時エラー1004はいろんな要因があります。
1つは取得したいデータが存在しないというエラーです。
文言から推測しても結果が取得出来ないんだ＝「データがない」と判断します。
マクロの建付けを知らない人がこのデータを使うとどうなるでしょうか。
きっと「エラーが出ます」と問合せがきてしまいますね。改善の必要があります。
VLOOKUP関数によるエラーを対策する
検索対象となるデータがデータベースに無い時にはエラーになってしまいます。
VLOOKUP関数を使ったコードは不完全なコードでした。
エラーが出る原因からすると今後も起こる可能性があります。ではどうしたら良いでしょうか？今まで勉強したことで使えそうなものがあるはずです。
IF文を使って対策できる
エラーが出るときと出ないときの条件は明確なので条件分岐を使って考えてみましょうか。
データがあるときはVLOOKUPを実行します。
データが無い時はVLOOKUPを実行せず「データが無いよ」と言ってあげれば良さそうな気がします。
という事は条件分岐が使えそうな感じがしてきますね。IF文採用しましょう。
CountIf関数を使って対策する
データの有無を確認するにはいろいろな方法があります。
今回もワークシート関数を使いましょう。CountIf関数を使います。
CountIf関数の説明
建付けは以下参照ください。
CountIf（第１引数,第２引数）
第1引数
関数実行時の対象となるデータベースの範囲です。
第2引数
データベースの中で検索したい文字です。
対策方法
以下のような条件を設定します。第1引数にデータベースの値を取ります。
第2引数に検索対象の値を用意します。
これでデータベースの中に検索対象の情報が居るのか確認出来ますね。
最初の引数にデータベースのキーが居る列、次の引数で検索ワードを指定します。
上記をIF文の条件にしましょう。
TRUEだったらVLOOKUP関数を実行させます。
FALSEだったら”該当なし”等の言葉を出してあげればエラーは出ないです。
これでエラーが出ない様になりました。
エラー対策済みのコードを紹介
以下コードをご覧ください。
（コードをコピーする際はOption Explicitが重複しない様に注意してください）
Option Explicit
Sub VLOOKUP()
'************************************************************
'VLOOKUP関数を使うがエラー対策が必要
'先にCountIfでデータの有無を確認しTrueの際は
'VLOOKUP関数を実行する様にした
'************************************************************
'変数の定義（日本語使用しています）
Dim 最終行 As Long
Dim 最終列 As Long
'************************************************************
'最終行と最終列を取得
最終行 = Cells(Rows.Count, 1).End(xlUp).Row
最終列 = Cells(6, Columns.Count).End(xlToLeft).Column
'************************************************************
'セルB2の名前がリストの中に1つ以上あったら・・・
If WorksheetFunction.CountIf(Range(Cells(6, 2), Cells(最終行, 2)), Range("B2")) > 0 Then
'VLOOKUP関数を実行
Range("C2") = WorksheetFunction.VLOOKUP(Range("B2"), Range(Cells(6, 2), Cells(最終行, 最終列)), 8, False)
Else
'セルC2に 該当なし を展開
Range("C2") = "該当なし"
End If
End Sub
まとめ
VLOOKUP関数は単体だと業務の仕様に対応出来ない事があります。
関数を組み合わせて使う事でエラーを回避してください。
- 関数の定義
- マクロもワークシート関数が使える
- 関数のエラーでマクロは止まる
- エラー回避も関数を使う
Enjoy Excel
次回が最後です。
今まで勉強したことをある程度使ってデータ抽出が出来るようなものを用意します。
