Auto_naming object varibles

[rbarnhart1]

I'm just starting to learn how to create custom classes and objects and I don't fully understand it all yet. I want to create multiple objects from my custom class. But, I don't know how many I will need (it will be different each time I run the program). Because of this, I can't create object variable names in advance (if I knew I only needed three objects, I could create myObj1, myObj2, and myObj3 in advance).

In Excel, if I create a button, it is auto-named CommandButton1. If I create another one, it is called CommmandButton2. How do I do that in my own classes?

Since I don't know how many objects I'll need, the only option I can think of is this:

ReDim object_arr (1 to max_size) As New myObjectClass

It seems like there has to be a better way. Any suggestions will be appreciated.

 

[Jaafar Tribak]

I'm just starting to learn how to create custom classes and objects and I don't fully understand it all yet. I want to create multiple objects from my custom class. But, I don't know how many I will need (it will be different each time I run the program). Because of this, I can't create object variable names in advance (if I knew I only needed three objects, I could create myObj1, myObj2, and myObj3 in advance).

In Excel, if I create a button, it is auto-named CommandButton1. If I create another one, it is called CommmandButton2. How do I do that in my own classes?

Since I don't know how many objects I'll need, the only option I can think of is this:

ReDim object_arr (1 to max_size) As New myObjectClass

It seems like there has to be a better way. Any suggestions will be appreciated.

Normally, each new Object is added to a Collection object declared Public so as not to lose state.
You can use the Collection Key argument to give a unique ID (like a name) to each created object so you can later on refer to the objects via their unique IDs.

Public oPersons As Collection

Sub CreatePersons()

    Dim oPerson As C_Person
    
    Set oPersons = New Collection
    
    Set oPerson = New C_Person
    oPerson.Name = "John"
    oPersons.Add oPerson, CStr(oPerson.Name)
    
    Set oPerson = New C_Person
    oPerson.Name = "Erik"
    oPersons.Add oPerson, CStr(oPerson.Name)
    
    Set oPerson = New C_Person
    oPerson.Name = "Claire"
    oPersons.Add oPerson, CStr(oPerson.Name)

End Sub

Sub QueryPersons()

    MsgBox oPersons("Erik").Name

End Sub

You can aslo use a loop to create and add the objects to the collection.

 

[rbarnhart1]

Thanks for the response, Jaafar Tribak. That is an interesting solution. I was a little surprised by the use of the same object variable (oPerson) to create multiple objects. So, basically, once you assign the object to the Collection, you can reuse the object variable...is that correct? I'm guessing that by assigning it to the Collection, you're reassigning the reference to the object.

There is still one problem that I don't understand. The example you provided works well if you intend to create the objects manually. In my case, I will need to create an undetermined number of objects each time I run the program.

Even if I had a loop that operated to max_count, I would have to figure a way to create the unique keys. Maybe something like this:

Public oPersons As Collection

Sub Create_Persons ()

Dim sPrefix As String
Dim lCount As Long
Dim lMaxCount as Long
Dim oPerson as C_Person
Dim sKey as String

sPrefix = "Accountants"

Set cPersons = New Collection


lMaxCount = 200 'this would actually be determined by the number of rows on the sheet...but this will work for simplicity

For lCount = 1 to lMaxCount

        Set oPerson = New C_Person
    oPerson.AccountantId = Range(F2).Value 'again, this is just a snippet of how it might look in my code. I'm setting the AccountantID property of C_Person from a cell on the sheet
    oPerson.Name = Range(B2).Value

    sKey = CStr(prefix + lCount)
    
    oPersons.Add oPerson, CStr(sKey)

Next lCount

End Sub

I haven't checked that to see if everything in my example is valid, but hopefully it explains my thought process. Do you think that would be a good solution to my problem? Or am I totally lost?

 

[Jaafar Tribak]

I would use the AccountantId Property as the Key as it is supposed to be unique for each Person.

oPersons.Add oPerson, CStr(oPerson.AccountantId)

 

[rbarnhart1]

I would use the AccountantId Property as the Key as it is supposed to be unique for each Person.

Unfortunately, the data I'm using is not setup properly and, since I don't own the data, I have no control over it. There isn't one field in a given record that is guaranteed to be unique.

Granted, each user has their own unique user ID, but in this particular dataset, they show up in multiple records. I wish I could use your suggestion, but the tables and query would have to be redesigned

Since I don't have a unique field, would the example I provided work?

 

[Jaafar Tribak]

Since I don't have a unique field, would the example I provided work?

In that case , maybe use this :

 sKey = CStr(oPerson.AccountantId & "_" & lCount)

or

sKey = CStr(oPerson.Name & "_" & lCount)

 

[rbarnhart1]

Sounds good. Thank you for your help.