VBAでワークシート関数を自作する方法を解説

Excel VBA

VBAでワークシート関数を自作できることを知っていますか?
意外とVBAのこの機能は、VBAを使う方でも知らない場合も多いようです。
そこでこの記事では、VBAを使ってワークシート関数を自作する方法について詳しく解説していきます。

スポンサーリンク

VBAではワークシート関数を自作できる!

では、どうやって追加するのでしょうか。

答えは簡単、
Private Function
を記述して、値を返すだけなのです。

これで、そのブック内の任意の箇所であなたの自作したワークシート関数が使えるようになります。

ブックのリンクをうまく使えば、グループ内での関数の共有もできますね。
(参照がぐじゃぐじゃになるのであまりおすすめはしませんが)

さっそく解説していきます。

基本的なワークシート関数の追加方法

サンプルということで、引数を二つ受け取り加算する関数を書いてみましょう。

Private Function sample_add(a, b)
    sample_add = a + b
End Function

これだけです。

関数から戻り値を返すには、関数名に値を代入します。

この関数はVBAのコードからも、シートからも呼べます。

シートの任意のセルに、次の式を書き込んでください。

=sample_add(1,2)

3
と表示されているはずです。

最初のワークシート関数は自作できました。

関数にセルの値を渡す

関数にセルの値を渡すにはどうすればいいのでしょうか。

答えは簡単、式にセルの番地を書き込むだけです。他のワークシート関数と全く同一です。

A1セルに1、A2セルに2が入っているとして

=sample_add(A1, A2)

とすると
3
と表示されます。

これで自作関数もいろいろ使い勝手が広がります。

可変個の引数を受け取る

ここからは、より高度な関数の作り方を学んでいきます。

まず、ワークシート関数のトピックではないのですが、VBAのFunctionが可変個の引数を受け取るにはどうすればいいのかを解説します。

それは、引数を

ParamArray arg() As Variant

と宣言すればいいのです。
argは自由に名前を変えて結構です。

これにFunction内のコードでどうアクセスするかというと、たとえばFor Eachを使います。

For Each a In arg()
    s = s + a
Next

このようにします。

このコードだと、For内ではarg()の値が順にaに代入されるので、そのaを使い処理を実行します。

時刻を作成する

サンプルのために、時刻について解説します。

まず、Excelの日付・時刻はすべて

です。

1日を1として、時刻は少数部で表します。
これで日付と時刻を表しています。
普段見やすい形に変換されるのは、Excelがそう変換している(もしくは自分でそのセルを日付・時刻形式にする)からです。

よって、日付・時刻の計算には数に使える演算子が使えます。

とは言っても、例えば時刻同士を足したいとかいうときがありますよね。

そんなときは、
TimeValue

TimeSerial
を使います。

a = TimeValue("12:00:00")
a = TimeSerial(12, 0, 0)

どちらも同じ意味です。時刻を数に直します。数に直してから計算します。

TimeValueは
時:分:秒
で指定します。

TimeSerialは
時,分,秒
で指定します。

ワークシート関数のサンプル

以上を使って、勤怠管理に使う独自関数を作成してみます。

複数の引数をとり、時間を合計し、6時間を超えたら休憩時間1時間を引き、6時間以下なら引かないとします。

コードは下記になります。

Private Function 独自関数(ParamArray arg() As Variant)
    s = 0
    For Each a In arg()
        s = s + a
    Next
    If s > TimeSerial(6, 0, 0) Then
        s = s - TimeSerial(1, 0, 0)
    End If
    独自関数 = s
End Function

この関数を使うには、たとえば

=独自関数(B1, B2, B3)

とします。

きちんと仕様通りに計算されているのが分かりますね。

まとめ

今回は、ワークシート関数の拡張の仕方について解説してきました。

ワークシート関数を独自に拡張できるとなるとなかなか便利です。

活用していきましょう。

エージェントへの登録はお済みですか?

今すぐフリーランスになる気がなくても、エージェントへの登録は済ませておくことをお勧めします。理由はストレスや疲労が限界に達してからだと遅いから。

現在の自身のフリーランスとしての価値・単価相場を把握できますし、条件にあった案件をキャッチできるようになるので、いざフリーランスになろうとした際に早期に手が打てます。

エージェント登録は無料なので、今はフリーランスにならなくとも登録だけはしておくべきです。

真っ先に登録すべきエージェントは、圧倒的に多くのクライアントを開拓しているDYMテック。2500社の優良な取引先と、条件交渉力が強く高条件で仕事を紹介してくれるエージェントです。

案件紹介のみでなく、エンジニアとしてのキャリア相談や業界動向のキャッチアップなど、フリーランス対してのサポートも充実していますので、初めてフリーランスに挑戦する方も安心です。

DYMテックの詳細を見る

Excel VBA
スポンサーリンク
shigotoswitchをフォローする
DYMテックの評判・特徴・口コミ|しごとスイッチ