20240307 エクセルVBAクラス備忘録
＜前書き＞
エクセルVBAオブジェクト指向、初心者です。
VBA自体は、以前から使用していました。
オブジェクト指向というものがあることは、知っていましたが、ネットなどを見てもよく分からず、挫折した過去あり。
＜以上前書き＞
今回の疑問とかやったこと
ネットの「いつも隣にITのお仕事」様の記事
「【初心者でもできる】エクセルVBAで最も簡単なクラスを作る方法」
を利用しながら、コードを書く。（感謝）
参考記事：
・エクセルVBAでインスタンス生成時に自動で処理を実行するイベントプロシージャClass_Initialize
・エクセルVBAで自作コレクションのインスタンス生成時に初期データも投入する方法
・エクセルVBAで自作コレクションの要素を取得するプロパティの作り方
（感謝いたします。もし不都合がありましたら、即この記事は削除いたします。）
この記事は、自分用の備忘録なので、正しくないことやメモ程度の内容ばかりです。汗
やろうとしたこと
入力シートというシートに、コンボボックスを設置し、そのコンボボックスの候補リストをクラスを使って取得する。
具体的には入力シートにコンボボックスを用意して、「氏名」の一覧が出るようにする。
「氏名」はsimeシートから読み込む。
simeiシートには、通し番号、職員番号、氏名、職名、等々８列入っている。（一部空白セルもあり）
（上記simeシートの氏名以外の列もコンボボックスで氏名選択をすると表示内容が変わるようにする（予定））
（まあ、手続き型なら難しくないけど・・・。）
注：この記事では、やろうとしたことは、完成してません。
（１）最初にクラスモジュール「氏名」を作る。
上記項目に沿って、
-------------------------------------------------
Private m通し番号 As String
Public Property Let 通し番号(ByVal p通し番号 As String)
m通し番号 = p通し番号
End Property
Public Property Get 通し番号() As String
通し番号 = m通し番号
End Property
-------------------------------------------------
↑こんなのを作る。private変数にしました。（メンバー変数等の書き方は「いつも隣にIT様」とは違います。
）
これで、１行のデータ（１人分）を保持できる。
（２）クラスモジュール「氏名リスト」を作る。
次に、個人データを束ねる「氏名リスト」を作る。
参考：
・エクセルVBAでインスタンス生成時に自動で処理を実行するイベントプロシージャClass_Initialize
あたり。
---------------------------------------
Option Explicit
Public Items As Collection
Private Sub Class_Initialize()
Set Items = New Collection
End Sub
----------------------------------------
こんな感じ。
動かす標準モジュールは↓こんな感じ
------------------------------------------------
Function TEST_氏名リスト()
Dim v氏名リスト As 氏名リスト
Set v氏名リスト = New 氏名リスト
With ThisWorkbook.Sheets("sime")
Dim i As Long
i = 1
Do While .Cells(i, 3).value <> "" '氏名が空になるまで
Dim v氏名 As 氏名
Set v氏名 = New 氏名
v氏名.Initialize .Range(.Cells(i, 1), .Cells(i, 8))
v氏名リスト.Items.Add v氏名, v氏名.職員番号
i = i + 1
Loop
End With
Debug.Print v氏名リスト.Items.Item(1).職員番号
Debug.Print v氏名リスト.Items.Item("54").氏名
' 氏名にアクセスして氏名を表示
Dim v氏名Item As 氏名
For Each v氏名Item In v氏名リスト.Items
Debug.Print v氏名Item.職員番号
Debug.Print v氏名Item.氏名
Next v氏名Item
Stop
End Function
--------------------------------------------
Debug.Printは、もっと別の形でできないかと思ったが、これしか書けなかった。汗。
なお、上記に、
v氏名.Initialize .Range(.Cells(i, 1), .Cells(i, 8))
というコードがあるように、クラスモジュール「氏名」には、
---------------------------------------
Public Sub Initialize(rng As Range)
m通し番号 = rng(1).value
m職員番号 = rng(2).value
m氏名 = rng(3).value
m職名 = rng(4).value
End Sub
（部分的に省略してます。））
---------------------------------------
というメソッドを追加しています。
（注：クラスモジュール「氏名」のclass_initialize()ではありません。）
（３）上記コードの改良
参考：
エクセルVBAで自作コレクションのインスタンス生成時に初期データも投入する方法
あたり。
VBAのclass_initializeは、変数を持たすことができないらしい。
（多分、Private Sub Class_Initialize()のカッコに値をセットできないという意味か？）
そのため、
v氏名.Initialize .Range(.Cells(i, 1), .Cells(i, 8))
といったメソッドを作っている。
このコード自体は、クラスモジュール「氏名」と標準モジュール（function）（範囲の変数）に入れているが、
これらを動かすコード自体を、クラスモジュール「氏名リスト」のclass_initializeから呼び出そうというのが、
次の段階。
----------------------------------
Function TEST_氏名リスト()
Dim v氏名リスト As 氏名リスト
Set v氏名リスト = New 氏名リスト
Debug.Print v氏名リスト.Items.Item(1).職員番号
Debug.Print v氏名リスト.Items.Item("54").氏名
' 氏名にアクセスして氏名を表示
Dim v氏名Item As 氏名
For Each v氏名Item In v氏名リスト.Items
Debug.Print v氏名Item.職員番号
Debug.Print v氏名Item.氏名
Next v氏名Item
Stop
End Function
-------------------------------------
Debug.Printは、もっと別の形でできないかと思ったが、これしか書けなかった。汗。
↓クラスモジュール「氏名リスト」
---------------------------------------
Option Explicit
Public Items As Collection
Private Sub Class_Initialize()
Set Items = New Collection
With ThisWorkbook.Sheets("sime")
Dim i As Long
i = 1
Do While .Cells(i, 3).value <> "" '氏名が空になるまで
Dim v氏名 As 氏名
Set v氏名 = New 氏名
v氏名.Initialize .Range(.Cells(i, 1), .Cells(i, 8))
Items.Add v氏名, v氏名.職員番号
i = i + 1
Loop
End With
End Sub
------------------------------------------
（４）上記コードの改良
参考：
エクセルVBAで自作コレクションの要素を取得するプロパティの作り方
あたり。
上記では、
Debug.Print v氏名リスト.Items.Item(1).職員番号
Debug.Print v氏名リスト.Items.Item("54").氏名
というように、.Items.Item(1)みたいに間にcollectionを使うことで、少し長い。
そのため、クラスモジュール「氏名リスト」に独自のプロパティを持たせる。
-----------------------------------------------
Public Property Get 独自Item(ByVal pKey As Variant) As 氏名 'pKeyはインデックスでも、キーでも受け取れるようにvariant
Set 独自Item = Items.Item(pKey)
End Property
-----------------------------------------------
（Itemだけだと見にくいので、ここでは 「独自Item」としました。）
---------------------------------------------------
Function TEST_氏名リスト()
Dim v氏名リスト As 氏名リスト
Set v氏名リスト = New 氏名リスト
Debug.Print v氏名リスト.Items.Item(1).職員番号
Debug.Print v氏名リスト.Items.Item("54").氏名
Debug.Print v氏名リスト.独自Item(1).職員番号
Debug.Print v氏名リスト.独自Item("54").氏名
Stop
' 氏名にアクセスして氏名を表示
Dim v氏名Item As 氏名
For Each v氏名Item In v氏名リスト.Items
Debug.Print v氏名Item.職員番号
Debug.Print v氏名Item.氏名
Next v氏名Item
' ' 氏名にアクセスして氏名を表示
' Dim v氏名Item As 氏名
' For Each v氏名Item In v氏名リスト.独自Item 'エラー：引数は省略できない独自Itemsではできない
' Debug.Print v氏名Item.職員番号
' Debug.Print v氏名Item.氏名
' Next v氏名Item
Stop
End Function
-----------------------------------------------------
上記のように、独自Itemを使ったコードを書きました。
が、一番下のコメントアウトしたfor each文はエラーでした。自分では原因究明できず。（放置）
（５）上記コードの改良
参考：
エクセルVBAで自作コレクションの要素を取得するプロパティの作り方（同上）
あたり。
上記「独自Item」をprivate変数にして隠ぺい。他のモジュールからアクセスできなくします。
（あまり分かっていない、概念・・・汗）
クラスモジュール「氏名リスト」
-------------------------------------------------
Option Explicit
'Public Items As Collection
Private mItems As Collection
Private Sub Class_Initialize()
Set mItems = New Collection
With ThisWorkbook.Sheets("sime")
Dim i As Long
i = 1
Do While .Cells(i, 3).value <> "" '氏名が空になるまで
Dim v氏名 As 氏名
Set v氏名 = New 氏名
v氏名.Initialize .Range(.Cells(i, 1), .Cells(i, 8))
mItems.Add v氏名, v氏名.職員番号
i = i + 1
Loop
End With
End Sub
Public Property Get 独自Item(ByVal pKey As Variant) As 氏名 'pKeyはインデックスでも、キーでも受け取れるようにvariant
Set 独自Item = mItems.Item(pKey)
End Property
--------------------------------------------
（注：宣言部だけでなく、コード内変数も変えています。）
-------------------------------------------
Function TEST_氏名リスト()
Dim v氏名リスト As 氏名リスト
Set v氏名リスト = New 氏名リスト
'itemsをprivateにすると動かない
' Debug.Print v氏名リスト.Items.Item(1).職員番号
' Debug.Print v氏名リスト.Items.Item("54").氏名
Debug.Print v氏名リスト.独自Item(1).職員番号 '独自Itemでしかアクセスできない
Debug.Print v氏名リスト.独自Item("54").氏名
'itemsをprivateにすると動かない
' ' 氏名にアクセスして氏名を表示
' Dim v氏名Item As 氏名
' For Each v氏名Item In v氏名リスト.Items
' Debug.Print v氏名Item.職員番号
' Debug.Print v氏名Item.氏名
' Next v氏名Item
' ' 氏名にアクセスして氏名を表示
' Dim v氏名Item As 氏名
' For Each v氏名Item In v氏名リスト.独自Item 'エラー：引数は省略できない独自Itemsではできない
' Debug.Print v氏名Item.職員番号
' Debug.Print v氏名Item.氏名
' Next v氏名Item
Stop
' ' 氏名にアクセスして氏名のプロパティを表示
' Dim v氏名Item As 氏名
' For Each v氏名Item In v氏名リスト.独自Item() '()付けても出来なかった。エラー：引数は省略できない
' Debug.Print v氏名Item.職員番号
' Debug.Print v氏名Item.氏名
' Next v氏名Item
Stop
End Function
-------------------------------------------
private変数にしたせいだと思いますが、
上記の
' Debug.Print v氏名リスト.Items.Item(1).職員番号
' Debug.Print v氏名リスト.Items.Item("54").氏名
もアクセスできなくなりました。
「独自Item」だけしか、入れなくなる。？？？汗（このcollection「Items」がパブリックでなくなったからか？。）
（クラスモジュール「氏名リスト」内で使う変数は、mItems(private)だからか？？？）
（６）オマケ
上記だと、debug.printをしても、標準モジュール(function)では、
Debug.Print v氏名リスト.独自Item(1).職員番号 '独自Itemでしかアクセスできない
Debug.Print v氏名リスト.独自Item("54").氏名
これしか動きません。（自分の能力不足か・・・）
で、クラスモジュール「氏名リスト」にDebug.Printを置いてみました。
---------------------------------------------
Option Explicit
'Public Items As Collection
Private mItems As Collection
Private Sub Class_Initialize()
Set mItems = New Collection
With ThisWorkbook.Sheets("sime")
Dim i As Long
i = 1
Do While .Cells(i, 3).value <> "" '氏名が空になるまで
Dim v氏名 As 氏名
Set v氏名 = New 氏名
v氏名.Initialize .Range(.Cells(i, 1), .Cells(i, 8))
mItems.Add v氏名, v氏名.職員番号
i = i + 1
Loop
End With
'コレクションの要素についてループ
For Each v氏名 In mItems
Debug.Print v氏名.職員番号, v氏名.氏名
Next v氏名
End Sub
Public Property Get 独自Item(ByVal pKey As Variant) As 氏名 'pKeyはインデックスでも、キーでも受け取れるようにvariant
Set 独自Item = mItems.Item(pKey)
End Property
------------------------------------------
↑これは、うまく動きました。Debug.Print v氏名.職員番号, v氏名.氏名
（７）その他
for eachを使ったDebug.Print文について、標準モジュールでは、うまくいかなかった点について、
-----------------------------
'Private m独自Item As Collection
'Public Property Let 独自Item(p独自Item As Variant)
'
' m独自Item = p独自Item
'End Property
-----------------------------
とか、Property Letを使ってもダメでした。今は放置です。ああ。
ちなみに、今回の記事の処理ですが、クラスはクラスでも、シートからの読み込みなので、collectionでなく、配列でやろうかなと改めて考えています。
追記：コンボボックス、スタックオーバーフローしたので、どうしよう・・・
