野口香社会保険労務士事務所

群馬県にある社会保険労務士事務所のページです。

  • 【VBA】クラスモジュールの使いどころーコレクションで使う―

【VBA】クラスモジュールの使いどころーコレクションで使う―

  • [記事公開]2023.02.26[最終更新]2023.03.06
  • VBA

前にこういう記事を書きました。

【VBA】クラスモジュールの使いどころ

記事というより、パッと頭に浮かんだことを忘れないようにメモしただけのものなんですが、このときの結論は「クラスモジュールの使いどころはない」というものでした。

あれからいろいろ考えていて、その結論は早計だという気がしています。では何に使えるのか？というと、CollectionとかDictionaryです。

あ、この記事は初心者向けではないです。中級～上級者向けです。初心者の方はブラウザのバックボタンで他の有益な記事の海へ戻っていってください。

目次

CollectionかDictionaryで使う

クラスモジュールはCollectionaかDictionaryで使います。

長年VBAを使ってきましたが、これに落ち着きました。

使い方

クラスモジュールを挿入します。

メンバ名をPublicで宣言します。

標準モジュールや他のモジュールでこのクラスを呼び出して使います。

呼び出し方は、まずDimで宣言→初期化→使うの順です。

初期化のタイミングについては、重要な注意点があります。ループ内でクラスをインスタンス化した変数を使う場合、ループの中で初期化しないといけません。これは慣れないと大変奇妙ですが、VBAの仕様です。

    '配列からコレクションに値を転記する
    Dim ii As Long
    Dim member As Class1
    For ii = LBound(data, 1) To UBound(data, 1)
        
        'クラス用インスタンス初期化
        Set member = New Class1                 '・・・⑤
        
        '各メンバに値をセット
        member.stName = data(ii, 2)             '・・・⑥
        member.lNumber = data(ii, 1)
        member.stClass = data(ii, 6)
        
        'コレクションにメンバを追加
        members.Add member, CStr(ii)            '・・・⑦
    Next ii

上記のコードでは⑤で初期化しています。ループの中です。

初期化のタイミングを誤ると・・・

これを例えばループが始まる直前に初期化して使った場合、正しいコレクションが作れません。なぜかというと、VBAにおいてクラスのインスタンス初期化は、Newをしたタイミングではなく、Newをして最初に値を代入したときに行われるからです。上記の例で言うと、あたかも⑤で初期化しているように人間の目には見えますが、実際には⑥の代入が行われる直前で初期化が行われています。

もう一つ、重要な点があります。Newは、メモリ上の番地に割り当てを行います。Newした回数分メモリ上の番地の割り当てを行います。C言語のポインタのようなものと考えていただければよいと思います。

つまり、ループの中でNewしないと、メモリ番地が１つだけmemberのために割り当てられて終わりになるのです。

何を言っているか分からない方はこの部分は読み飛ばしていいです。変数の割り当てとメモリの番地の知識がないと理解が難しいです。

こういうのは自分で作って失敗してみればいいんです。そうすると頭が理解しなくても身につきます。私も実は本当の意味では理解していません。ただ何回も同じ失敗をしたので身につきました・・・orz

失敗の例。ループ外で初期化した結果、こういう実際にはありえない結果になる。

Enumとセットで使う

ここからがちょっとディープな話になります。

私はEnumとセットで使うことが多いです。その方が可読性が高くなるし、コーディング量も減るからです。

例えば、次のようなコードがあったとします。処理自体は表の中から営業部の人を抽出して表示するだけの単純なプログラムです。

モジュールとワークシート

まずはクラスモジュールです。

Option Explicit

Public stName As String     '氏名
Public lNumber As Long      '社員ナンバー
Public stClass As String    '部署名

標準モジュールはこうです。

Option Explicit

Public Sub クラスを使う実験()

    Dim data As Variant
    Dim lRows As Long, lCols As Long
    
    'Excel上のデータを配列に読み込む
    With Range("A3")
        lRows = .CurrentRegion.Rows.Count - 1   '・・・①
        lCols = .CurrentRegion.Columns.Count    '・・・②
        data = .Resize(lRows, lCols).Value      '・・・③
    End With
    
    'コレクションを初期化する
    Dim members As Collection
    Set members = New Collection                '・・・④
    
    '配列からコレクションに値を転記する
    Dim ii As Long
    Dim member As Class1
        
    For ii = LBound(data, 1) To UBound(data, 1)
        
        'クラス用インスタンス初期化
        Set member = New Class1                 '・・・⑤        

        '各メンバに値をセット
        member.stName = data(ii, 2)             '・・・⑥
        member.lNumber = data(ii, 1)
        member.stClass = data(ii, 6)
        
        'コレクションにメンバを追加
        members.Add member, CStr(ii)            '・・・⑦
    Next ii
    
    'コレクションの件数を把握
    Dim lCnt As Long
    lCnt = members.Count
    
    'コレクションを使う
    Dim Buf As String
    For ii = 1 To lCnt
        If members(ii).stClass = "営業部" Then   '・・・⑧
            Buf = Buf & members(ii).stName & "、"
        End If
    Next ii
    
    MsgBox "営業部は" & Left(Buf, Len(Buf) - 1) & "です。"
    
    '後始末
    Set members = Nothing
    Set member = Nothing
End Sub

ワークシートはこうです。

解説

①CurrentRegionでカタマリをとってきています。行数からマイナス1しているのは、ヘッダ行の分を差し引いています。

②列数を把握しています。

③セルA3を基準に、Resizeして値を配列dataに一気に転記しています。

④コレクションを初期化しています。

⑤Class1型の変数(これをインスタンス変数と言ったりします)memberを初期化しています。ちなみに、クラスモジュールをコレクションで使うとき、コレクション名とクラス用変数名はsをつけるかつけないかの違いだけにしておくと、分かりやすいです。

クラス用インスタンス変数名　member

コレクション名　members

⑥dataの内容をmemberの各メンバに値をセットしています。

⑦コレクションにmemberをAddしています。Keyは文字列である必要がありますので、Cstr関数で数字を文字列に変換しています。

⑧コレクションのメンバを呼び出すときはこんな風に書きます。members.Items(ii).stClassという風に書くこともできますが、冗長になるのでたいていmembers(ii)で始まる形にします。

Enumを追加

以上のままでも別に使えるんですが、ちょっとめんどくさいのがここ↓

        '各メンバに値をセット
        member.stName = data(ii, 2)             '・・・⑥
        member.lNumber = data(ii, 1)
        member.stClass = data(ii, 6)

dataの2次元の要素番号で氏名や番号や部署名を転記しているところです。これはいちいちワークシートに戻って、左から何番目だから・・・と数えて記入しました。めんどくさいです(｡･ˇДˇ･｡)

そこでEnumの出番。

Enum Sheet1
    連番 = 1
    氏名
    ふりがな
    性別
    生年月日
    部署名
End Enum

これを標準モジュールのPublic Subの上に定義しておきます。そうするとVBEが予測変換して候補を表示してくれるようになります。これをインテリセンス(予測補完機能)と言います。

コーディングのときインテリセンスがきいて楽になる

Enumを追加した後のコードがこちら↓

        '各メンバに値をセット
        member.stName = data(ii, e.氏名)             '・・・⑥
        member.lNumber = data(ii, e.連番)
        member.stClass = data(ii, e.部署名)

どうでしょう？さっきよりぐっと可読性が上がったと思いませんか？

コーディングのときに補完もしてくれるので、時間短縮のためにも私はEnumとクラスモジュールを使っていました。

使いどころ

今回の事例は小さい表だったのでクラスモジュールを使うメリットを全く感じないと思います。

ところが、例えば対象となる表の列数が1000列あるとしたらどうでしょう？

ワークシート3つから4つかに分かれて、1つのシートに列数が255列もあるような、そんな巨大な表です。

そんな表は使ったことがないって？

それはよかったです。一生そのままそういう極悪な表を経験することなく終わるといいですね！＾＾

ところが巷にはそういう極悪非道な表があふれていまして・・・・たとえばこちら↓

https://www.moj.go.jp/isa/content/001351564.xlsm

これは在留申請オンラインシステムにおける在留資格変更許可申請用（その他：4.1MB）のテンプレートファイルなんですが、とてつもなくでかいです＾＾；

データを入力するシートの先頭部分

横に長くて、ずっと横にスクロールしていくと・・・・

DA列までありました。列番号にして105番です・・・・。長すぎ。しかもこれで終わりじゃないんです。下のシートのタブを見ると身分事項2シートとか申請情報入力シートがあるのが見えますか・・・？これらのシート全部を入力しないとオンライン申請できないという仕組みになっています・・・・。申請情報入力(区分Y )シートの場合、最終列番号はHDでした(数字にして212！)。さすがにこれを手入力するのは難しいと思いませんか・・・・？極悪すぎ、時間泥棒すぎ。これを出入国在留管理庁が提示してきたときは、ドン引きしたものです。働き方改革とか過労死なくそうとか長時間労働を是正しようとかいう時代の流れをどう考えているのかなあ？と遠い目になりながら・・・。

会社で使っているデータベースから値を抽出してCSVで吐き出して、それをこのExcelテンプレートに値をセットするという仕組みを作りたくなってしまうのも仕方ないと思います・・・。こんなの手作業で入力していたら、時間がいくらあっても足りないです。

そういうときにEnumとクラスモジュールを使ってさくっとCSV転記マクロを作るという訳です。

PropertyGetとPropertySetは作らない

ここからはJavaご経験者の方だけが読めばよいです。

私は次のようにクラスを作りました。

Option Explicit

Public stName As String     '氏名
Public lNumber As Long      '社員ナンバー
Public stClass As String    '部署名

「え？！なにそれ？！SetterとGetterは！？」と思ったあなた。そう、その疑問は当然です。

でもご心配ご無用！VBAでももちろんSetterとGetterを作れます。こんな風に↓

Option Explicit

Private stName As String     '氏名
Private lNumber As Long      '社員ナンバー
Private stClass As String    '部署名

'氏名
Public Property Set Name(argName As String)
    stName = argName
End Property
Public Property Get Name() As String
    Name = stName
End Property

'社員番号
Public Property Set Number(argNumber As Long)
    lNumber = argNumber
End Property
Public Property Get Number() As Long
    Number = lNumber
End Property

'部署名
Public Property Set Busho(argBusho As String)
    stClass = argBusho
End Property
Public Property Get Busho() As String
    Busho = stClass
End Property

作れるけど・・・・作らないです。データのカプセル化を要求されるようなシビアな場面というのはVBAではほとんどないです。少なくとも私は経験したことがないです。

どうせカプセル化しないのなら、わざわざめんどくさいセッターとゲッターなんて作らないでPublicで宣言してしまえ！というのが私のコードです。

たとえば、先ほど示した在留資格のテンプレートファイルを私がVBAで使うとしたら、こんな風に作ります(全部ではない。一部だけ)

Option Explicit

Public Enum Mbn1
    a001国籍地域
    a002氏名
    a003性別
    a004生年月日
    a005
    a006
    a007
    a008
    a009
    a010配偶者の有無
    a011職業
    a012本国における居住地
    a013住居地_都道府県
    a014住居地_市区町村
    a015住居地_町名丁目番地号等
    a016電話番号
    a017携帯電話番号
    a018メールアドレス
    a019旅券番号
    a020旅券有効期限
    a021
    a022
    a023
    a024
    a025
    a026犯罪を理由とする処分を受けたことの有無
    a027有を選択した場合に犯罪を理由とする処分を受けたことの具体的内容を入力
    a028在日親族の有無
    a029在日親族1国籍地域
    a030在日親族1氏名
    a031在日親族1生年月日
    a032
    a033
    a034
    a035
    a036
    a037在日親族1続柄
    a038在日親族1同居の有無
    a039在日親族1勤務先名称通学先名称
    a040在日親族1在留カード番号
    a041在日親族2国籍地域
    a042在日親族2氏名
    a043在日親族2生年月日
    a044
    a045
    a046
    a047
    a048
    a049在日親族2続柄
    a050在日親族2同居の有無
    a051在日親族2勤務先名称通学先名称
    a052在日親族2在留カード番号
    a053在日親族3国籍地域
    a054在日親族3氏名
    a055在日親族3生年月日
    a056
    a057
    a058
    a059
    a060
    a061在日親族3続柄
    a062在日親族3同居の有無
    a063在日親族3勤務先名称通学先名称
    a064在日親族3在留カード番号
    a065在日親族4国籍地域
    a066在日親族4氏名
    a067在日親族4生年月日
    a068
    a069
    a070
    a071
    a072
    a073在日親族4続柄
    a074在日親族4同居の有無
    a075在日親族4勤務先名称通学先名称
    a076在日親族4在留カード番号
    a077在日親族5国籍地域
    a078在日親族5氏名
    a079在日親族5生年月日
    a080
    a081
    a082
    a083
    a084
    a085在日親族5続柄
    a086在日親族5同居の有無
    a087在日親族5勤務先名称通学先名称
    a088在日親族5在留カード番号
    a089在日親族6国籍地域
    a090在日親族6氏名
    a091在日親族6生年月日
    a092
    a093
    a094
    a095
    a096
    a097在日親族6続柄
    a098在日親族6同居の有無
    a099在日親族6勤務先名称通学先名称
    a100在日親族6在留カード番号
    a101住民税の納税額_直近年度
    a102在留カードの受領方法
    a103受領官署
    a104申請人である外国人本人の通知送信用メールアドレス
    TheEnd = a104申請人である外国人本人の通知送信用メールアドレス
End Enum

頭にa001などのプレフィックスをつけるのは、インテリセンスのリストとなったときに順番に表示されるようにです(アルファベット順で表示されてしまうので)。

最後のTheEndは、最後の列番号を手軽にゲットできるようにです。これはTwitterで紹介されていてノウハウです(情報源を忘れました・・・)。

セル結合されている列もブランクとしてカウントします。そうしないといちいち「セル結合はここからここまでだから、じゃあこの項目は次は何番から開始・・・」ということを設定しないといけないのでめんどくさくなるからです。

クラスモジュールはこんな↓風になります。

Option Explicit

Public a001国籍地域 As String
Public a002氏名 As String
Public a003性別 As String
Public a004生年月日 As String
Public a005 As String
Public a006 As String
Public a007 As String
Public a008 As String
Public a009 As String
Public a010配偶者の有無 As String
Public a011職業 As String
Public a012本国における居住地 As String
Public a013住居地_都道府県 As String
Public a014住居地_市区町村 As String
Public a015住居地_町名丁目番地号等 As String
Public a016電話番号 As String
Public a017携帯電話番号 As String
Public a018メールアドレス As String
Public a019旅券番号 As String
Public a020旅券有効期限 As String
Public a021 As String
Public a022 As String
Public a023 As String
Public a024 As String
Public a025 As String
Public a026犯罪を理由とする処分を受けたことの有無 As String
Public a027有を選択した場合に犯罪を理由とする処分を受けたことの具体的内容を入力 As String
Public a028在日親族の有無 As String
Public a029在日親族1国籍地域 As String
Public a030在日親族1氏名 As String
Public a031在日親族1生年月日 As String
Public a032 As String
Public a033 As String
Public a034 As String
Public a035 As String
Public a036 As String
Public a037在日親族1続柄 As String
Public a038在日親族1同居の有無 As String
Public a039在日親族1勤務先名称通学先名称 As String
Public a040在日親族1在留カード番号 As String
Public a041在日親族2国籍地域 As String
Public a042在日親族2氏名 As String
Public a043在日親族2生年月日 As String
Public a044 As String
Public a045 As String
Public a046 As String
Public a047 As String
Public a048 As String
Public a049在日親族2続柄 As String
Public a050在日親族2同居の有無 As String
Public a051在日親族2勤務先名称通学先名称 As String
Public a052在日親族2在留カード番号 As String
Public a053在日親族3国籍地域 As String
Public a054在日親族3氏名 As String
Public a055在日親族3生年月日 As String
Public a056 As String
Public a057 As String
Public a058 As String
Public a059 As String
Public a060 As String
Public a061在日親族3続柄 As String
Public a062在日親族3同居の有無 As String
Public a063在日親族3勤務先名称通学先名称 As String
Public a064在日親族3在留カード番号 As String
Public a065在日親族4国籍地域 As String
Public a066在日親族4氏名 As String
Public a067在日親族4生年月日 As String
Public a068 As String
Public a069 As String
Public a070 As String
Public a071 As String
Public a072 As String
Public a073在日親族4続柄 As String
Public a074在日親族4同居の有無 As String
Public a075在日親族4勤務先名称通学先名称 As String
Public a076在日親族4在留カード番号 As String
Public a077在日親族5国籍地域 As String
Public a078在日親族5氏名 As String
Public a079在日親族5生年月日 As String
Public a080 As String
Public a081 As String
Public a082 As String
Public a083 As String
Public a084 As String
Public a085在日親族5続柄 As String
Public a086在日親族5同居の有無 As String
Public a087在日親族5勤務先名称通学先名称 As String
Public a088在日親族5在留カード番号 As String
Public a089在日親族6国籍地域 As String
Public a090在日親族6氏名 As String
Public a091在日親族6生年月日 As String
Public a092 As String
Public a093 As String
Public a094 As String
Public a095 As String
Public a096 As String
Public a097在日親族6続柄 As String
Public a098在日親族6同居の有無 As String
Public a099在日親族6勤務先名称通学先名称 As String
Public a100在日親族6在留カード番号 As String
Public a101住民税の納税額_直近年度 As String
Public a102在留カードの受領方法 As String
Public a103受領官署 As String
Public a104申請人である外国人本人の通知送信用メールアドレス As String

プレフィックスをつけるのはEnumと同じ理由です。リストアップされたときに出現順に並んでいた方が探しやすいからです。

全部String型としていますが、数字属性がはっきりしているものはLong型にしてもよいと思います。

これらの宣言は、すべてExcelで作っています。それほど手間もかかりません。元ネタ(ワークシートの横に長い列)をコピーし、別の空白のシートで値を選択して貼り付け→縦横を逆にして貼り付けを選んで貼り付けます。

その後、変数名には使えない文字(,や/や空白など)を置換で消して、プレフィックスやPublicやAs Stringなどの文字列の列を用意し(オートフィルで簡単に作ることができます)、アンパサント(&)記号でくっつけていくだけです。その数式をオートフィルして一気に作成し、コピーしてモジュールに貼り付けています。

何が言いたいかというと、これだけ巨大な表の列の項目一つ一つにセッターもゲッターも作ってられっか！という話です＾＾；

全部Public宣言してしまえば楽ですよというお話でした。