Excelを勉強しましょう EnjoyExcel

アイコンの機能や関数の説明、VBA初心者向けの勉強方法について情報掲載しています。関数、VBAともに理解が進むように出来るだけ事例を交えて説明しています。

VBA⑬ VBAでは関数とワークシート関数って分けて使います。

f:id:EnjoyExcel:20210628130926p:plain

 

関数には関数扱いするプログラム自体を指す関数とワークシート関数があります。そうやって分けて書いている本もあるので私も分けて紹介してみようと思います。

 

前回は繰り返し処理について紹介させていただきました。 

enjoyexcel.hatenablog.com

 

ではまず関数扱いするプログラム自体を指す関数というのはどういったものなのかという事を書いていきます。

 

Sub ~ EndSub までが1つのプログラムだと紹介してきました。プログラムというと伝わらない事があるかもしれませんので正式にはプロシージャと言います。1つのサブプロシージャが・・・という感じで使います。

 

たとえば最終行や最終列を認識するようなコードはプロシージャ毎に書くと面倒だしコード自体も長くなります。そんな時の為に別で最終行と最終列を確認するプロシージャを用意してしまいそれを関数と呼んでいます。

毎回関数を参照しに行く事で結果を取得する様にして同じコードを書かないというのが狙いです。

 

結果を返すという意味では関数ですが1つのプロシージャになりますのでワークシート関数と同じ様に考えるとちょっと違和感ありますよね。書籍によってはこれを関数と呼ぶ場合もありますのでご注意ください。

 

別プロシージャを関数として使用するには引数等他の情報についても説明が必要ですがあまり行き過ぎると初級編を超えてしまうのでひとまずここまでにします。興味ある方は調べてみてください。

 

次にワークシート関数です。

これは御馴染みのセルに書く関数です。

コードの先頭に WorksheetFunction. と書くと使える関数が出てきます。

以下はVLOOKUPを探している最中の画像です。

f:id:EnjoyExcel:20210628112749p:plain

というわけでワークシート関数も使えます。

 

ただし・・・

・書き方はVBAの作法で。

・ワークシート関数の中でも使えない関数もある。

VBA側でも用意された関数がありそれにはWorkSheetFunction.は書かない。

 

またいつもの例外ばっかですね。

でも1つ1つ使いながら覚えていくしかないです。持久戦だと思って対応してください。先に書かないといけなかったのですが「サクッとVBA」って考えている人は考えを変えてください。かならず量をこなす時が来てやらないと絶対知識は定着しません。

 

では話を元に戻します。

VLOOKUP(第1引数,第2引数,第3引数,第4引数)これがワークシート関数のVLOOKUP

です。

 

第1引数

対象範囲の中から検索したい文字、数値(セルの場合もある)

 

第2引数

対象範囲 選択範囲の1番左に第1引数がくるように範囲を指定

 

第3引数

第2引数の何列目を持ってくるのかを指定

 

第4引数

完全一致か類似か(ここはほぼ完全一致ですね False です)

 

こんな感じですね。これをVBAで書くと以下のようになります。

f:id:EnjoyExcel:20210628133540p:plain

ワークシートの画面は以下のようなレイアウトです。

個人情報風のデータは「なんちゃって個人情報」で生成したダミーです。

f:id:EnjoyExcel:20210628144406p:plain

A6からの表ですがB列に名前が居るのでB6を一番左の列にとったリストを第2引数にしてB2の値がB列にいたらいたらC2にI列の部署をもってきてくださいという関数です。結果は「経理」となりますね。

 

最終行はA列で認識して最終列は6行目で認識・・・って感じです。

enjoyexcel.hatenablog.com

 

あとは実行ボタンやC2の値をクリアするボタン、B7からI列の最終行までを一括で削除するボタンを作ってあげたら簡易的な検索ツールが出来ます。

 

ベースとなるデータ用意して名前をB2に書いてボタン押すだけで部署が分かるので誰でも使えますね。

 

ただですね・・・VLOOKUP関数をシートで使った際 #N/A が出る事ってないですか?VBAでこれを出してしまうとコード自体がエラーですってメッセージボックスが出てきてしまうんですよ。 以下のようなメッセージボックスが出ます。

f:id:EnjoyExcel:20210628133155p:plain

実行時エラー1004はいろんな要因がありますが1つは取得したいデータが存在しないというものがあります。文言から推測しても結果が取得出来ないんだ=多分データないってことね。と判断します。

 

VBAに限らずですがプログラミング言語のエラーメッセージって結構不親切でピンポイントでどこが悪くて理由は○○ですって言ってもらえることは少ないです。

 

エラーメッセージとどう向き合っていくかも課題です。都度調べることが必要になります。

 

話を戻します。エラーメッセージが出てしまうという事でしたね。

検索がストップしてしまいメッセージボックスが出てくるので知らない人が使うと「エラーが出ます」ってなりますね。困ります。

 

では上記の改善を含め最後に今まで勉強したことをある程度使ってデータ抽出が出来るようなものを用意してみたいと思います。