オブジェクト名を指定してWorksheetオブジェクトを取得するFunction(Excel)

CodeNameプロパティというものがある

akashi-keirin.hatenablog.com

コチラの記事のコメント欄が炎上ｗしたことにより、CodeNameというプロパティの存在を知った。

CodeNameプロパティとは

Worksheet.CodeNameで、Worksheetオブジェクトのオブジェクト名（デフォルトでVBEのプロジェクトエクスプローラーに表示される「Sheet1（Sheet1）」の左側の「Sheet1」。シート名を変えてもオブジェクト名は変わらない。プロジェクトエクスプローラー上で変更可能。）

オブジェクト名は、手動でシート名を変更されても変化しないので、ユーザーによる勝手なシート名の変更にも耐えられる可能性が高い、ということだ。

もちろん、わざわざVBEを開いてオブジェクト名を変更するような猛者がいれば別だがｗ

たとえば、テスト用ブックの各ワークシートのオブジェクト名を

f:id:akashi_keirin:20190511091527j:plain

こんなふうにしておいて、イミディエイト・ウインドウに

?Worksheets("アホ").CodeName

と打ち込んで[Enter]してみると、

f:id:akashi_keirin:20190511091529j:plain

このように、オブジェクト名「Sh01Aho」が返っている。

つまり、そういうことだ。

オブジェクト名を指定してWorksheetオブジェクトを取得する

Excelで様式を配布して、それを回収するような場合、配布前に必要なシートのオブジェクト名を適当なものに変更しておけば、見かけ上シート名をいじくられていてもCodeNameプロパティの値をキーに必要なシートを特定することができる。

リスト１　標準モジュール

Public Function getWorksheetByCodeName( _
            ByVal targetBook As Workbook, _
            ByVal targetCodeName As String) As Worksheet
  Dim ret As Worksheet
  Set ret = Nothing
  Dim i As Long
  For i = 1 To targetBook.Worksheets.Count
    With targetBook
      If .Worksheets(i).CodeName = targetCodeName Then _
        Set ret = .Worksheets(i): Exit For
    End With
  Next
  Set getWorksheetByCodeName = ret
  Set ret = Nothing
End Function

Workbookオブジェクトとオブジェクト名を受け取り、指定したオブジェクト名のWorksheetオブジェクトがあればそれを返し、なければNothingが返るしくみ。

使ってみる

ThisWorkbookの同一フォルダ内にテスト用ブック「Test2.xlsx」を置いて、次のコードで実験。

リスト２　標準モジュール

Public Sub test02()
  Dim targetBook As Workbook
  Set targetBook = Workbooks.Open(ThisWorkbook.Path & "\Test2.xlsx")
  Dim targetSh As Worksheet
  Set targetSh = getWorksheetByCodeName(targetBook, "Sh05Dekosuke")
  If Not targetSh Is Nothing Then Debug.Print targetSh.Name
End Sub

Sh05Dekosukeというオブジェクト名のWorksheetオブジェクトを取得し、そのNameプロパティの値をイミディエイト・ウインドウに表示させようという試み。

f:id:akashi_keirin:20190511091532j:plain

ちゃんと、Sh05Dekosukeオブジェクトに相当するWorksheetオブジェクトのNameプロパティが取得できていることがわかる。

おわりに

オブジェクト名を用いたWorksheetオブジェクトへのアクセスは、ブック（プロジェクト）内で閉じた処理には多用しているが、ブック（プロジェクト）間をまたいだ処理で使えなかった。

しかし、事前にオブジェクト名を指定せねばならんにせよ、少しは道が開けたかも知れない。

ちなみに

たとえば、今回のSh01Ahoオブジェクトの場合、親オブジェクトは何なのだろう、と思って調べてみた。

イミディエイト・ウインドウに

?Sh01Aho.pa

まで打ち込むと、

f:id:akashi_keirin:20190511091534j:plain

このように、Parentプロパティが入力候補に出てくる。

期待に胸を高鳴らせつつ、

?Sh01Aho.Parent

と入力して[Enter]！

f:id:akashi_keirin:20190511091537j:plain

なんやねん、それ。

ちなみに、「VBAProject」を参照設定してやると、別ブック（プロジェクト）からでもVBAProject.Sh01Ahoみたいにすれば各シートオブジェクトにアクセスすることはできる模様。しかしながら、同じプロジェクト名でもブックが変われば参照設定は切れてしまう（当り前）ので、あまり使い道はなさそう。