ExcelVBAでワークシートからユーザー定義関数が使える条件と隠す方法

VBAのFunctionプロシージャで宣言された関数は、Excelワークシートの数式からSUM関数等と同じように使用することが出来ます。

しかし、表示されているのにエラーとなったり、表示されていないのに使えてしまったりという、よくわからない挙動をします。

今回は、この辺りの解説をしていきます。

重要事項

入力候補に関数名が出ないからと言って使えないわけではない
数式で関数を使用するときにモジュール名は普通書かないが、Excelが自動的にモジュールを選択して実行している
関数名が重複しているとモジュール名から記述が必要になる
関数をクラスモジュールに記述すれば完全に隠すことができるが、VBAから使いづらくなるため工夫が必要となる
アドインブックに記述された関数は、他のブックで使用することができる

Privateは入力候補から見えないようにしているだけ

VBAのプログラミングにおいて、Privateは「別のモジュールから使用できないようにする」という強力な隠蔽効果があります。

しかし、ワークシートに対しては、それほどの効果はありません。

例えば、標準モジュールに次のようなVBAが記述されているものとします。

Function FuncPublic()
    FuncPublic = "FuncPublic"
End Function

Private Function FuncPrivate()
    FuncPrivate = "FuncPrivate"
End Function

マクロを記述したブックのセルA1で =Func と入れると、 FuncPublic は表示されますが、 FuncPrivate は表示されません。

f:id:Kotori-ChunChun:20211010235810p:plain

しかし、実際に関数名を入力すると、どちらも使うことが可能です。

f:id:Kotori-ChunChun:20211010235832p:plain

Option Private Moduleも入力候補から見えないようにしているだけ

VBAにはモジュール全体を隠すような機能として Option Private Module ステートメントがあります。

この記述も、ワークシートに対しては、それほどの効果はありません。

Option Private Module
Function FuncPublic()
    FuncPublic = "FuncPublic"
End Function

マクロを記述したブックのセルA1で =Func と入れると、 FuncPublic が表示されなくなっていますが、実際に書いてみると動作します。

f:id:Kotori-ChunChun:20211010235856p:plain

関数名が異なるモジュールで重複していると、呼び出し時にモジュール名から記述が必要になる

今度は、Module1とModule2に同じ名前の関数 FuncPublic を宣言してみます。

Function FuncPublic()
    FuncPublic = "Module2.FuncPublic"
End Function

すると、入力候補からFuncPublicが消失し、数式を確定した時ににメッセージボックスで「名前が適切ではありません。」が発生し、数式の結果は #NAME! となります。

f:id:Kotori-ChunChun:20211010235914p:plain

これは、Excelから見てどちらの関数を指しているか分からないために起きている現象です。

この場合は、数式にモジュール名を明記することで解決が可能です。（なお、モジュール名 =Module1. と書いても候補は出ません）

f:id:Kotori-ChunChun:20211010235923p:plain

この他に、アドインで宣言された関数とも競合することがあります。かなり複雑になるので割愛しますが、この時はブックが特定できる形での記述が必要となります。

クラスモジュールに記述したメンバーはExcelから完全に隠すことができる

今までは標準モジュールでしたが、クラスモジュールを使った場合は話が変わってきます。

クラスの詳しい説明は割愛しますが、クラスを使うには New キーワードを使ってインスタンス化を行う必要があり、インスタンスの格納された変数名. と書くことで初めてメンバを呼び出すことができます。

以下がクラスの呼び出し例です。

Sub Test_UseClass1()
    Dim ins As Class1
    Set ins = New Class1
    MsgBox ins.FuncPublic()
End Sub

ワークシートには、変数にインスタンスを代入するという構文を書くことはできません。

仮にインスタンスが代入済みの変数があったとしても、数式で変数を使うことはできません。

したがって、ワークシート上からユーザー定義関数は完全に隠す事ができます。

VBA内で関数が使いづらくなる問題はインスタンス自動生成機能を使って回避する

クラス化することでワークシートから隠蔽はできましたが、今度はVBAで関数を呼び出すのが面倒になってしまいます。

1箇所でしか使わないなら大した問題ではありませんが、あちこちで使う汎用関数の場合は多大な修正が必要となります。

この問題を解決するには、自動インスタンス生成機能（非公式名）を使うのが有効です。

自動インスタンス生成機能とは、変数の宣言と同時にNewする以下のような記述です。 - 宣言セクションで Public ins As New Class1 - 宣言セクションで Private ins As New Class1 - 宣言セクションまたはプロシージャ内で Dim ins As New Class1

例えば、Module1には以下のように記述します。

Private ins As New Class1

Sub Test_UseClass1()
    MsgBox ins.FuncPublic()
End Sub

個人的には、以下の理由からモジュールレベル変数(Private)で宣言するのがおすすめです。

ローカル変数(プロシージャ内のDim)は言わずもがなですが、プロシージャ毎に何度も何度も書かなくてはならないため適しているとは言えません。

パブリック変数(Public)は、モジュールの依存関係が絡まりやすくなります。もしパブリック変数を使用するなら一番メインとなるモジュールに集中して記述すると決めるか、グローバル変数宣言用のモジュールを作るのが良いかと思います。

インスタンス生成コードを完全に排除するにはクラスモジュールの隠し属性（Attribute VB_PredeclaredId = True）を使用する

詳しい解説は割愛しますが、クラスモジュールをVBEからエクスポートして、ソースコードをテキストエディタで開いて、以下のように書き換えてください。

Attribute VB_PredeclaredId = False
↓
Attribute VB_PredeclaredId = True

こうすることで、バックグラウンドでクラス名と同一の自動生成機能付きのパブリックレベル変数が宣言されたことになり、変数の宣言を完全に省略することができます。

また、関数を呼び出す部分のコードを見れば親のモジュール名が分かるので、直感的に読み取れるようになります。（そして、クラスモジュールから標準モジュールに戻す場合の互換性も担保できます。）

※この機能を使用するかどうかは賛否両論ありますので、よく調べて自分で判断してください。（筆者は頻繁に活用しています）

アドインブックに記述された関数だけは、他のブックで使用することができる

ユーザー定義関数は、同じブック内では使えますが他のブックから使用することはできません。

しかし、アドインブックの場合は、起動さえしていれば開いているすべてのブックで使えるようになります。

その上でありがちな勘違いとして、PERSONAL.XLSBはアドインブックではないため、いつでも関数を使用することは出来ません。

関数名がアドインブックと競合した場合、非常に厄介な挙動をしますが今回は割愛します。

まとめ

ユーザー定義関数がワークシートから使えるかどうかをまとめるとこうなります。

Public Function・・・候補に表示され使用できる。
Private Function・・・候補には表示されないが使用できる。
Option Private Module・・・候補には表示されないが使用できる。
クラスモジュール・・・候補に表示されないし絶対に使用できない。
関数名の競合・・・・候補には表示されないし関数名だけでは使用できない。だが、モジュール名から書けば使用できる。
アドインブックとの競合・・・色々厄介なことになる（※詳細は後日修正）

なお、Sheetモジュール、ThisWorkbookモジュール、フォームモジュールは、クラスモジュールに相当します。

備考

Excelのアドインは様々なプログラミング言語で開発が可能です。そのためExcelVBA以外の方法でユーザー定義関数を増やすことも可能です。

ということは、他のアドインと関数名が競合するケースも想定されます。

2021年には新たに追加された LAMBDA関数によって、外部プログラミングに頼らずとも関数を増やすことも可能となりました。

どのような現象が起こるのか確認できていませんが、競合しないよう注意してください。

今回はFunctionについて書きましたが、Sub（Alt+F8や図形のマクロの登録で実行できるかどうか）の基準もだいたい同じです。

ユーザー定義関数(UDF)作成のコツについては、こちらをご覧ください。

えくせるちゅんちゅん

ことりがエクセルをちゅんちゅんするブログ