excelvbadictionarystring-comparisontext-comparison

Excel VBA: "invalid procedure call or method" when setting CompareMode to TextCompare with Dictionary


For some reason, I cant set the CompareMode in the dictionary offONameDic to TextCompare. I keep getting a "Invalid Procedure or Call Error". I want to ensure that the capitalization of a, let say, user provided provided key does not affect ability for the Dictionary to recognize the key as valid i.e. if a key was "hello", "Hello" would also be recognized as a key.

Please Note that I use the Microsoft Scripting Runtime Reference from the list of installed libraries in VBA Excel

My Code:

Sub Main()
'...code...
Dim offONameDic As Scripting.Dictionary
'...code...
Set offONameDic = New Scripting.Dictionary
With offONameDic
    .Add "Blood", "Blood"
    .Add "Liver", "Liver"
    .Add "Kidneys", "Kidneys"
    .Add "Kidney", "Kidneys"
    .Add "Spleen", "Spleen"
    .Add "Heart", "Heart"
    .Add "Lungs", "Lungs"
    .Add "Lung", "Lungs"
    .Add "Stomach", "Stomach"
    .Add "Muscle", "Muscle"
    .Add "Bone", "Bone"
    .Add "Carcass", "Carcass"
    .Add "Tumor", "Tumor"
    .Add "Axial Lymph Node", "Axial Lymph Node"
    .Add "ALN", "Axial Lymph Node"
    .Add "Igunial Lymph Node", "Igunial Lymph Node"
    .Add "IgLN", "Igunial Lymph Node"
    .Add "Intestines", "Intestines"
    .Add "Intestine", "Intestines"
    .Add "Cecum", "Cecum"
    .Add "Tail", "Tail"
    .CompareMode = TextCompare '****This Line Causes the "Invalid Procedure or Call" Error"****
End With
'...code...
End Sub

Solution

  • You need to set the CompareMode property before you add any data to the dictionary, so move the line that causes the error to be the first one after the With offONameDic line, e.g.:

    With offONameDic
        .CompareMode = TextCompare ' No longer gives an error!
        .Add "Blood", "Blood"
        ...
    

    According to the documentation:

    An error occurs if you try to change the comparison mode of a Dictionary object that already contains data.