Excelのカスタムワークシート関数を使用する方法

2020年5月4日

はじめに

ホームページで公開されているカスタムワークシート関数(以下、「カスタム関数」と記載)を、ワークシート上で使用する方法を説明します。カスタム関数とは、ワークシート関数であるSUM関数やAVERAGE関数のような関数を、ユーザーが好きなように作成したものです。そのためカスタム関数は、自作関数やユーザー定義関数といった言い方もされます。

さて、ここでいう「ホームページで公開されているカスタムワークシート関数」とは、VBA(Visual Basic for Application)で書かれたコード(文字列)のことを指しています。VBAが何だか分からない方も問題ありません。基本的にはコードを貼り付けるだけでカスタム関数を使用することができますので、問題解決の手段の1つとして覚えておいて損は無いでしょう。

なお、カスタム関数はExcelブック内に保存されるため、原則としてカスタム関数を保存したExcelブックでしかカスタム関数を使用することは出来ません(厳密には、限定的条件下で他のブックからでも使用できます)。複数のExcelブックで同じカスタム関数を使用したい場合は、これから説明する方法ではなく、カスタム関数をまとめたExcelブック(アドイン)を使用する方法を採用すべきです。

 

[開発]タブの表示

カスタム関数のVBAコードは、Excelに内蔵されたVBAのエディタを使用して登録します。このエディタをVBE(Visual Basic Editor)と呼びます。略称がVBAとVBEで似ていますが、VBAはプログラミング言語のことであり、VBEはVBAを書くためのソフトウェアです。なお、これらの意味を知らなくてもカスタム関数の登録・使用に支障は無いため、トリビア程度に考えて頂いても問題ありません。

閑話休題。ExcelでVBEを起動する方法は2つあり、1つはExcelワークシートを表示している状態で、[Alt]+[F11]キーを押します。もう1つは、リボンの[開発]タブから開く方法です。[開発]タブはExcelのインストール直後の状態では表示されていないため、これを表示させる方法を説明します。[開発]タブの表示までの説明はExcel2016を使用しますが、Excel2010以降であれば同じような方法で[開発]タブを表示できますので、参考にして下さい。どうしても表示方法が分からない場合は、[Alt]+[F11]キーでVBEを起動しましょう。

表示方法の説明を開始します。まず、Excelを起動してリボンの[ファイル]タブを選択して下さい。

 

画面左下の[オプション]を選択します。

 

[Excelのオプション]ダイアログが表示されるため、画面左側のメニューから[リボンのユーザー設定]を選択します。ダイアログの内容が下記画面のようになりますので、[メインタブ]リストボックスの中の[開発]チェックボックスをチェックし、[OK]ボタンを押します。

リボンに[開発]タブが表示されるため、これを選択します。

 

[開発]タブの中のコマンドが表示されます。

 

[Visual Basic]コマンドを選択すると、VBEが起動します。このコマンドのショートカットキーが、[Alt]+[F11]キーという訳です。

 

 

複数のExcelブックを開いている場合、VBEには開いている全てのブックが表示されるため、ブック名を確認して操作するブックを間違えないように注意してください。自信がない場合は、カスタム関数を登録するExcelブック以外は閉じておいた方が安全でしょう。

 

VBEの設定

カスタム関数の登録には直接関係がありませんが、VBEで「変数の宣言を強制させる」設定を行います。この設定は行わなくても問題ありませんし、設定の意味が分からなくても問題ありません。しかし、この設定の影響で、ホームページからコピーしてきたカスタム関数を貼り付けた際にエラーが出る場合、そのホームページのカスタム関数は使用しないことをお勧めします。

設定の説明に入ります。VBEのメニューの[ツール]から[オプション]を選択します。

[オプション]ダイアログが表示されるので、[変数の宣言を強制する]チェックボックスをチェックします。以上です。簡単ですので、特に理由がなければこの設定を行っておいて下さい。

 

カスタム関数の入力(登録)

いよいよ、カスタム関数を登録(貼り付け)します。カスタム関数の貼り付け先を「標準モジュール」と呼びます。まず、プロジェクトエクスプローラ内のブックを右クリックします。具体的な場所は下記画像の通りですが、文章で説明すると、[VBAProject(ブック名)」と表示されているツリービューアイテムのことです。右クリックにより表示されるメニュー(コンテキストメニュー)の中から[挿入]>[標準モジュール]の順にクリックします。

余談ですが、コンテキストメニューを表示するための右クリックは、厳密にはブック(プロジェクト)を選択する必要はありません。特に、ブック(プロジェクト)ツリービューアイテムの下位項目(画像の例ではMicrosoft Excel Objects 、Sheet1、またはThisWorkbook)でも問題ありませんし、その横の空白部分でも問題ありません。しかし、複数のブックが開いた状態で、空欄を右クリックして標準モジュールを挿入する場合、意図したブックに挿入されない可能性があるため、本項ではブックを右クリックする手順としています。

 

標準モジュールが挿入され、コード編集画面(コードペイン)が表示されます。ここにカスタム関数を貼り付けます。コードペインを閉じた場合は、標準モジュール名(下記画像の赤枠)をダブルクリックするか、標準モジュールを選択した状態で左上のボタン(下記画像の青枠)を押すと、標準モジュールのコードペインを開くことができます。

 

コードペインに使用したいカスタム関数を貼り付けます。登録作業はこれだけです。使用したいカスタム関数が無く、動作確認だけしたい場合は下記のコードを貼り付けて下さい。このカスタム関数は、単純に"カスタムワークシート関数テスト"という文字列を返すだけです。なお、カスタム関数は「Public Function ・・・」または単に「Function ・・・」で始まる行で始まり、「End Function」で終わります。貼り付ける際、コピー範囲が正しいことを確認する手掛かりにしてください。なお、関数の始まりの行の前に「’」(シングルクオーテション)で始まる文章が1行~複数行書かれていることがありますが、これはコメントです。コメントはカスタム関数の動作に一切影響を与えません。しかし。カスタム関数の説明などが書いてあることが多いので、必要に応じて関数本体と一緒にコピーしましょう。

'シングルクオーテーションで始まる行はコメントです。カスタム関数の動作には直接影響を与えませんが、
'カスタム関数の説明が書いてあることが多いので、必要に応じてコピーしましょう。
Public Function CustomFunction() As Variant
    CustomFunction = "カスタムワークシート関数テスト"
End Function

 

カスタム関数を使用する方法は、SUM関数など、他の関数と同様です。例のカスタム関数はCustomFunctionなので、A1セルに「=CustomFunction()」と入力すれば、カスタム関数を使用することが出来ます。

 

他のカスタム関数を追加する場合は、既存のカスタム関数の上か下に別のカスタム関数を貼り付けます。もしくは、新たに標準モジュールを追加して、そちらに貼り付けても問題ありません。動作確認用として、別のカスタム関数を用意しました。今回は引数付きです。

Public Function ArgumentTest(ByRef Param1 As Variant) As Variant
ArgumentTest = Param1 & " : " & Param1
End Function

先程と同様にカスタム関数を呼び出してみましょう。複数のカスタム関数が同時に使用できることを確認できたかと思います。

 

カスタム関数を使用するExcelブックの保存

カスタム関数の実体はVBAで作成したマクロであり、VBAはプログラムです。プログラムは、悪意のある人が作成すると、プログラムを実行したコンピュータ又はコンピュータの使用者に悪影響を与えることができます。そのため、Excelブックにプログラム(≒ VBA ≒ カスタム関数)を含める場合は、Excelブックにプログラムが含まれることを明示するため、専用のExcelブックに変換する必要があります。

それでは、専用のExcelブックに変換する説明に入ります。リボンの[ファイル]タブを選択し、画面左側のメニューから[名前を付けて保存]を選択します。保存場所は任意ですが、今回の説明ではデスクトップに保存します。

[名前を付けて保存]ダイアログが表示されます。ダイアログ下方の[ファイルの種類]コンボボックスには、既定で「Excelブック(*.xlsx)」が表示されています。この保存形式は、保存時にカスタム関数を保存しない(自動削除される)形式です。試しに、このまま保存してみましょう。[保存]ボタンを選択します。

 

下記の警告が表示されます。これを要約すると、[はい]を選択するとカスタム関数が自動削除され、Excelブックが保存されます。[いいえ]を選択すると、[名前を付けて保存]ダイアログに戻ります。今回は[いいえ]を選択して下さい。

[名前を付けて保存]ダイアログに戻りました。[ファイルの種類]コンボボックスのアローボタンをクリックすると、保存形式のドロップダウンリストが表示されます。この中から、「Excelマクロ有効ブック(*.xlsm)」を選択します。

 

[ファイルの種類]コンボボックスの内容が「Excelマクロ有効ブック(*.xlsm)」になっていることを確認し、[保存]ボタンをクリックします。これで無事、カスタム関数がExcelブックに保存されました。Excelブックの拡張子が「xlsm」になっていることに注意して下さい。

 

カスタム関数を含むブックを開く場合の注意事項

カスタム関数を含むブック(マクロ有効ブック)を開く際、2種類の警告が表示されることがあります。警告の1つが、[Microsoft Excelのセキュリティに関する通知]ダイアログです。カスタム関数を機能させるためには、[マクロを有効にする]を選択して下さい。

 

もう1つは、[セキュリティの警告]です。カスタム関数を機能させるためには、[マクロを有効にする]を選択して下さい。

上記2種類の警告について、[マクロを無効にする]を選択したり、警告を無視したりすると、カスタム関数が機能しなくなります。機能しないとは、カスタム関数を含むセルを編集(F2キーを押す、ダブルクリックする、または文字入力をするのいずれか)すると、セルの値が「#Name?」となります。ただし、編集しない限りは直近(最後にExcelブックを保存した時点)のセルの値が表示されます。

 

おわりに

Excelでカスタム関数を使用する方法について説明しましたが、理解できましたでしょうか?説明しておいて何なのですが、カスタム関数は、本当に必要に迫られない限りは、使用すべきではありません。しかし例外もあります。代表的な例外の1つは、現在使用中のExcelのバージョンで使用できない関数を、カスタム関数で実現する場合です。例えばCONCAT関数をExcel2013で使用したい場合、カスタム関数で実現することができます。将来Excelをバージョンアップした場合は、Excelブックをマクロ無しブック(拡張子:xlsx)で保存することで、容易に標準関数へ移行することができます。また、本当に必要に迫られた場合、すなわちExcelの標準関数では実現不可能か、実現可能でも実用的でないほど重くなる場合は、迷わすカスタム関数を使用してください。

VBA

Posted by 黒箱