★Functionマクロ

エクセルのマクロは、sub型とfunction型の2通りあります。sub型は、オートマクロ機能で自動生成することもできますが、function型はすべて自分で書かなければなりません。VBA言語で記述しますが、BASIC言語を習ったことがあれば、ほとんど同じです。

1.書き出しと書き終わり

Function 名前(引数1,引数2,・・)    *名前・引数はアルファベットで、

End Function                 *これが、書き終わり

2.名前について

定義したい関数名は、シート関数で既に使われているものは使えません。定義済みの関数は英語名なので、ローマ字表記の日本語を使うと、殆どダブりません。この名前が、そのまま戻り値の変数名として使われます。従って、構文の中に、必ず関数名を定義する文が入っているはずです。

例:hirituという関数を定義するとき、

Function hiritu(分子,分母)
  hiritu=分子/分母
End function

3.引数について

定義したい関数を計算するために必要な変数を、引数といいます。引数が無い場合でも( )は必要です。引数が複数ある場合は、,(カンマ)で区切って並べます。上の例のように日本語(全角)は使えません。半角英数のみ使用可能です。

例:hirituという関数を定義するとき、

Function hiritu(bunsibunbo
  hiritu=bunsi / bunbo
End function

注:本来は、変数はdim分で、種類の定義が必要です。整数(±32000の範囲)や小数点以下も含まれる数とか、あらかじめ数の種類によって定義します。しかし、これをしなくとも、自動で定義されます。メモリーを有効に使うためには、細かく定義した方が良いのですが、さほど大きくないマクロでは、自動変数でも構いません。

4.定義された関数を使う

使い方は、シート関数と同じです。引数には、直接セルの座標を書き込みます。関数の書かれたセルに、計算された返り値が入ります。

例:      =hiritu(A4,B4)

5.シート関数(組込関数)とfunction関数(マクロ関数)の違い

シート関数も、マクロ関数も使い方の上では、全く同じです。マクロが組み込まれているシートでないと、マクロ関数は使えないのに対し、シート関数はすでにワークシートに組み込まれているので、定義無しで使えます。マクロ関数を全てのシートで使いたいときは、これをpersonal.xlsというブック名で保存しておきます。エクセルの起動時に、このファイルがあるとbookに組み込んでくれます。この機能を利用して、エクセルのマクロウイルスは、personal.xlsの名前で保存されています。このため、personal.xlsのファイルはウィルスの温床のようになってしまっています。

function文の中では、VBAの関数を使います。これが、組込関数と同じ名前であったりするので混乱しますが、あくまで、別物です。構文が異なるので注意して下さい。例えば、if文は

if 条件式 then ○ else × で書き、行を変えて書く場合は、最後に end if が必要になります。組込関数のif とは、全く別物です。

6.モジュールシートを開く

エクセル95では、挿入・シート・モジュールシートで、白紙のシートが開きます。ここに、functionから書き始めます。エクセル97以降は、モジュールシートを開く手順が面倒になりました。

macro1.gif (3579 バイト) 表示・ツールバーで、Visual Basicにチェックを入れると、下のような、ツールバーが現れます。
編集ボタンをクリックします。

macro2.gif (920 バイト)

 このような、VisualBasicの編集画面が現れます。

macro4.gif (6819 バイト)

さらに、挿入メニューを開き、標準モジュールをクリックします。

macro5.gif (12310 バイト)

VBAで関数を作る・素数発見


最近のWindowsでは、昔のBasicのようなプログラムが作れませんが、エクセルのマクロを使うと、Basic風のプログラムが楽しめます。VBAの勉強の参考にしてください。

素数かどうかの判定

 1と自分自身以外に約数が無ければ素数です。偶数の素数は2以外に無く、素数はすべて奇数です。そこで、3・5・7・・・と、片っ端から奇数で割っていき、割り切れる数が無いか調べます。しかし、その数自身まで調べる必要は無く、その数の平方根に近い奇数まで調べれば十分です。nがm1で割り切れれば、n=m1×m2となるはずで、m1がnの平方根により大きな奇数であれば、m2はそれよりも小さい奇数のはずで、すでにチェックしているはずです。nの平方根に近い内輪の奇数をn0とし、nを3・5・7・9・・・n0と割り算していって、割り切れるものが無ければ、その数は素数です。

素数を見つける関数の仕様を決める

関数 Sosu(N)は、N以上の最も近い素数を返すようにします。

Sosu(10)=11で、Sosu(11)=11というようにこの関数は、n以上の素数を返すようにします。こうすると、nがどんな数であっても、必ず整数を返します。

割り切れるかどうかは、Mod関数を使って調べます。n Mod n0 は、nをn0で割った余りを返します。basicの関数ですがVBAでもそのまま使えます。

平方根は SQR(n)を使い、この内輪の整数は INT(n)で求めることにします。

INTを使うと、 N/n0 と INT(N/n0)が等しいかどうかで、Nがn0で割り切れるかどうか判定できます。実際には、この形式で作ってあります。

Do Loop Until 条件式

Do Loop を使うと、繰り返しが作れます。Untilは、条件が成立しない間 Tilは条件が成立するまで、繰り返されます。割る数を 3・5・7・・と2ずつ増やして行き、割る数が平方根を超えるまでチェックしますが、途中で約数が見つかったら、次の奇数を調べます。ここでは、2重のLOOPを使っています。

最初の2行は、引数が偶数の場合は1を加えて、調べるスタートの数Nsを奇数にするものです。

正確には、NやNs・n0(Nの平方根に近い整数)は整数として定義しなければなりませんが、特に定義しなくても動きます。( Dim N as Integer)

モジュールシートを開く

実際に関数を書き込むのは、ワークシートでなくモジュールシートです。VisualBasicのツールバーを表示させEditorのボタンをクリックします。

 

ここの挿入メニューで、標準モジュールを挿入します。これで開いた窓に書き込みます。

ワークシートでこの関数を使う

これで、=sosu(10) と入れれば、11と出るはずです。

自動的に、200個の素数を計算するように関数を入れたものが下の例です。

実際のファイルは ここから ダウンロードできます。(sosu.xls 25KB)

戻る