「Excel」VBAコードは短くわかりやすくかくために!

( ノ゚Д゚)こんばんは、こんにちは、おはようございます。おやすみなさい! とっちーです。

本日は、VBAのコードを短くするためのコツの一つ同一処理を纏める方法と、ファンクションプロシージャの仕組みについてのご紹介です、楽しくお話をしたい!と思います。

 

 

ExcelVBAのプロシージャの種類 その中の4つ

 

Sub~End_Sub(サブ)プロシージャ

 

Subプロシージャは、その中に必要なコードを記述すればすべての処理を終わらせることが出来るプロシージャになります。

そのため、長くなればなるほど読みづらいコードが出来上がってしまいます。

 

そこで、こまめにSubプロシージャを作成しCallステートメントで呼び出し合いをしましょう。見やすく、修正しやすいコードが作成できます。

 

また、Subプロシージャーは、Functionプロシージャと違い「値を返してくれない」と言われていますが、Callステートメントで変数の授受を取れば値をきちんと返してくれます

 

又改めてご紹介いたします。

 

 

Function~End_Function(ファンクション)プロシージャ

 

例えば、Excelのセルに入力してで使用する関数があります。

=COUNTIF(範囲,検索条件)などの指定された範囲に含まれるセルのうち、検索条件に一致するセルの個数の戻り値を返してくれるのですが、

=COUNTIF(範囲,”<>”)を

Functionプロシージャで、以下のように作成してみますと

Function Ccnt(ByRef 範囲 As Range) As Long

Ccnt = CLng(WorksheetFunction.CountIf(範囲, “<>”))

End Function

 

 

出来上がったユーザー定義関数

=Ccnt(範囲)で処理させる関数が出来てしまうんです。

 

また、消費税の計算も=ROUND(選択セル番地*1.08,0)を同様に作成してみますと

 

 

出来上がったユーザー定義関数=消費税(金額)で処理させる関数が出来てしまうんです。

 

(ちなみに消費税と金額の部分は、認識しやすくしているためなので日本語でなくても大丈夫です。)

 

Functionプロシージャで作成することによりユーザー定義関数として登録されて、セルで関数として活用できるんです。

 

また、Functionプロシージャは値を戻すということで活用されます。

「実はSubプロシージャでも値を戻させる方法はあります。Option Explicitで変数宣言をしてみましょう。」

 

Functionプロシージャは、Subプロシージャ内に記述するコードのうち同一処理を複数記述したコードを簡素化するときに役立ちます。

 

長いコードの中に同一の処理コードが存在した場合にFunctionプロシージャに同一処理のコードを記述し、他のプロシージャからデータを投げかけ処理をしてもらって答のデータを返してくれる仕組みづくりが出来るんです。

 

イベントプロシージャ

 

イベントプロシージャは、WorkbookやWorksheetなどオブジェクトに対しアクションを起こさせたいときに使用します。

イベントプロシージャの構文について。

 

アクションを起こさせたいオブジェクトをプロジェクトエクスプローラで選択後、プロシージャを選択できるボックスからイベントを選択すると、自動的にコードウインドウに表示されます。

 

表示された型式はPrivate Sub オブジェクト名_イベント名(引数)に設定されます。

必ず作成を行う場合は上記の方法で作成を行うようにしましょう。

他のプロシージャを「Callステートメント」で呼び出すことも可能です。

詳しくは、改めてご紹介します。

 

Propertyプロシージャ

 

Propertyプロシージャは、クラスモジュールの中に作成するプロシージャです。

図形やフォームボタンなどオブジェクトのプロパティを定義するためのプロシージャになります。

大きさや色などの様々な定義(仕様範囲内で)を行うことが出来ます。

 

(はてな君)
プロシージャって呼び名がついてるの?

 

(あいちゃん)
そうなんです。あっ!・・

 

(えっちゃん)
あいちゃん、代わるね! はてなっち、実は呼び名があるんだ!コードをなるべく短く綺麗につくる時とかに便利なプロシージャがあるんだ。

 

(はてな君)
Functionプロシージャのこと?

 

(えっちゃん)
そう、今日はその短くできるプロシージャ Functionプロシージャについていっしょにみてみるかい!

 

 

(はてな君)
は~い!

 

 

Functionプロシージャの使用例

 

 

シート上にある各A列からE列の「z」の位置をしらべるコードを作成してみました。

 

 

まずはSubプロシージャのみで作成したコードになります。

 

 

このコードを実行すると以下のようになります。

動画をご覧ください。

 

 

 

 
では、続いてFunctionプロシージャを用いて作成したコードになります。
 
 

 

以上のように「Functionプロシージャ」を活用するとコードもすっきりします。いかがでしょうか。

 

Functionプロシージャの記述方法

 

Function ROWRc(  ByRef ROWR) As Range

Set ROWR = WSS1.Columns(COlu).Find(what:=ROWA, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If ROWR Is Nothing Then
MsgBox “最終行にzの文字がないため終了します”
Else
Set ROWRc = ROWR
End If

End Function

 

構文の説明

 

Function ROWRc( ByRef ROWR) As Range

「Function プロシージャ名( ByRef (参照渡し) 引数( 引数 As データ型)) As 戻り値の オブジェクト型

 

※引数は引数だけを記述しても動作する場合もありますが、必ず( 引数 As データ型)で記述をすると何のデータ型なのか判断が一目でつきます。

(今回はコードの最初Option Explicitで一括宣言をしていますが、定義場所に記述を行うのが判断しやすいと思います。)

次に「処理するコードを記入します」

 

オブジェクト型は、「Set プロシージャ名 = 引数」のSet文を忘れないようにしましょう。

Set ROWRc = ROWR

End Function

ちなみに戻り値の渡し方には、参照渡し「ByRef」と値渡し「ByVal」の2種類があります。

 

参照渡しByRef

 

ByRefを設定すると引数を差出側のプロシージャから受け取り、Functionプロシージャで引数を変更すると差出側プロシージャへ受け渡すとき、変更した引数を渡すように出来ます。ByRefを使用するときは省略も可能です。

参照渡しの場合、贈り物に箱と魚(箱の中身)を頂いたとします。

 

わ~い魚だ、ありがとう。

そうだ、お礼にお肉をお返ししよう。

頂いた箱にお肉を入れて頂いた方にお渡しするんです。

そうすると頂いた方は、箱は同じだけど中身はお肉になっているんですね。

なので、もうお気づきだと思いますがオブジェクト型の場合は参照渡しをしないと中身の確認が出来ないってことになります。

値渡しByVal

 

ByValを設定すると、差出側のプロシージャへ受け渡すとき、変更した「値」を提供するだけですので、差出側の引数の変更はしません。

値渡しの場合、贈り物に箱と魚(箱の中身)を頂いたとします。

わ~い魚だ、ありがとう。

そうだ、お礼を伝えよう。

お魚ありがとうって頂いた方にお礼するんです。

 

こんなの入ってましたよって、そうすると頂いた方は、差し上げた贈り物の中身の情報を再確認できるんですね。

それと、贈り物を返されたくない、つまり、違う中身を送らないでねってことで。

 

渡したデータを改造しないで返してねって時は、ByValの指定をお忘れなく。

 

まとめ

 

 

今回は、VBAのコードを整理する方法と、ファンクションプロシージャについてお話してみました。

ファンクションプロシージャじゃなくても出来るんだけど、折角だからみんなで仲良く遊びましょうってことで使っちゃいました。

使い方次第では大変便利です。関数つくれるし。

ただし、VBAを作成する際は、こまめな保存と確認をしてからSub/ユーザーホームの実行をするようにいたしましょう。

 

 

今日も安心してプロシージャを作成できるように

そんな、お手伝いが出来ることを願っています。

 

 

楽しくお仕事できたらなって。そう思っていただけたら幸せです。

 

ま、何でも楽しくしていただけたら、うれしいです。ものすごく。