Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

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

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



ユーザー定義関数が邪魔

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

f:id:ExcelLover:20211017141545j:plain

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


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

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


スコープ

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

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

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

docs.microsoft.com

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

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

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

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

f:id:ExcelLover:20211017141632j:plain

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

f:id:ExcelLover:20211017141712j:plain

f:id:ExcelLover:20211017141725j:plain

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

f:id:ExcelLover:20211017141746j:plain


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

f:id:ExcelLover:20211017141803j:plain

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

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

Option Private Module

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

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

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

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

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

docs.microsoft.com

関数用クラス

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

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

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

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

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

f:id:ExcelLover:20211017141926j:plain

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

f:id:ExcelLover:20211017141939j:plain

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

Attribute VB_PredeclaredId

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

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


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

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

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

f:id:ExcelLover:20211017141958j:plain

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

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

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


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

Attribute VB_PredeclaredId = Falseを

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

f:id:ExcelLover:20211017142011j:plain

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

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

f:id:ExcelLover:20211017142024j:plain

以上で準備はOKです。

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

f:id:ExcelLover:20211017142038j:plain

まとめ

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

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

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

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

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

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

謝辞

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