Excel VBS のシートモジュールにプロパティを設定する時のメモ
各モジュールへの記述
シートモジュールにプロパティを設定することは可能。
特にクラスモジュールや標準モジュールへの記述と変わらない。
他のモジュールからアクセスする時
下記のようにシートモジュールのオブジェクト名を使用してプロパティにアクセスすれば、インテリセンスも働くし問題はない。
Msgbox WorksheetCodeName.PropertyGetName
上の例では、
WorksheetCodeName がシートモジュールのオブジェクト名。
PropertyGetName がプロパティ取得プロシージャ名。
ワークシートを引数とする時
ワークシートのオブジェクト名が使えない場合、例えば対象のワークシートが定まっておらず、ワークシートを引数として、他のモジュールに渡したい場合。
当然、引数として渡すには引数の型を指定する必要がある。
Worksheetオブジェクトとした場合
この時、Worksheetオブジェクトとして渡そうとすると以下のような記述になる。
Public Sub Procedure(Byref vWorksheet as Worksheet)
このようにして受け取ったvWorksheetオブジェクトを用いて、プロパティにアクセスしようと下記のように記述してもエラーになる。
Msgbox vWorksheet.PropertyGetName
ワークシート型として扱われるので、ワークシート型に定義されていないPropertyGetNameプロパティにはアクセスできない。
独自オブジェクトとした場合
Worksheetオブジェクトは、それぞれを オブジェクト名=オブジェクト型 として使用できる。
上記の例の場合ワークシートのオブジェクト名をWorksheetCodeNameとしているので
WorksheetCodeName型が使用できる。
そこで Worksheet型ではなく WorksheetCodeName型として受け取れば下記のように記述できる。
Public Sub Procedure(Byref vWorksheet as WorksheetCodeName)
Msgbox vWorksheet.PropertyGetName
End sub
これであれば、インテリセンスも使えるし、期待どおりに動く。
ただ、これではWorksheetCodeName型しか受け取れないので、オブジェクト名からの記述と変わらない。当然だが他のワークシートはそれぞれ各オブジェクト名型であり、WorksheetCodeName型ではない。これでは引数にしている意味がない。
インテリセンスを使用できなくても良いならば・・・
VBAにはObject型というオブジェクトならなんでも入れられる型がある。
便利に見えるが、インテリセンスが働かないのでコーディングの効率的には避けたい。
特にWorksheetオブジェクトは多くのメンバを持っているので、何とか使いたいところ。
解決策というには妥協点が多いが、現状下記を利用中。
Public Sub Procedure(Byref vWorksheet as Object)
Dim mTargetSheet as Worksheet
Set mTargetSheet =vWorksheet
Msgbox vWorksheet.PropertyGetName
End sub
引数はObject型として受けとり、それをワークシート型で定義した変数に代入する。
こうすると、mTargetSheetはワークシート型として扱えるので、ワークシートのメンバはインテリセンスが働く。
vWorksheetはオブジェクト型として扱われるので、このオブジェクトからメンバのPropertyGetNameにアクセスも可能だ。
ただし、オブジェクト型のため、こちらのインテリセンスは働かない。
ツッコミどころ
・同じオブジェクトなのに、対象のメンバによってオブジェクト名を使い分けるってどうなの？
→意外と気にならなかった。そんなにカスタムしたメンバが多くないからかも。
・カスタムしたメンバのインテリセンスが働かないって意味なくない？
→これは思う。カスタムしたメンバが多くないから成り立っているかも。それよりデフォルトのワークシートメンバのインテリセンスが働くことの利点が大きい。