Excel VBA 標準モジュールは、複数作成することができます。

Excelで標準モジュールを作成(挿入)する時には、複数作ることが可能です。

試しにもう一つ標準モジュールを作成してみてください。

excel-vba-41.gif

複数の標準モジュールを作成すると、役割ごとにユーザー定義関数を分類できるので、とても便利です。

また標準モジュール名は、連番で自動的に付けられます。もちろん自由に変えることもできます。

標準モジュールの名前を変更するには、プロジェクトエクスプローラで、対象となる標準モジュールを選択し、プロパティで「オブジェクト名」を変更します。

excel-vba-36.gif

でも今回は名前を変更せずに、そのままにしておいてください。

これまで標準モジュールに作成したユーザー定義関数を利用するには、以下のように呼び出しました。


=calcGankin(1000,0.5)


でも正確には、次のように書きます。

=Module1.calcGankin(1000,0.5)


この二つの違いは、関数名の前に「標準モジュール名.」があるかどうかだけです。

calcGankin()という関数は、一つしか無いので、標準モジュール名を省略できたわけです。

実は標準モジュールが複数ある場合は、同じ名前のユーザー定義関数を作成できます。

まったく同じ関数名で、異なった処理を行ない、戻り値を返すことも可能です。

そうなると関数名だけでは、どちらの関数を指しているかわかりません。そこで必ず標準モジュール名から指定して、区別する必要があります。

同じ関数名を作らなければ、標準モジュール名を省略しても大丈夫です。

Excelの既存の関数と同じ名前も避けたほうが無難です。

でもあえて既存の関数名と同じにして、使いやすいように独自の関数をVBAで定義する手もあります。

例えば COUNT() という関数は既にありますが、標準モジュールを使って、独自に定義することが可能です。そんな時は、もちろん標準モジュールから指定します。

また不要になった標準モジュールは、削除(解放)することができます。

削除したい標準モジュールをマウスで右クリックし、コンテキストメニューから、「Module の解放」を選択します。

excel-vba-38.gif

するとModuleをエクスポートするかどうかのメッセージが表示されるので、「いいえ」を選択します。

excel-vba-39.gif


【ワンポイント】
標準モジュールを解放する時に、「はい」を選択すると、ファイルとしてエクスポートすることが出来ます。

エクスポートしたファイルは、別のExcelにインポートすることが可能です。

あなたが作成した標準モジュールを、他の人にも利用させてあげたい時に便利です。



Excelで標準モジュールにユーザー定義関数を作成するには?

Excelのワークシート上から呼び出して使えるユーザー定義関数を作成するには、標準モジュールにVBAでプログラムを書く必要があります。

前回までは、ダウンロードしたファイルを使用していました。理由は、最初にユーザー定義関数が便利なことを、実感していただきたかったからです。

そのため順番が逆になってしまいましたが、標準モジュールにユーザー定義関数を作成する方法について説明します。

今回はダウンロードしたExcelのファイル「gankin.xls」を使わずに、新たにファイルを作成します。


【1】新たにExcelのファイル「gankin.xls」を作成します。

excel-vba-26.gif


【2】新たに作成したファイル「gankin.xls」を開きます。

*ダウンロードしたファイルではないので、注意してください。


【3】「gankin.xls」が開きました。

excel-vba-27.gif

もちろんこの状態では、通常のExcelファイル(Book)と変わりません。


【4】VBEを起動します。

*「Altキー」+「F11キー」


【5】「gankin.xls」のVBE画面が表示されました。

excel-vba-28.gif

この画面を使って、プログラミングを行ないます。

VBEとは、Visual Basic Editor の略で、統合開発環境のことです。VBEは Microsoft社の Office製品で、共通して使えます。


【6】プロジェクトエクスプローラには、まだ標準モジュールがありません。

excel-vba-29.gif


【7】プロジェクトエクスプローラ内をマウスで右クリックし、コンテキストメニューが表示されたら、「挿入 → 標準モジュール」を選択します。

excel-vba-30.gif

プロジェクトエクスプローラ内なら、どこでもかまいません。

またVBEのメニューからでも、標準モジュールが作成できます。

・「挿入 → 標準モジュール」
excel-vba-31.gif

結果は同じなので、好きな方法を覚えてください。


【8】プロジェクトエクスプローラ内に「Module1」という標準モジュールが作成されました。

excel-vba-32.gif

標準モジュールは複数作成できるので、階層構造になっている点に注目してください。

またVBEの画面右側には、「コードウィンドウ」が表示されます。

これは「Module1」に対してのコードウィンドウです。今、プロジェクトエクスプローラでは、「Module1」が選択されているはずです。

ここにVBAでユーザー定義関数を作成します。

標準モジュールとは、名前の通り、標準のプログラムのことです。

標準モジュールに作成したプログラムは、他のプロシージャやExcelのワークシートから、共通して使うことができます。


【9】コードウィンドウに「Option Explicit」が表示されていますか?

excel-vba-33.gif

もし表示されていない場合は、加えてください。

最初から表示されるかどうかは、Excelの設定によります。加えれば問題ありません。

Option Explicitは、変数の宣言を強制するためのものです。

この1行を加えると、プログラムの中で変数を使う時には、必ず宣言が必要になります。

変数を宣言することで、バグ(誤り)の入り込む余地が減りますから、必ず加えるようにしてください。


【10】以下の図のように、VBAプログラムを入力してください。

excel-vba-34.gif


Public Function calcGankin(risoku As Long, kinri As Single) As Currency
  Dim gankin As Currency
  Const TAX As Single = 0.2
  
  gankin = risoku / ((kinri / 100) * (1 - TAX))
  calcGankin = gankin
  
End Function


このプログラムは、ダウンロードしたファイルの内容と、全く同じものです。


【11】ここまでの作業を「保存」しましょう。

excel-vba-35.gif

VBEで行なった作業は、VBE側で保存します。Excel側の保存とは別なので、注意してください。

以上でプログラムは完成です。


【12】あとはダウンロードしたファイルの時と同様に、Excelのワークシート上で、動作確認してください。

セルに以下のような計算式を入力してください。

・例

=calcGankin(1000,0.5)


=calcGankin(5000,1.2)


=calcGankin(80000,0.85)



【ワンポイント】
Excelのファイルを一度閉じ、再度開く時には、既にプログラムを書いたので、メッセージが表示されるようになります。

excel-vba-40.gif

このプログラム(マクロ)は、あなた自身で作成したものなので安心です。「マクロを有効にする」を選択してください。

Excel VBA ユーザー定義関数は、計算式の中でも使えます。

ExcelVBAでプログラミングした「ユーザー定義関数」は、計算式の中でも使うことができます。既存の関数も同じです。

これはプログラミングで重要なことなので、図を交えて詳しく説明します。

まずは以下の手順で、動作を確認してください。

【1】ダウンロードしたExcelのファイル「gankin.xls」を開きます。

*マクロを有効にする


【2】ワークシート上で、適当なセルを選択します。

excel-vba-22.gif


【3】セルの「数式の編集」に、以下のような計算式を入力します。

excel-vba-23.gif

・例

=1000 + calcGankin(1000,0.5) + 1000


計算式の中に関数が含まれていることがポイントです。


【4】実行すると、計算結果が出ます。

excel-vba-24.gif

結果は 252000 になります。正確には 252000.0009 です。


【5】小数点以下の表示桁数を変更すると、正確な値が出てきます。

excel-vba-25.gif


以上のことから、ユーザー定義関数が、計算式の中でも使えることがわかりました。

もちろん既存の関数も計算式の中で使うことができます。
*戻り値が数式で使える値なら


【解説】
Functionプロシージャ(関数プロシージャ)は、戻り値を返すことを前に説明しました。

つまり関数は、結果として値が返って来るので、計算式の中でも使えるのです。

でもプログラミング初心者は、イメージし難いと思うので、以下のように考えてください。

関数の部分が、戻り値の数値に置き換わると考えるとわかりやすいです。

=1000 + 250000 + 1000


この「置き換わる」という感覚を掴むことは、プログラミングでは大事です。

関数と戻り値の関係がわかれば、プログラムを読むのが、だいぶ楽になります。

ExcelのVBAプログラムで、メールを送受信する方法。

ExcelVBAプログラミングを行なうと、メールを送信したり、受信できるようになります。

普段よく使っているExcelからメールが送受信できるのは、とても画期的なことです。

たぶん今より、Excelの活用範囲が、10倍以上は確実に広がります。100倍と言っても過言ではないかもしれません。

そこで、プログラミング初心者向けに無料Web講座を用意したので、ぜひご活用ください。

メール送信・受信プログラミング初心者入門(Excel版)

この講座は、VBAプログラミングの基礎から学べるように工夫しているので、プログラミング初心者でも大丈夫です。

Excelを今より活用できて、しかもVBAプログラミングの基礎まで学べるのですから、一石二鳥です。

Excelの良さは、何といってもワークシートが編集しやすいことです。初心者でも数時間あれば、使い方を学べます。

また、データをワークシートのセルに簡単に保存できることも、大きなメリットです。

Excelなら編集が簡単で、一覧性があるので、メールアドレスや氏名などの管理がとても楽です。

このExcelの良さを、メールの送受信に取り入れたのが、上記の講座です。

講座を学ぶとすぐに気付きますが、ワークシートの列に並べたメールアドレスに対して、一括してメールを送信できるのは、とても便利です。

範囲を指定して送信することも可能です。

またメールの件名や本文中に、自動的に氏名を挿入することができます。

自動挿入を応用すると、メールにいろんなデータが挿入できます。

すると、一括送信したメールでも、個々に送信したような、親しみのある内容にすることが可能です。(フレンドリーなメール)

さらに、複数の添付ファイルを付けて送信することができます。もちろんメールごとに異なった添付ファイルを指定することも可能です。

他にも「ステップメール」を送信してみたり、様々な可能性を感じていただけると思います。

ぜひ一度ご覧ください。きっとあなたの仕事に役立ちます。

ExcelのVBAで作成したユーザー定義関数プログラムの解説。

ExcelVBAを使うと、ユーザー定義関数が作成できます。

今回は例題として取り上げた、「欲しい利息から、必要な元金を逆算して、答えを返す」というユーザー定義関数のプログラムについて、詳しく解説します。

【1】ダウンロードしたExcelのファイル「gankin.xls」を開きます。

*マクロを有効にする


【2】VBEを起動します。

*「Altキー」+「F11キー」

VBE(Visual Basic Editor)とは、統合開発環境のことです。


【3】VBEを起動すると、既に標準モジュールが作成されていて、ユーザー定義関数のプログラムが書かれています。

excel-vba-20.gif

・calcGankin()
excel-vba-21.gif

このプログラムは、私が作成したものです。


【解説】
それでは、ユーザー定義関数のプログラムを詳しく見てみましょう。

まずVBAのプログラミングは、プロシージャという単位で行ないます。

プロシージャには、いくつか種類があります。詳しくは今後のステップで説明します。

今回のように、何らかの処理を行なった後に値を返したい時には、Functionプロシージャ(関数プロシージャ)を使います。


Public Function calcGankin(risoku As Long, kinri As Single) As Currency
  Dim gankin As Currency
  Const TAX As Single = 0.2
  
  gankin = risoku / ((kinri / 100) * (1 - TAX))
  calcGankin = gankin
  
End Function


(1)Functionプロシージャは、「Function」で始まり、「End Function」で終了します。


(2)Publicとは「公の」という意味を表すキーワードです。
Publicで宣言したユーザー定義関数は、どこからでも呼び出して使えます。

例えば、他のプロシージャ内から呼び出したり、Excelのワークシート上でも使えます。


(3)関数名は、自由に付けることができます。働きがわかりやすい名前が良いでしょう。
例 calcGankin


(4)Functionプロシージャは、引数を受け取ることが出来ます。
例 calcGankin(risoku As Long, kinri As Single)

・「,」(カンマ)で区切れば、複数の引数を受け取れます。
・risokuとkinriは、引数のデータを受け取る変数で、データ型を指定しています。

長整数型(Long) サイズ:4バイト 範囲:-2,147,483,648 ~ 2,147,483,647

単精度浮動小数点数型(Single) サイズ:4 バイト
範囲:1.401298E-45 ~ 3.402823E38 (正の値) -3.402823E38 ~ -1.401298E-45 (負の値)


(5)gankinという変数を宣言している部分です。
Dim gankin As Currency

変数とは、データを入れておく箱のようなものをイメージしてください。

変数を使う場合には、どんなデータを入れるのか、あらかじめデータ型を指定しておく必要があります。

今回は Currency という通貨型を指定しています。
通貨型(Currency) サイズ:8バイト 範囲:-922,337,203,685,477.5808 ~ 922,337,203,685,477.5807

また変数は、宣言した場所によって有効範囲があります。gankinは宣言したプロシージャ内だけで有効な変数です。


(6)TAXという定数を宣言している部分です。
Const TAX As Single = 0.2

預貯金の金利には、源泉徴収税が20%かかります。税金の割合はあまり変わることがないので、定数に代入しています。

定数も値を入れておくための箱のようなものです。ただし定数は、変数と違って、プログラム中で値が変化しません。だから定数(定められた数)と言います。

定数は大文字で書いていますが、これは変数と明確に区別するためです。プログラミングの慣習のようなものです。

実際には、定数を小文字で書いてもエラーにはなりません。


(7)変数や定数に値を代入する時には、「=」(イコール)を使います。

プログラミングでの「=」は、算数のように「等しい」という意味ではありません。

プログラミングでは、「右辺の値を左辺に代入する」という意味なので、注意してください。

プログラミング言語によっても違いますが、VBAの場合は、「等しい」という意味で「比較」に使うこともあります。

少し紛らわしいですが、VBAプログラミングをしていくうちに、代入なのか比較なのか、区別できるようになります。

ちなみに他のプログラミング言語では、代入「=」と比較「==」を明確に区別しているものもあります。このほうがわかりやすいですよね。


(8)右辺で元金を計算して、左辺の変数に代入している部分です。
gankin = risoku / ((kinri / 100) * (1 - TAX))

必要な元金は、欲しい利息と、金利(年利)、源泉徴収税から、上記の計算式で逆算することができます。

計算式の書き方には、いろいろあります。答えが正しく出るなら、同じでなくてもかまいません。


(9)関数を呼び出す時の引数と、受け取る関数の引数のデータ型は、一致させる必要があります。


calcGankin(1000,0.5)
calcGankin(risoku As Long, kinri As Single)

金利の値は、小数点以下を含むので、Singleにしている点に注意してください。


(10)Functionプロシージャは、戻り値を返すことができます。今回はCurrency型です。
例 calcGankin(risoku As Long, kinri As Single) As Currency

*関数の戻り値のデータ型を指定している部分は重要です。

戻り値(return value)とは、関数が処理の後に返す計算結果のことです。


(11)戻り値を返すには、関数名と同じ名前に代入します。
例 calcGankin = gankin

*忘れやすいので注意してください。


(12)Excelのワークシート上で、Functionプロシージャを呼び出すには、先頭に「=」を付けて、引数を指定します。

=calcGankin(1000,0.5)


以上がFunctionプロシージャ(関数プロシージャ)の基本です。

この部分のプログラムを変えることで、いろんな処理(計算)を行なって、結果を返すことができます。


【補足】
プログラムの先頭にある、「Option Explicit」は、変数の宣言を強制する時に指定します。

*Explicit 明快なという意味

この1行があると、変数は宣言しないと使えなくなります。

VBAでは、基本的に変数は宣言しなくても使えます。でもその時は、バリアント型 (Variant)になります。

バリアント型は、どんな値でも代入できるので便利なのですが、必要なメモリー容量が大きくなります。

それにプログラミングでは、変数を宣言しないと、バグ(誤り)が入り込む原因になります。

そのため、変数はしっかり宣言してから使うようにしましょう。

「Option Explicit」を加えておくことで、変数の宣言を忘れた時には、VBEが教えてくれます。

ExcelのVBAで作成したユーザー定義関数を、実際に使ってみる。

ExcelVBAで、ユーザー定義関数を作成する最大のメリットは、複雑な処理でもたった1行の関数名だけで呼び出せることです。

ユーザー定義関数は、既存の関数を組み合わせたり、新たに処理を加えて、独自の関数が作れます。

今回は例題として、預貯金の元金を計算するユーザー定義関数を作ってみました。

その動作確認を行なってみましょう。

【1】私のほうでExcelファイルを用意しました。
まず gankin.zip をダウンロードしてください。

・gankin.zip
excel-vba-11.gif


【2】「gankin.zip」を解凍すると、「gankin.xls」というExcelファイルがあります。

excel-vba-12.gif

このExcelファイルには、あらかじめ calcGankin()という、ユーザー定義関数を作成してあります。


【3】「gankin.xls」を開きます。


【4】ファイルを開く時に、警告メッセージが表示されますが、安全なので「マクロを有効にする」をクリックします。

excel-vba-13.gif

このメッセージは、既にVBAでプログラミングしているために表示されています。


【5】「gankin.xls」が開きました。

excel-vba-14.gif

ワークシートは白紙で、まだ何もありません。


【6】適当なセルをクリックして、選択します。

excel-vba-15.gif

セルはどこでもかまいません。


【7】「=」(数式の編集)をクリックします。

excel-vba-16.gif


【8】以下の図のように、「=」の後にユーザー定義関数と引数を入力し、「OK」ボタンをクリックします。

excel-vba-17.gif

・例

=calcGankin(1000,0.5)


この時、先頭の「=」を忘れないように注意して下さい。忘れると、計算式になりません。

また式は、すべて半角英数で入力します。


【9】セルに答えが返ってきました。

excel-vba-18.gif

これはユーザー定義関数側で、答えを返すようにプログラムしているからです。


【10】引数の値をいろいろ変えて、試してみてください。

・例

=calcGankin(5000,1.2)


=calcGankin(80000,0.85)


【ワンポイント】
「複雑な処理でも、たった1行の関数名だけで呼び出せる」という意味が、少しはわかったのではないでしょうか?

この感覚は、プログラミングではとても大事なことなので、ぜひ覚えておいてください。

今回のようなユーザー定義関数や、既存の関数を組み合わせて、さらに複雑な処理を行なったり、しかも簡単で自動的に行なえるようにすることが、プログラミングであり、システム開発なのです。

Excel VBA ユーザー定義関数を作るメリットとは?

ExcelVBAで、ユーザー定義関数を作るメリットは、簡単な関数名だけで、複雑な処理が行なえることです。

これはユーザー定義関数だけでなく、既存の関数でも同じです。

もし数百行~数千行にもなる複雑な処理が必要な場合でも、ユーザー定義関数として作っておけば、たった1行で呼び出すことができます。

最初はイメージし難いかも知れませんが、実はとても便利なことなのです。

これだけでもVBAを習得するメリットがあります。

例えば、預貯金で利息を計算する場面を考えてみましょう。

・問題1

あなたは1年間で1,000円の利息が欲しいとします。
この時、普通預金の金利が0.05%です。(年利)
預貯金の利息には、源泉徴収税が20%かかります。
さて、元金をいくら預ければ、手取りで1,000円の利息が得られますか?


答えは250万円です。

2,500,000円 × 0.05%(0.0005) = 1,250円
1,250円 × 20%(0.2) = 250円
1,250円 - 250円 = 1,000円


これくらいなら、力技で何とか計算できます。

では、もう少し難しくなるとどうでしょうか?

・問題2

1年間で利息が5,000円欲しくて、金利が1.2%なら、元金はいくら必要ですか?


こうなると簡単には計算できません。逆算するのは難しいですよね。

一つの方法としては、Excelのセルに以下のような計算式を入力すれば、答えがでます。

= 5000 / ((1.2 / 100) * (1 - 0.2))

答えは、約520,833.33円です。

でも、条件の数値が変わる度に計算式を入力し直すと、ミスが出そうです。
それにExcelに詳しくない人に任せると、もっと心配ですよね。

そこで、ユーザー定義関数を使って、簡単に計算できるようにしてみましょう。

今回は初めてなので、私のほうでExcelファイルを用意しました。

gankin.zip をダウンロードしてください。

このファイルには、calcGankin()という、ユーザー定義関数を作成してあります。

使い方は次回のステップで詳しく解説しますが、ユーザー定義関数を使うと、以下のように簡単に計算できるようになります。

・例

=calcGankin(5000,1.2)


=calcGankin(1000,0.5)


それぞれを適当なセルに計算式として入力すると、答えが返って来ます。

・1番目の引数は、1年間で欲しい利息の金額です。
・引数が複数ある場合は、半角の「,」(カンマ)で区切ります。
・2番目の引数は、金利(年利)をパーセントで指定します。

*金利の%は不要です。数値だけ入力します。
*引数とは、関数に渡す値のことです。

ユーザー定義関数名と、引数に指定する数値を変えるだけで、答えが出せるのがポイントです。

Excelのセルに直接長い計算式を入力する場合よりも、ずっとシンプルになっていることが分かると思います。

元金の計算式自体は隠れているので、式を書き間違えてしまう心配はありません。

これなら入力ミスが、かなり減ると思います。また仕事を他の人に頼んでも安心です。

そしてもう一つ大事なポイントです。

今回の計算は1行で済みますが、これが複雑になると、セルに直接計算式を入力する方法では無理があります。

ところがユーザー定義関数にすると、数百行~数千行のプログラムでも、たった1行で呼び出せます。

これこそがユーザー定義関数を作るメリットなのです。

Excel VBA ユーザー定義関数とは?

Excel(エクセル)でVBAプログラミングが出来るようになるメリットの一つが、オリジナルの関数を作れることです。

ユーザーが独自に作った関数を「ユーザー定義関数」と言います。

ユーザー定義関数は、既存の関数と同じように、Excelのワークシート上から呼び出して使うことができます。

*正確には、標準モジュールに作成したユーザー定義関数が呼び出せます。

このユーザー定義関数が作れるようになるだけでも、ExcelでVBAを習得する価値が、十分あります。

たぶん今の10倍以上は、Excelを活用することができます。

Excelは、とても優れた表計算ソフトウェアです。Excelには、豊富な関数があらかじめ用意されています。

例えば、SUM()、COUNT()、AVERAGE()などは、Excelを使ったことのある人なら、一度は利用したことがあるのではないでしょうか?

Excelでは、既存の関数を使うだけでも、いろんな処理(計算)が出来ます。

通常の業務で使う程度なら、既存の関数だけで十分です。

しかし、それだけではExcelの能力のほんの一部しか使っていません。Excelにはもっと凄い能力が秘められているのです。

その能力を引き出すのが、VBAというプログラミング言語と、VBEという統合開発環境です。

これらを使えば、あなた自身でオリジナルのユーザー定義関数を作ることが可能です。

ユーザー定義関数を使えば、今よりずっと便利になります。また仕事が効率的になります。

そこで当講座では、一番最初にユーザー定義関数を作る方法について紹介します。

スポンサードリンク

スポンサードリンク






Excel VBA 初心者入門講座 TOPへ

×

この広告は180日以上新しい記事の投稿がないブログに表示されております。