ユーザー定義関数をセルからは参照できないようにする(VBEからは参照できる)方法

先日、ユーザー定義関数を作成してモヤモヤしたことをつぶやいたところ様々な貴重なご意見を頂いたので備忘録。

ユーザー定義関数を別モジュールからは呼べるけど、セルからは呼べない状態にすることって出来ましたっけ？

— Kou Excelが大好きだ！ (@LoverExcel) 2021年10月10日

ユーザー定義関数が邪魔

モヤモヤしたきっかけはこちら。

セル上で使うものなら当然表示されないと困るが、VBE内でしか使わないユーザー定義関数が表示されることに急にモヤモヤするようになった。

関数の入力補完に表示される条件

セルへの関数入力時に入力補完が表示されるか否かは、「関数のスコープ」が影響してきます。

スコープ

スコープとはVBEのモジュールに作成した変数・定数・プロシージャをどの場所から使用できるかを設定するものです。

スコープは下記の3種類を設定できます。

スコープ	内容
プロシージャレベル	記載されたプロシージャ内でのみ使用可能
プライベートモジュールレベル	記載されたモジュール内でのみ使用可能
パブリックモジュールレベル	プロジェクト内の全てのモジュールのプロシージャから使用可能

docs.microsoft.com

スコープと入力補助表示の関係は下記のとおりです。

	Public		Private
	入力補助	使用可能	入力補助	使用可能
セル	○	○	×	○
VBE	○	○	×	×

プロシージャで特にスコープを指定しなかった場合はPublicとして扱われます。

入力補助と使用可能はどう違うのでしょうか？

関数を入力しようとした時にPrivateスコープを指定している関数はセルから、そして別モジュールから入力しようとした時に、入力補助が効いていません。

ところがセルからはこの入力補助が効いていない関数を使用することは可能です。

関数の挿入ダイアログにも表示されていません。

使用可能とはいえ表示されませんのでモヤモヤすることもありませんし、間違って使用することもまず有りません。

スコープをPrivateに設定しておけばモヤモヤは解消できそうです。

Option Private Module

各プロシージャにスコープを設定する以外に、モジュール単位でスコープを変更する方法があります。

モジュールの先頭部分にOption Private Moduleと記載します。

これを追加することで該当モジュール内のプロシージャは他のモジュールやセルから参照することができなくなります。

使用することは可能です。

	入力補助	使用可能
セル	×	○
VBE	×	○

docs.microsoft.com

関数用クラス

上記の方法はいづれも関数を標準モジュールに記載している場合に使用する方法でした。

この方法は関数用のクラスを作成してセルからのみ使用する関数を登録する方法です。

	入力補助	使用可能
セル	×	×
VBE	○	○

この方法であればセルからは入力補助にも表示されず、使用することも出来ません。

このようにクラスを用意してその中に必要な関数を用意すればOKです。

しかしこの方法では関数を使用するために関数クラスのインスタンスを作成する必要があります。

関数を使うために一々変数を用意してインスタンスを作成するのもなんだか面倒です。

Attribute VB_PredeclaredId

変数の宣言・インスタンス作成を省略する特殊な方法があります。

クラスモジュールのAttribute VB_PredeclaredIdにTRUEを設定する方法です。

クラスモジュールのエクスポートを行います。

クラスモジュールを右クリック → ファイルのエクスポート で可能です。

エクスポート後は該当クラスを削除してください。

クラスモジュールの解放でももちろんOKです。

但しエクスポートは必ず行ってください。

忘れるとクラスモジュールは消えて無くなってしまいます。

エクスポートしたクラスファイル(拡張子clsのファイル)をメモ帳などのテキストエディタで開きます。

Attribute VB_PredeclaredId = Falseを

Attribute VB_PredeclaredId = Trueに変更して保存。

修正したクラスをインポートします。

適当な項目を右クリックしてファイルのインポートから先程修正クラスファイルを指定します。

以上で準備はOKです。

変数の用意・インスタンスの作成無しでいきなりクラス名を書くだけでクラス内の関数を呼び出すことが出来るようになりました。

まとめ

	Public		Private		Option Private Module		関数クラス
	補助	使用	補助	使用	補助	使用	補助	使用
セル	○	○	×	○	×	○	×	×
VBE	○	○	×	×	×	○	○	○

当初のモヤモヤを解消する方法としては以下の3つの方法があります。

1. 関数プロシージャのスコープをPrivateにする
2. 関数が記載されたモジュールにOption Private Moduleの設定を行う
3. 関数クラスを用意する

３つの内、関数クラスを用意する方法を採用することにしました。

1. セルからは完全に隠蔽できる
2. Attribute VB_PredeclaredId = Trueの設定をすれば関数の使用が簡単
3. ユーザー定義関数以外の候補が表示されない

３についてはVBE内でユーザー定義関数を使用する時に、元々用意されている関数とユーザー定義関数が混ざっていると、どれがユーザー定義関数かわからなくなりますが、この方法であればユーザー定義関数しか表示されませんので迷うことは有りません。

謝辞

twitterフォロワーの方々にはいつもお世話になっております。

ところが、クラスモジュールに書いてしまうと、使う前にインスタンス化（New）が必要になってしまうので、クラス名.関数名 と書ける裏技を強引に使うために Attribute VB_PredeclaredId = True を記載する必要があります。

— ちゅん🐣VBAer (@KotorinChunChun) 2021年10月10日

たぶん要望を叶える方法としては、
Option Private Module
が手っ取り早いかと～💡

自分は全てのモジュールにいれてたり🐣

あっ、VBEアドインに、
自動挿入オプションあり〼 pic.twitter.com/rGRMDr3QTr

— 踊るエクセル@VBEアドイン作ってVBEハック中･･･ (@ExcelVBAer) 2021年10月10日