CODEWITHSUNDEEP
vbaexcel
Andrew Perry
Andrew Perry

Reputation: 765

Can I create variables dynamically based on other variables?

I'm working on a VBA module to process lists of quote items. My current boggle is trying to stack full or partial sets of things from the quote lists, and I'm trying to figure out how to keep track of them.

The item lists do not have a consistent number of items; one might be a single item, another might be a hundred.

The system divides the cargo into four broad types (Pipes, Plates, Beams and Other) for the sake of selecting which calculator logic to use.

Is there any way to create variables on the fly to keep track of individual line items? For instance, deploying a spot of pseudocode:

Public Qty & "_" & Class & "-" & ClassCount As Integer

Is there any way to make something like that work, or is there a better way to do it?

Upvotes: 1

Views: 94

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

I'm a bit sketchy on classes, and I really should start looking at them more as they're very powerful - this link will give you more info: http://www.cpearson.com/excel/classes.aspx

Expanding on Jasons comments this is one way of building the class, and I'm sure there's a much better way of doing it:
Add a Class Module to your project and name the module cls_Quote.
Add this code to the class module: 

Option Explicit

Private sQuote As String
Private lQuantity As Long
Private lAnotherValue As Long

Public Property Let Quote(Value As String)
    sQuote = Value
End Property
Public Property Get Quote() As String
    Quote = sQuote
End Property

Public Property Let Quantity(Value As Long)
    lQuantity = Value
End Property
Public Property Get Quantity() As Long
    Quantity = lQuantity
End Property

Public Property Let AnotherValue(Value As Long)
    lAnotherValue = Value
End Property

In a normal module add this code:

Option Explicit

Private MyQuotes As Collection

Public Sub Test()

    Dim MyNewQuote As cls_Quote
    Dim x As Long
    Dim vIQuote As Variant
    Dim FinalSum As Long

    Set MyQuotes = New Collection

    For x = 1 To 10
        Set MyNewQuote = New cls_Quote
        With MyNewQuote
            .Quantity = x
            .Quote = "Pipes"
            .AnotherValue = x * 5
        End With
        MyQuotes.Add MyNewQuote
    Next x

    For Each vIQuote In MyQuotes
        If vIQuote.Quote = "Pipes" Then
            FinalSum = FinalSum + vIQuote.Quantity
        End If
    Next vIQuote

    MsgBox "Total sum of Pipes is: " & FinalSum

End Sub

Note: In the For x loop I'm creating a new instance of the class each time.

Now just waiting for someone with more class programming experience to show a much better way of doing it. :)

Upvotes: 1

Related Questions