CODEWITHSUNDEEP
excelvba
FinDev
FinDev

Reputation: 439

VBA: How to loop through multiple variant arrays of the same length and turn them into Objects

I currently have 3 separate variants that are all arrays consisting of 13 rows and 1 column. One variant represents names, another represents changes and the last represents occurrences. Please see my starter code below:

Sub watchList()

    Dim names As Variant
    names = Sheets("Watch Calculations").Range("B4:B16")
    
    Dim changes As Variant
    changes = Sheets("Watch Calculations").Range("G4:G16")
    
    Dim occurances As Variant
    occurrences = Sheets("Watch Calculations").Range("G22:G34")
    
    
End Sub

I also have a class called counterParty with the following fields:

Public Name As String
Public changeStatus As String
Public negativeOccurences As Integer

How can I loop through all 3 variants at the same time and input the values into an object of the counterParty class based on the row number of each variant. Please see psuedo code below:


 Dim i As Integer
    Dim MyArray(1 To 13) As Integer
    For i = 1 To UBound(MyArray)
        
         'psuedo code stars here

         create new object of class counterParty
         set object.Name = names(i,1)
         set object.changeStatus = changes(i,1)
         set object.negativeOccurences= occurrences.get(i,1)

         add object to array of counterParty objects
    Next i

Upvotes: 0

Views: 696

Answers (1)

Warcupine
Warcupine

Reputation: 4640

Try this out

First the class module:

Private pName As String
Private pchangeStatus As String
Private pnegativeOccurrences As Long

Public Property Get Name() As String
    Name = pName
End Property

Public Property Let Name(lName As String)
    pName = lName
End Property

Public Property Get changeStatus() As String
    changeStatus = pchangeStatus
End Property

Public Property Let changeStatus(lchangeStatus As String)
    pchangeStatus = lchangeStatus
End Property

Public Property Get negativeOccurrences() As Long
    negativeOccurrences = pnegativeOccurrences
End Property

Public Property Let negativeOccurrences(lnegativeOccurrences As Long)
    pnegativeOccurrences = lnegativeOccurrences
End Property

Then the module:

    Dim names As Variant
    names = Sheets("Watch Calculations").Range("B4:B16")
    
    Dim changes As Variant
    changes = Sheets("Watch Calculations").Range("G4:G16")
    
    Dim occurrences As Variant
    occurrences = Sheets("Watch Calculations").Range("G22:G34")
    
    Dim i As Long
    Dim clsarr(1 To 13) As Object 'You can use lbound and ubound on names() to get dynamic values
    Dim mycls As Class1
    For i = 1 To UBound(names)
        Set mycls = New Class1 'Overwrite current object
        'assign values to the class properties
        mycls.Name = names(i, 1)
        mycls.changeStatus = changes(i, 1)
        mycls.negativeOccurrences = occurrences(i, 1)
        
        Set clsarr(i) = mycls
    Next i

Upvotes: 2

Related Questions