Need a help with loading data using class module (Derivative & Portfolio)

[demian]

Hi VBA Experts,

I thought I added a new module and added a function then set, and created the class using the defined function correctly.
But, it does not allow me to upload the data in the yellow highlighted area in the attached file.

Could you please tell me what I am missing to display the table regarding COB, Value, and Returns for each instruments using VBA?
Also, please be advised that I presented how the tables should look like starting in column AC using the excel function, and I put the range reference as Output_Cash, Output_Bond, Output_Stock, Output_Option, Output_CDS, and Output_Portfolio where I would like to load the data.


Class module for Portfolio

Option Explicit


Private pDerivatives As Collection


Public Property Get Derivatives() As Collection
    Set Derivatives = pDerivatives
End Property


Public Property Set Derivatives(lDerivatives As Collection)
    Set pDerivatives = lDerivatives
End Property


Function GetData(strInstrumentType As String) As Variant
    '10 Dates, Date, Value, Returns
    Dim Mat(1 To 10, 1 To 3) As Variant
    
    Dim i As Integer
    i = 1
    Dim Der As Derivative
    
    'Load the array with values and Dates for that instrument Type
    For Each Der In Derivatives
        If strInstrumentType = Der.InstrumentType Then
            Mat(i, 1) = Der.COB
            Mat(i, 2) = Der.Value
            i = i + 1
        End If
    Next Der


    'Returns
    For i = 2 To 11
        Mat(i, 3) = (Mat(i, 2) / Mat(i - 1, 2)) - 1
    Next i
    
    GetData = Mat
    
End Function

Class module for Derivative

Option Explicit


Private pValue As Double
Private pInstrumentType As String
Private pCOB As Date


Public Property Get Value() As Double
    Value = pValue
End Property


Public Property Let Value(lValue As Double)
    pValue = lValue
End Property




Public Property Get InstrumentType() As String
    InstrumentType = pInstrumentType
End Property


Public Property Let InstrumentType(lInstrumentType As String)
    pInstrumentType = lInstrumentType
End Property




Public Property Get COB() As Double
    COB = pCOB
End Property


Public Property Let COB(lCOB As Double)
    pCOB = lCOB
End Property

Regular Module

Option Explicit


Function PopulateArray() As Collection
    Dim cDers As Collection
    Dim Der As Derivative
    Set cDers = New Collection


Dim i As Integer


'Loads in all the instruments and Value from Spreadsheet
For i = 2 To 51
    Set Der = New Derivative
    Der.InstrumentType = Sheets("VaR").Cells(i, 1)
    Der.COB = Sheets("VaR").Cells(i, 2)
    Der.Value = Sheets("VaR").Cells(i, 3)     ' Let function
    cDers.Add Der
Next i


    Set PopulateArray = cDers
    
End Function


Sub TestGetPortfolioValue()


    Dim newPort As Portfolio
    Set newPort = New Portfolio
    Set newPort.Derivatives = PopulateArray()
    
Dim i As Integer, j As Integer
Dim Mat As Variant
Mat = newPort.GetData


'---------------------------------------------------
'load output_Cash range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_Cash").Cells(i, j) = Mat(i, j)
    Next j
Next i
   
   
'---------------------------------------------------
'load output_Stock range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_Stock").Cells(i, j) = Mat(i, j)
    Next j
Next i


   
'---------------------------------------------------
'load output_Bond range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_bond").Cells(i, j) = Mat(i, j)
    Next j
Next i
   
   
'---------------------------------------------------
'load output_Option range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_Option").Cells(i, j) = Mat(i, j)
    Next j
Next i
   
   
'---------------------------------------------------
'load output_CDS range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_CDS").Cells(i, j) = Mat(i, j)
    Next j
Next i
   
   
 '---------------------------------------------------
'load output_Portfolio range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_Portfolio").Cells(i, j) = Mat(i, j)
    Next j
Next i
   
   
End Sub

I do not fully understand the concept of the class.
I would really appreciate if you could correct my code.


Regards,
Demian

[Bob Phillips]

I get run time errors

Capture.jpg

[demian]

"Compile Error: Argument not optional". What does this mean?
Could you please enlighten me?

I still get the message " Compile Error: Variable not defined" after I change it as below...

Sub TestGetPortfolioValue()


    Dim newPort As Portfolio
    Set newPort = New Portfolio
    Set newPort.Derivatives = PopulateArray()
    
Dim i As Integer, j As Integer
Dim Mat As Variant
Mat = newPort.GetData(Cash)


'---------------------------------------------------
'load output_Cash range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_Cash").Cells(i, j) = Mat(i, j)
    Next j
Next i

[Paul_Hossler]

The calling procedure for GetData in class Portfolio ...

Function GetData(strInstrumentType As String) As Variant

... is looking for a string in the function call

The first time (in post #2) , you didn't have one and since it was required, it was not optional

The other error (in post #3) is telling you that you tried to use a string variable (called 'Cash') but that it was never defined (like Dim Cash As String)

[demian]

Thanks for simplifying it for me!
I was able to load the table for each instruments as below, except the Portfolio table.

Capture3.jpg

Dim i As Integer, j As Integer
Dim Mat As Variant
Mat = newPort.GetData("Cash")


'load output range with matrix from Getdata() Function
For i = 1 To 10
    For j = 1 To 3
        Range("Output_Cash").Cells(i, j) = Mat(i, j)
    Next j
Next i

Regarding the table for Portfolio, could you please provide me with your advice?
Any suggestions most welcome, I'm open to any approach.

Regards,
Demian