call dictionary from one sub to another in VBA-CodePudding

I'm stuck with a problem when I'm working with a dictionary in VBA. The reason why I want to work with a dictionary and a do-while loop is because I have variables with different length, that I want to loop through. First I want to give the dic keys and and items. The reason why I skip one col for each loop is because each series has a col with dates and then a col with prices. If it is possible I want to capture the dates that match the prices in the same dictionary.

Sub opg1(dicSPX As Object)

    Dim i As Integer, m As Integer
    Dim varColLeng As Variant

    Set dicSPX = CreateObject("Scripting.Dictionary")

    m = 10

    Sheets("Data").Activate
    
    ReDim intCol(1 To nCol)
   'opretter dictionary
    ReDim n(1 To m)
    Do While n <> ""
        For i = 1 To mn
'            redim preserve IntColLen
            dicSPX.Add Cells(1, 2   ((i - 1) * 2)).Value, Range(Cells(9, 2   ((i - 1) * 2)), Cells(n, 2   ((i - 1) * 2))).Value
        Next i
    Loop
End Sub

then I want to execute a procedure for all keys in my dic. I want to compute returns in different time series. However, when I call the dic, to the sub Returns() I get an error (Compile error: Variable not defined). I'm new to dictionaries and I probably missed a small detail.

Sub Returns()

    Call opg1(dicSPX)
    
    Dim dicSPX As New Scripting.Dictionary
    Dim varKey As Variant, varArr As Variant
    
    For Each varKey In dicSPX
        varArr = dicSPX(varKey)
        For i = LBound(varArr, 1) To UBound(varArr, 1)
            For j = LBound(varArr, 2) To UBound(varArr, 2)
'                varReturns(i,j) = compute  the return here
            Next
        Next
    Next

Any suggestions? I hope the question is clear.

Thank you

CodePudding user response：

It should be clear which variable is not defined by the line which is highlighted after the error is thrown. At a quick glance I can tell that you didn't define i or j:

Sub Returns()
    Dim dicSPX As New Scripting.Dictionary
    opg1(dicSPX)
        
    Dim varKey As Variant
    For Each varKey In dicSPX
        Dim varArr As Variant
        varArr = dicSPX(varKey)
        Dim i As Long
        For i = LBound(varArr, 1) To UBound(varArr, 1)
            Dim j As Long
            For j = LBound(varArr, 2) To UBound(varArr, 2)
                varReturns(i,j) = compute  the return here
            Next
        Next
    Next
End Sub

You should always explicitly define each variable, one line at a time, right before they are used. This way the declaration is always near the usage so you can see it on the same page.

You should also make opg1 a function that returns a dictionary. That would clarify your intent. Passing a variable by reference makes it harder to tell how the program works to the reader.