Dynamically Set Object name in for loop

Question

I have a for loop with some conditions, I want to Create new Objects inside the for loop dynamically when met the conditions, i have commented the error in the code, Is there a way to accomplish this ? I am new to vba so xcuse my ignorant. Would appreciate any help.

Set rng1 = ThisWorkbook.Worksheets("Calculator").Range("M16:M30")
x = 1
For Each rcell In rng1.Cells
    If Not IsEmpty(rcell) Then
        If rcell = "1" Then
            Set family_member_&x = CreateObject("Scripting.Dictionary")       // ERROR HERE
            family_member_&x.Add "family_group", "FG_1"
            family_member_&x.Add "name", ThisWorkbook.Worksheets("Calculator").Range("B"&x)
            family_member_&x.Add "date_of_birth", ThisWorkbook.Worksheets("Calculator").Range("C"&x)
        ElseIf rcell = "2" Then
            Set family_member_&x = CreateObject("Scripting.Dictionary")
            family_member_&x.Add "family_group", "FG_2"
            family_member_&x.Add "name", ThisWorkbook.Worksheets("Calculator").Range("B"&x)
            family_member_&x.Add "relationship", ThisWorkbook.Worksheets("Calculator").Range("E"&x)        
        End If
        x = x +1
    End If
        

Next rcell

Usually when you think dynamic variables, think a Scripting.Dictionary. So you could create a dictionary of dictionaries. — BigBen
– BigBen, Commented Jan 21, 2022 at 16:40
how do you suggest it should be done with your approach, sorry but im not used to vba syntax and i want to call my api with those dictionaries :) — Abdulla Osama
– Abdulla Osama, Commented Jan 21, 2022 at 16:57
Create a Scripting.Dictionary outside your loop, then add new dictionaries to it within the loop using "family_member_" & x as the key. — BigBen
– BigBen, Commented Jan 21, 2022 at 17:01

Toddleson · Accepted Answer · 2022-01-21 17:12:43Z

1

It seems like you are wanting a Collection of family_member dictionaries. You can accomplish this by creating either a Collection or a Dictionary outside the loop and then adding each family member to that collection during the loop.

Sub Example()
    Dim Family As Object, familyMember As Object
    Set Family = CreateObject("Scripting.Dictionary")
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Calculator")
    
    Dim rng1 As Range
    Set rng1 = ws.Range("M16:M30")
    
    Dim rCell As Range
    For Each rCell In rng1.Cells
        If rCell = "1" Then
            Set familyMember = CreateObject("Scripting.Dictionary")
            familyMember.Add "family_group", "FG_1"
            familyMember.Add "name", ws.Range("B" & rCell.Row)
            familyMember.Add "date_of_birth", ws.Range("C" & rCell.Row)
            Family.Add familyMember("name"), familyMember
        ElseIf rCell = "2" Then
            Set familyMember = CreateObject("Scripting.Dictionary")
            familyMember.Add "family_group", "FG_2"
            familyMember.Add "name", ws.Range("B" & rCell.Row)
            familyMember.Add "relationship", ws.Range("E" & rCell.Row)
            Family.Add familyMember("name"), familyMember
        End If
    Next rCell
    
End Sub

At the end of the loop, you now have a Dictionary named Family that has one item for each familyMember. The items are accessible by their name like Family("George") and the item's items are accessible like Family("George")("relationship"). You can also loop through the collection directly like For Each Member In Family.Items

answered Jan 21, 2022 at 17:12

Toddleson

4,4852 gold badges9 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Abdulla Osama Over a year ago

Thanks for your reply, is it possible to remove the ("name") part from the beginning of the dictionary? like i want to access the dictionaries of family_members by their index not a name. Thanks for your help. that really works well

Toddleson Over a year ago

You can access them by index using Family.Items()(index), no changes needed.

Collectives™ on Stack Overflow

Dynamically Set Object name in for loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related