Excelでワークシート関数を自作する

 

初めて投稿します。株式会社ゼネットの中井です。

ずっと金融系の開発現場にいますが、最近現場が変わってから多少時間の余裕が出来たので、何か書いてみることにしました。
ただ思ったより書けそうなことがなく。
考えた結果、Excelのワークシート関数の自作(ユーザー定義関数)について書いてみます。

少しでもExcelのマクロ(VBA)を書いたことがある方なら全く難しくないと思いますが、そうでない方向けの基本手順も入れますので、マクロの入門としても読めるといいかなと。

前書き(この記事にした理由)

開発現場で、例えばテストデータ作成や環境準備などのちょっとした作業をするのに、手元で補助用のスクリプトや簡易ツールを作ったり、そこまでではなくてもExcelで何か書くことはあるかと思います。

ただ、そういった作業やってるときに長いマクロやスクリプト書く余裕はないのはもちろんですが、「ボタン押したら一発実行！」みたいな作りにすると、ボタン押したときにどういう動きになるのかが分かりづらかったり、小回りがきかなかったりで、個人的にはあまり好きではなく。

なので、私の場合はデータ加工程度ならExcelワークシートで数式やなにやらで作ってしまうパターンが多いのですが、場合によっては複雑になってしまうこともあるので、そういう時に自作関数にしたりします。

しかし現場では、自分以外の人がワークシート関数作ってるのをまだ見たことがありません。(マクロを書く人はけっこういるのに)

もしかして関数を作れるということ自体がそれほど知られていないのか？

なので、もし知られてないだけなのであればちょっと書いてみようと思った次第です。

本題(ユーザー定義関数の例)

私がたまにやるパターンを例として挙げます。

例1

パス＋ファイル名から、ファイル名だけを取り出す関数。

ごくたまにですが、ファイル名をリストアップして作業に使うことなどがあったりします。
フルパスからファイル名だけを拾うには、一番最後の"￥"の文字位置を探す必要があるわけですが、基本のワークシート関数ではちょうどよい関数が無いので、普通に数式書くと意外と面倒で、マクロを使えるときならこんな感じでユーザー定義関数にしたりします。

ちなみに基本の関数だけで書いてみるとこんな感じになります。(もっと簡素に書ける方法あったら知りたい)

例2

シートに入力したデータからSQLのINSERT文を作る関数。

※説明簡略化のため、INSERTの項目名などの出力は省略しています。

作業環境でGUIのDB操作ツールが使えないときとか、データ移行するときとか、よくやります。
テキストファイルにSQL一括出力するスクリプト、とか書かなくても、たいした件数でなければこれで数式の結果をコピペで足ります。

実装方法

自作と言っても、何か特別なことをやるわけではなく、普通にVBAでFunctionを書くだけです。

マクロ(VBA)未経験の方への前提説明

VBAのエディタは「開発」タブの「Visual Basic」で表示できます。

ただしExcelデフォルトでは「開発」タブ自体が非表示なので、無かったらリボンのユーザー設定から表示してください。
(画面はExcelのバージョンによって多少違うと思います。これはExcel10)

あるいは、面倒なら「Alt＋F11」でも出せます。

保存済みのファイルを開くときは、もちろんマクロは有効にしてください。

警告メッセージも出ないで問答無用で無効になってしまう場合は、「開発」タブの「マクロのセキュリティ」(またはExcelオプション「トラストセンター」)から設定変更できます。
(ここもExcelのバージョンによって多少違います。「トラストセンター」ではなく「セキュリティセンター」の場合もあり)

例1の場合

引数でフルパスを受け取り、戻り値でファイル名を返すFunctionプロシージャを作ります。

まず標準モジュールを追加。

ソースは以下です。(内容はコメント参照)

' 変数宣言を強制する
Option Explicit

'引数のパスからファイル名を取り出して返す
'
'引数1：パス＋ファイル名の文字列
'戻り値：ファイル名
'
Public Function getFileName(フルパス As String) As String

    Dim p As Integer
    
    'フルパスから、一番最後の"\"の文字位置を取得
    p = InStrRev(フルパス, "\")
    
    '取得した位置の次の文字から最後までを取得して返す
    getFileName = Mid(フルパス, p + 1)

End Function

標準モジュールに書いたら、もうワークシートの数式で普通に使えます。
(バグってなければ。できればメニューバーの「デバッグ → ～のコンパイル」実行しておいたほうがベター)
 
関数挿入ダイアログでも「ユーザー定義」選択すればちゃんと出てきます。

※上の例のとおり、VBAは実は変数名やプロシージャ名に日本語も使えます。
あまり使わないですが、ワークシート関数として作る場合は、引数を日本語にしておくと数式入力するときに表示が分かりやすいので、たまにやります。
違和感ある方は普通に英数字でいいと思います。

例2の場合

引数でテーブル名とセル範囲を受け取り、戻り値でINSERT文を返します。

'入力データからSQLのINSERT文を作る
'
'引数1：テーブル名
'引数2：データ範囲(1行,複数列)
'戻り値：INSERT文
'
Public Function toInsertSQL(テーブル名 As String, データ範囲 As Range) As String

    Dim sql As String
    
    'まず INSERT～VALUESまで
    sql = "INSERT INTO " & テーブル名 & " VALUES ("
    
    Dim col As Long
    Dim celstr As String
    
    ' データ範囲の1列目～最後の列までループ
    For col = 1 To データ範囲.Columns.Count
        
        '2項目目以降だったらSQLにカンマつなげる
        If col > 1 Then sql = sql & ", "
        
        'データ範囲内の1行目/col列目の値を取得
        celstr = データ範囲.Cells(1, col).Value
        
        '未入力の場合はnullに、それ以外は文字列としてシングルコーテーションで括る
        If celstr = "" Then
            celstr = "null"
        Else
            celstr = "'" & celstr & "'"
        End If
        
        'SQLにつなげる
        sql = sql & celstr
    Next
    
    '最後のカッコ追加
    sql = sql & ");"
    
    '結果返却
    toInsertSQL = sql

End Function

実際に現場で作ったものは、上に加えて項目名やデータ型もセル範囲で引数に渡して、項目名も出力したり、NUMBERやDATE型なら値の引用符を変えたりしてました。

またセットでUPDATE文を作る関数も作ってましたが、UPDATEの場合はさらに更新対象の項目だけを並べる＋WHERE条件を入れる必要があるので、その項目をセル書式で判別させたり。

関数のコードは割愛しますが、見出しのセルの Font.Bold プロパティや、データセルの Interior.ColorIndex プロパティで判別して対象項目を抜き出します。こんなふうにセル書式を使うこともできるということで。

エラーを返したいとき

「引数値が正しくなかったら#VALUE!にしたい」など。
CVErr関数で、エラーの種類を指定してやればエラーを返せます。
この場合、関数の戻り値の型は Variant に。

'入力データからSQLのINSERT文を作る
'
'引数1：テーブル名
'引数2：データ範囲(1行,複数列)
'戻り値：INSERT文 引数1が空文字列の場合は#VALUE!
'
Public Function toInsertSQL(テーブル名 As String, データ範囲 As Range) As Variant

    'テーブル名が空だったら#VALUE!を返す
    If テーブル名 = "" Then
        toInsertSQL = CVErr(xlErrValue)
        Exit Function
    End If
    ：

CVErrの引数は定数が使えます。例えば#N/AにしたいならxlErrNAを指定。

注意点

マクロなので拡張子はxlsm

ファイルは「～.xlsm」(マクロありファイル)で保存する必要があります。～.xlsx だとマクロが保存されません。

また、ファイル開くときにマクロを無効にした場合も動かないのでエラーになります。

なので、数式で出したデータだけを他人に渡す場合などは、計算結果を値貼り付けしてしまうのが良いかもしれません。

作った関数は基本そのブックでしか使えない(と思う)

というか、ブックを一緒に開いていれば他のブックでも使うことは出来ますが、マクロ定義のあるブックを閉じてしまえぱエラーになるのであまりやらないかと。
(他のブックに自作関数入り数式のセルをコピーして、値貼り付けを忘れるとそうなったり)

あちこちのブックで使いたいなら、アドイン形式(.xlam)にしてしまうと良いかもしれません。アドインについては必要ならググってください。

数式の再計算

今回の例のように引数としてセルを指定している場合、そのセルの値が変われば再計算(Function再実行)がかかります。

大量のセルに入力していたりすると、関数もそれごとに実行されるわけなので、複雑な処理を書くとそれなりに重くなるので注意。

ただし、引数の値までは変わらないシート再計算だけなら、ユーザー定義関数は通常は再計算されないようです。
逆に、引数の値が変わらないときでもまめに再計算させたい場合は Application.Volatile 参照。

普通に書くFunctionと特に違いは無い

ので、元々ワークシート関数用に書いたプロシージャでなくても数式で使えることになります。

ただしあくまでも関数の範疇の処理に。
例えばFunctionの中でMsgBoxを出してたりすると、セルが再計算されるたびにメッセージダイアログが出まくる事態になると思われます。やらないとは思いますが。

(2022/03/15追記)

「特に違いは無い」と書きましたが、少し違いがありました。
ユーザー定義関数として実行される場合は、Functionの中でセルの値や書式を変
えていても無効になるようです。
(関数なのでシートを変える処理は制御していると思われます)

締め

書き始めてみたら思ったより長くなりました。
何かの作業で使えそう、などの気付きや、マクロをさわるきっかけに繋がれば幸いです。