Excel VBAでWorksheetではなくユーザー定義のオブジェクトを取得する
検証環境 Excel 2013
普通に考えたら単純なんですが地味にハマッたのでメモ。
例えばオブジェクト名”unko”のシート名”Sheet1″のシートがあった場合は、
|
1 2 |
Dim sheet As Worksheet Set sheet = Worksheets("Sheet1") |
ではなく
|
1 2 |
Dim sheet Set sheet = Worksheets("Sheet1") |
As Worksheet 抜きで取得してやると Worksheet オブジェクトではなく、ユーザー定義のunkoがオブジェクト取得できる。
このオブジェクトの場合、もちろんunko(Sheet1)に定義された関数や変数にアクセス可能。worksheetオブジェクトで取得するとアクセスできない。
- unko(Sheet1)
|
1 2 3 4 5 6 7 8 9 10 11 |
Option Explicit Dim kuso As Long Sub SetKuso(value As Long) kuso = value End Sub Function GetKuso() As Long GetKuso = kuso End Function |
以下のVBAを実行コード
|
1 2 |
sheet.SetKuso 1 Debug.Print "kuso = " & sheet.GetKuso() |
出力
|
1 |
kuso = 1 |
言い換えれば、Worksheets()や、ActiveSheet()など、Worksheetを取得する系の関数はユーザー定義のオブジェクトが返却されるので、変数にオブジェクトを入れる時だけ注意すれば良い。
unkoシートをコピーして使用することで、別インスタンスのオブジェクトとして生成されるためメンバ変数、関数を持ったクラスの用な使い方ができる。
|
1 2 3 4 5 6 7 |
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() |
出力
|
1 2 |
kuso = 1 kuso = 2 |
コピー時オブジェクト名は必ず重複しない名前となり、上記の例の場合、新規に作成したシートオブジェクト名は”unko1″となる。
また、オブジェクト名はObjectのメンバとして”CodeName”に設定されている。
|
1 2 |
Debug.Print "CodeName = " & Worksheets(1).CodeName Debug.Print "CodeName = " & Worksheets(2).CodeName |
出力
|
1 2 |
CodeName = unko CodeName = unko1 |
これを利用して、シートに配置しているボタンのマクロ等を動的に設定ができる。
シートにラベル”kuso_button”と設定したシート”Sheet1″を作成。
unko(Sheet1)に以下のVBA追加
|
1 2 3 |
Function KusoButton() As Long Debug.Print Name & " kuso = " & kuso End Function |
以下のVBAを実行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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)”それぞれのボタンを押下すると以下のように出力される。
|
1 2 |
Sheet1 kuso = 1 Sheet1 (2) kuso = 2 |
おわい