Excel VBAでWorksheetではなくユーザー定義のオブジェクトを取得する

検証環境 Excel 2013

普通に考えたら単純なんですが地味にハマッたのでメモ。

例えばオブジェクト名”unko”のシート名”Sheet1″のシートがあった場合は、

    Dim sheet As Worksheet
    Set sheet = Worksheets("Sheet1")

ではなく

    Dim sheet
    Set sheet = Worksheets("Sheet1")

As Worksheet 抜きで取得してやると Worksheet オブジェクトではなく、ユーザー定義のunkoがオブジェクト取得できる。

このオブジェクトの場合、もちろんunko(Sheet1)に定義された関数や変数にアクセス可能。worksheetオブジェクトで取得するとアクセスできない。

• unko(Sheet1)

Option Explicit

Dim kuso As Long

Sub SetKuso(value As Long)
    kuso = value
End Sub

Function GetKuso() As Long
    GetKuso = kuso
End Function

以下のVBAを実行コード

    sheet.SetKuso 1
    Debug.Print "kuso = " & sheet.GetKuso()

出力

kuso = 1

言い換えれば、Worksheets()や、ActiveSheet()など、Worksheetを取得する系の関数はユーザー定義のオブジェクトが返却されるので、変数にオブジェクトを入れる時だけ注意すれば良い。

unkoシートをコピーして使用することで、別インスタンスのオブジェクトとして生成されるためメンバ変数、関数を持ったクラスの用な使い方ができる。

    Worksheets("Sheet1").Copy After:=Worksheets("Sheet1")
    
    Worksheets(1).SetKuso 1
    Worksheets(2).SetKuso 2

    Debug.Print "kuso = " & Worksheets(1).GetKuso()
    Debug.Print "kuso = " & Worksheets(2).GetKuso()

出力

kuso = 1
kuso = 2

コピー時オブジェクト名は必ず重複しない名前となり、上記の例の場合、新規に作成したシートオブジェクト名は”unko1″となる。

また、オブジェクト名はObjectのメンバとして”CodeName”に設定されている。

    Debug.Print "CodeName = " & Worksheets(1).CodeName
    Debug.Print "CodeName = " & Worksheets(2).CodeName

出力

CodeName = unko
CodeName = unko1

これを利用して、シートに配置しているボタンのマクロ等を動的に設定ができる。

シートにラベル”kuso_button”と設定したシート”Sheet1″を作成。

unko(Sheet1)に以下のVBA追加

Function KusoButton() As Long
    Debug.Print Name & " kuso = " & kuso
End Function

以下のVBAを実行

    Worksheets("Sheet1").Copy After:=Worksheets("Sheet1")
    
    Dim sheet1
    Set sheet1 = Worksheets(1)
    Dim sheet2
    Set sheet2 = Worksheets(2)
    
    sheet1.SetKuso 1
    sheet2.SetKuso 2

    ' ボタンマクロ設定
    sheet1.Shapes("kuso_button").OnAction = sheet1.CodeName & ".KusoButton"
    sheet2.Shapes("kuso_button").OnAction = sheet2.CodeName & ".KusoButton"

“Sheet1(2)”シートが追加されるので、”Sheet1″、”Sheet1(2)”それぞれのボタンを押下すると以下のように出力される。

Sheet1 kuso = 1
Sheet1 (2) kuso = 2

おわい