VBA 動的にCollectionを生成

Question

###前提・実現したいこと
VBAで動的にCollectionを生成したいです。

###発生している問題・エラーメッセージ

Type ブロック外では無効なステートメントです。

###該当のソースコード

Sub Sample34()

    Dim people() As String
    people = Split("Tanaka", "Yamada")
 
    Dim Member() As String
    ReDim Member(UBound(people))
    
    Dim ii As Integer
    For ii = 0 To UBound(people)
        Member(people(ii))  As Collection
        Set Member(people(ii)) = New Collection
    Next
    
    Tanaka.Add 25, "age"
    Tanaka.Add 090, "number"
    
    Yamada.Add "[e-mail]", "mail"
    Yamada.Add "tokyo", "addres"
    
    MsgBox Tanaka.Item("age")               
    MsgBox Yamada.Item("mail")   

End Sub

###試したこと
田中と山田という人物に対して、年齢やメールアドレスを登録したいです。
登録項目は人物によって違います。(例)田中には年齢を登録するが、山田には登録しない
macを使っているのでDictionaryオブジェクトは使えません。
あとで人物全員に対して処理を行うことも考えられるので、
田中と山田をMemberというグループに所属させ、
あとでそのMemberに一括で処理ができるようにと考えております。

###補足情報(言語/FW/ツール等のバージョンなど)
macOS High Sierra バージョン10.13.2
Excel バージョン 15.41

Answer 1

やりたかったことは以下のようなことですか？

Sub Sample35()

    Dim people() As String
    people = Split("Tanaka,Yamada", ",")

    Dim members As Collection
    Set members = New Collection

    Dim ii As Long
    For ii = LBound(people) To UBound(people)
        members.Add New Collection, people(ii)
    Next ii
    
    Dim Tanaka As Collection
    Set Tanaka = members.Item("Tanaka")
    Tanaka.Add 25, "age"
    Tanaka.Add 90, "number"
    
    Dim Yamada As Collection
    Set Yamada = members.Item("Yamada")
    Yamada.Add "[e-mail]", "mail"
    Yamada.Add "tokyo", "addres"
    
    MsgBox Tanaka.Item("age")
    MsgBox Yamada.Item("mail")

End Sub

---

以下元のコードでミスしていると思われる箇所です。

Split関数

Dim people() As String
people = Split("Tanaka", "Yamada")

この書き方だと（少なくともWindows版では）"Tanaka"しかpeople()の中に入りません。

両方入れる場合は

Dim people() As String
people = Split("Tanaka,Yamada", ",")

または

Dim people() As Variant
people = Array("Tanaka", "Yamada")

の書き方になると思います。

Member()

Collectionを辞書的に使いたい、その辞書をMember()にまとめたい、と考えるとMember()の型はCollectionですよね？

Dim Member() As String
↓
Dim Member() As Collection

配列の添え字に文字列は使えないので

Set Member(people(ii)) = New Collection
↓
Set Member(ii) = New Collection

としか書けません。

Member(people(ii))  As Collection

の意図がいまいちわかりませんでしたが、もしかして型変換をしようとしているのでしょうか？

VBAでは値型のみCStrなどの関数で型変換できますが、それ以外は「その型の変数に入れる」ぐらいしか方法がありません。

なぜType ブロック外では無効なステートメントです。のエラーが発生したかをざっくり説明すると
ユーザー定義型のフィールド宣言の構文と同じ書き方をしたためです。

上記の行には予約後のAsが入っているため、変数宣言などの一種だと解釈されます。
しかし行頭にDimなどが存在しないため普通の変数宣言ではありません。
Asを使いつつ、行頭にDimなどが無いパターンはユーザー定義型のフィールド宣言時のみとなります。
しかし、書かれている場所はType~End Type（Typeブロック）で囲まれていないため、Type ブロック外では無効なステートメントです。のエラーが発生します。

'ユーザー定義型の定義
    'Personという名前でNameというString型のフィールドを持つ型
Type Person
    Name As String
End Type

---

17/12/26追記

Dim Tanaka As Collection

Set Tanaka = members.Item("Tanaka")
Dim Yamada As Collection
Set Yamada = members.Item("Yamada")
上記４行をfor文で動的にしたくて
For ii = LBound(people) To UBound(people)
members.Add New Collection, people(ii)
Dim people(ii) As Collection
Set people(ii) = members.Item(people(ii))
Next ii
としました。

結論から申し上げると**「VBAでは不可能」**です。

VBAではプロシージャの実行時に使用する変数を全て初期化するため、実行するまで名前の決まらない変数は作成できません。

出来たとしても、エディタの支援を全て投げ捨てることになるため、ただやりにくいだけになるかと思います。

雰囲気だけなら以下のような書き方もできますが、私個人としてはオススメしません。

With members
    'members!Tanaka と members("Tanaka") は同じ意味
    
    !Tanaka.Add 25, "age"
    !Tanaka.Add 90, "number"
    
    !Yamada.Add "[e-mail]", "mail"
    !Yamada.Add "tokyo", "addres"
    
    MsgBox !Tanaka.Item("age")
    MsgBox !Yamada.Item("mail")
    
End With

おまけ：エラーの原因

Dim people(ii) As Collectionは純粋にpeopleという名前の変数の宣言だと解釈されます。
さらに(ii)となっているため、静的な配列（要素数が固定の配列）変数の宣言と解釈されます。
しかしiiは変数のため、定数ではありません（固定されていません）。
結果として、「静的配列の変数宣言に定数を使っていない」と解釈され、「定数式が必要です。」のコンパイルエラーが発生します。
実際は変数宣言も重複しているため、「定数式が必要です。」を解消しても「同じ適用範囲内で宣言が重複しています。」のコンパイルエラーが発生します。

Comments

[iiirohanihoheto]

ご回答有り難うございます。
今は時間が取れないので、のちほどコメントいたします。
申し訳ありません。

[iiirohanihoheto]

遅くなり申し訳ございません。取り急ぎ気になった点をひとつ。

Dim people() As Variant
people = Array("Tanaka", "Yamada")
上記２行に変更いたしました。

Dim Tanaka As Collection
Set Tanaka = members.Item("Tanaka")
Dim Yamada As Collection
Set Yamada = members.Item("Yamada")
上記４行をfor文で動的にしたくて
For ii = LBound(people) To UBound(people)
members.Add New Collection, people(ii)
Dim people(ii) As Collection
Set people(ii) = members.Item(people(ii))
Next ii
としました。

しかし「定数式が必要です。」というエラーになりました。
原因はこちらでも時間の許す時に調べます。
取り急ぎ現状の報告でした。

[iiirohanihoheto]

ご返信が遅くなり誠に申し訳ございません。
「VBAでは不可能」とのこと、承知いたしました。
エラー原因の説明も丁寧にしてくださり、感謝いたします。
ありがとうございました。

Answer 2

シートにデータ（メンバー列、氏名、年齢、etc）を作って、
メンバー列の値をコレクションに入れて、
コレクション毎に処理する方法が、
簡単で分かりやすく、データの調整もしやすいと思います。

Comments

[iiirohanihoheto]

ご回答有り難うございます。
検討してみます。

Answer 3

【Mac】MacのExcelVBAでもDictionaryオブジェクトが使えた！というか自作（コピペ）できた！　という情報があります。

MacでもVBAでDictionary相当のオブジェクトが使えるらしいです。

Comments

[iiirohanihoheto]

ご回答有り難うございます。
Office 2011用と書かれていますが、私が使っているエクセルは最新版になります。
この情報は知っていましたが、
バージョン違いによる不具合が起きないか心配だったので使っていません。

Answer 4

「Member(people(ii)) As Collection」の前に「ReDim」を付けて下さい。

Comments

[iiirohanihoheto]

ご回答有り難うございます。
「配列要素のデータ型を変更することはできません。」というエラーになりました。

[nullpon]

よくみると最初の宣言が「Dim Member() As String」になってますね。「String → Collection」

[iiirohanihoheto]

アドバイスありがとうございます。
「String → Collection」した後に「ReDim」も追記しましたが、
「ReDim Member(people(ii)) As Collection」の行が
「型が一致しません」というエラーになります。