How to calculate the Correlation of multiple Dictionaries?

[Manuel Cavero]

Hello everyone:

I have some dictionaries with values, and I need to calculate the correlation between the values of dictionaries. Let's see the code:

Sub Test()
Dim Dic1 as New Scripting.Dictionary
Dim Dic2 as New Scripting.Dictionary
Dim Dic3 as New Scripting.Dictionary
Dim Dictionaries as New Collection
Dim Correlation as double
Dim Dictionary as New Scripting.Dictionary
........
With Dic1[INDENT].add 1,2
.add 1,3
.add 1,4[/INDENT]
End With

With Dic2[INDENT].add 1,12
.add 1,13
.add 1,14[/INDENT]
End With

With Dic3[INDENT].add 1,22
.add 1,23
.add 1,24[/INDENT]
End With

'And here I'd need to loop through the dictionaries to determinate the correlation ?????

For each Dictionary in Dictionaries[INDENT]Application.WorksheetFunction.Correl(Dic1.items, Dic2.items)[/INDENT]
Next  Dictionary

end Sub

Here the problem is how to loop through the colecction of Dictionaries (Dictionary.items)?

 

[Domenic]

First, make sure that you set a reference to Microsoft Scripting Runtime, and then try...

Option Explicit

Sub test()
    
    Dim dic1                        As Scripting.Dictionary
    Dim dic2                        As Scripting.Dictionary
    Dim dic3                        As Scripting.Dictionary
    Dim allDictionaries             As Scripting.Dictionary
    Dim firstDictionary             As Scripting.Dictionary
    Dim secondDictionary            As Scripting.Dictionary
    Dim array1                      As Variant
    Dim array2                      As Variant
    Dim currentCorrelation          As Double
    Dim i                           As Long
    
    Set allDictionaries = New Scripting.Dictionary
    
    Set dic1 = New Scripting.Dictionary
    With dic1
        .Add "A", 2
        .Add "B", 3
        .Add "C", 4
    End With
    allDictionaries.Add "dic1", dic1
    
    Set dic2 = New Scripting.Dictionary
    With dic2
        .Add "A", 12
        .Add "B", 13
        .Add "C", 14
    End With
    allDictionaries.Add "dic2", dic2
    
    Set dic3 = New Scripting.Dictionary
    With dic3
        .Add "A", 22
        .Add "B", 23
        .Add "C", 24
    End With
    allDictionaries.Add "dic3", dic3
    
    For i = 1 To allDictionaries.Count - 1
        Set firstDictionary = allDictionaries.Items(i - 1)
        Set secondDictionary = allDictionaries.Items(i)
        array1 = firstDictionary.Items
        array2 = secondDictionary.Items
        currentCorrelation = Application.Correl(array1, array2)
        Debug.Print "Correlation between " & allDictionaries.Keys(i - 1) & " and " & _
            allDictionaries.Keys(i) & " = " & currentCorrelation
    Next i
    
    Set dic1 = Nothing
    Set dic2 = Nothing
    Set dic3 = Nothing
    Set allDictionaries = Nothing
    Set firstDictionary = Nothing
    Set secondDictionary = Nothing
    
End Sub

 

[Manuel Cavero]

Hi Domenic

Thanks a lot for your suggestion. It works fine to me!!!

This is a imaginative solution