2

I want to pass a dictionary as a parameter. My code may seem a bit strange, but I'll show it anyway: 

Sub get_Insurers_Dictionary()

Dim cInsurers               As c_Insurers
Dim myDictionary            As Scripting.Dictionary

Set cInsurers = New c_Insurers

Set myDictionary = New Scripting.Dictionary
Set myDictionary = cInsurers.Get_Parameters_Dictionary(cInsurers, myDictionary)

End Sub

Then in the c_Insurers class: 

Public Function Get_Parameters_Dictionary(cInsurers As c_Insurers, myDictionary As Dictionary) As Dictionary

Dim oSheet                      As Excel.Worksheet
Dim cParameters                 As c_Parameters


Set oSheet = ThisWorkbook.Sheets("Parameters_Insurers")
oSheet.Activate

Set cParameters = New c_Parameters
Set cParameters = cParameters.Get_Parameters(cParameters, oSheet)

Me.fPartner_ID_Col = cParameters.get_Header_Cols(oSheet, cParameters.fMax_Col, "INSURER_ID")
Me.fPartner_Name_Col = cParameters.get_Header_Cols(oSheet, cParameters.fMax_Col, "INSURER_NAME")
Me.fCountry_Col = cParameters.get_Header_Cols(oSheet, cParameters.fMax_Col, "COUNTRY")

For lcnt = 1 To UBound(cParameters.fArray)
    myDictionary.Add cParameters.fArray(lcnt, Me.fPartner_ID_Col), Me.Get_Parameters_Class(cInsurers, cParameters, lcnt)
Next lcnt

Set Get_Parameters_Insurers = myDictionary
Set cParameters = Nothing

End Function

Aside from the seemingly complex code, my question is simple: is it even possible to add a class object as item? (I'm not used to working with dictionaries yet). My dictionary is filled in in the c_insurers class, but why is it empty when I get back to the get_Insurers_Dictionary?

html_programmer
  • 18,126
  • 18
  • 85
  • 158

1 Answers1

1

You can add whatever you like to a dictionary, Its not behaving as you expect because you pass the dictionary into Get_Parameters_Dictionary but you never return it after its been populated, instead you assign it to something else; Set Get_Parameters_Insurers = myDictionary.

This means Set myDictionary = cInsurers.Get_Parameters_Dictionary(cInsurers, myDictionary) resets myDictionary to Nothing (because of the unassigned return type).

You need to either Set Get_Parameters_Dictionary = myDictionary in Get_Parameters_Dictionary() or ... not bother; a dictionary is a reference type so after:

Set myDictionary = New Scripting.Dictionary
cInsurers.Get_Parameters_Dictionary cInsurers, myDictionary

myDictionary will reflect the changes made in Get_Parameters_Dictionary.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Yes, I noticed the mistake. I changed the name of "Get_Parameters_Insurers" to "Get_Parameters_Dictionary". I suppose that you mean "cInsurers, myDictionary" between round brackets? And then there is another problem, I cannot retrieve the correct class instance by doing: set cInsurers = myDictionary.Item(20) (as example). It returns me the wrong class instance, namely the last one added to the dictionary... any idea why that may be? Many thanks! – html_programmer Jun 11 '12 at 15:18
  • 1
    To call a function with arguments but not capture its return value you don't use brackets (you can if you prefix the call with `Call func(arg)`) - As for the other its usually because your re-using a reference so are infact adding the same thing 20 times; see http://stackoverflow.com/questions/2674341/excel-vba-passing-a-collection-from-a-class-to-a-module-issue – Alex K. Jun 11 '12 at 16:12
  • God damn, thanks man that's it. I'm re-using the same c_Insurers class object, how dumb. Just a very important question; can I release the c_Insurers object after having it added to the dictionary, in other words, is a copy of the object being made? And how many objects can be stored in a dictionary without the risk for a stack overload? – html_programmer Jun 11 '12 at 17:22
  • 1
    If you `dict.add "key", c_Insurers` a copy of the reference in `c_Insurers` is made so you can `set c_Insurers=nothing` without affecting the stuff in the dictionary (Its not a copy of the object your adding; its a reference; `c_Insurers` & the dictionary entry are pointers to the same instance of the class. Destroying `c_Insurers` won't affect the underlying instance of the class as VBA knows there is still another active reference to it (the dict entry) so will keep it alive) – Alex K. Jun 11 '12 at 17:58