CODEWITHSUNDEEP
vbaclassexcel
Orphid
Orphid

Reputation: 2852

Initializing an indefinite number of classes in VBA: Using variable to name instance of class

As always, this may be something of a newb question but here goes:

I have a Class with 15 properties. Each class represents information about an item of stock (how many there are, how many recently shipped etc). Each time a class is initialized by passing it a stock code, it gathers all the data from other sources and stores it as properties of the class.

I want to be able to initialize n number of classes, dependent on the length of a list (never more than 200). I want to name those classes by their stock code, so that I can call up the information later on and add to it. The only problem is I don't know how to use a variable to name a class. I don't really want to write out 200 classes long hand because I'm sure there is a better way to do it than Diming: Stock1 As C_ICODE, Stock2 As C_ICODE, Stock3 As C_ICODE etc and initializing them in order, until input (from ActiveCell) = "" or it hits the maximum list length of 200. I would like to create as many class instances as there are stock codes if possible, and generate them something like this:

PseudoCode:

For Each xlCell In xlRange
    strIN = xlCell.Value

    Dim ICode(strIN) As New C_ICODE

    ICode(strIN).lIcode = strIN
Next

Letting classname.lIcode = strIN provides the class with all the user input it needs and then it carries out various functions and subroutines to get the other 14 properties.

I would be very grateful if someone could let me know if this sort of thing is possible in VBA, and if so, how I could go about it? Definitely struggling to find relevant information.

Upvotes: 2

Views: 1144

Answers (2)

Steve Rindsberg
Steve Rindsberg

Reputation: 14809

I just did a quick test of this and it seems as though it might work for you. You can create an array to hold multiple instances of your class.

Sub thing()

    Dim cArray(1 To 10) As Class1
    Dim x As Long

    For x = 1 To UBound(cArray)
        Set cArray(x) = New Class1
    Next

    ' Assume the class has a property Let/Get for SomeProperty:
    For x = 1 To UBound(cArray)
        cArray(x).SomeProperty = x * 10
    Next

    For x = 1 To UBound(cArray)
        Debug.Print cArray(x).SomeProperty
    Next

End Sub

Upvotes: 2

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35863

You can use Dictionary object:

Dim ICode As Object
Set ICode = CreateObject("Scripting.Dictionary")

For Each xlCell In xlRange
    strIN = xlCell.Value

    ICode.Add strIN, New C_ICODE

    ICode(strIN).lIcode = strIN
Next

Upvotes: 6

Related Questions