Case in-sensitive dictionary

Question

I have set Dictionary as an object an added several items to that dictionary, however it seems to be case-sensitive. Is there anyway I can set the dictionary to recognize different versions?

My Code:

Sub Test()

Dim sheet1 As String
Dim Dict As Object
Dim c As Range

Sheet1= "TEST"
Set Dict = CreateObject("Scripting.Dictionary")

Dict.Add "MIKE", 0
Dict.Add "PHIL", 0
Dict.Add "Joe", 0

For Each c In ActiveWorkbook.Worksheets(Sheet1).UsedRange
If Dict.Exists(ActiveWorkbook.Worksheets(Sheet1).Cells(c.Row, c.Column).Value) Then
        Dict(ActiveWorkbook.Worksheets(Sheet1).Cells(c.Row, c.Column).Value) = Dict(ActiveWorkbook.Worksheets(Sheet1).Cells(c.Row, c.Column).Value) + 1
End If
Next

Sheet1.Cells(25, 3) = Dict("MIKE")
Sheet1.Cells(25, 3) = Dict("PHIL")
Sheet1.Cells(25, 3) = Dict("Joe")

Set Dict = Nothing

End Sub

So I want to recognize "mike" for MIKE and "Phil" for PHIL etc.

Thanks in advance!

You could add and compare the elements by using lcase() https://msdn.microsoft.com/en-us/library/office/gg264497.aspx — Roland, Jul 24 '15 at 09:00
Add a line after creating the scripting.dictionary object like `Dict.CompareMode = vbTextCompare`. See [CompareMode property](https://msdn.microsoft.com/en-us/library/aa242874(v=vs.60).aspx). — , Jul 24 '15 at 09:01

score 8 · Accepted Answer · edited Jun 21 '21 at 14:08

8

Adding onto @Ralph

dict.CompareMode = TextCompare

is what I changed the file to.

Some clarifications regarding the comments:

TextCompare is only available with Early Binding, it is a member of Scripting.
vbTextCompare is always available in VBA.
Both are = 1.

? Scripting.CompareMethod.TextCompare
 1 
? VBA.VbCompareMethod.vbTextCompare
 1

Note: you can only set dict.CompareMode if dict is empty, i.e. you haven't added any members yet. Otherwise you will get an "Illegal procedure call" error.

edited Jun 21 '21 at 14:08

Andre

26,751
7
36
80

answered Jan 30 '17 at 18:09

John M

14,338
29
91
143

I just checked and there is no such thing as `vbTextCompare`. Where is this coming from? It should be `dict.CompareMode = TextCompare`. – Ralph Jul 25 '18 at 07:38
Ah, I just saw that the user @Marcucciboy2 updated your answer with incorrect code and reverted it back. Now it is correct again. – Ralph Jul 25 '18 at 07:50
@Ralph `vbTextCompare` is the defined constant for this operation. It might be a question of which version of excel you're using, but if you don't believe me check the microsoft documentation for yourself https://msdn.microsoft.com/en-us/vba/language-reference-vba/articles/comparemode-property – Marcucciboy2 Jul 26 '18 at 23:48
@Ralph my machine would not update `dict`'s compare mode until I added the `vb` prefix – Marcucciboy2 Jul 26 '18 at 23:48
@Marcucciboy2 That's because you are using lazy late binding and do not correctly `dim` you variables as dictionary but rather as `Objects` and then (later in the code) tell VBA in your code (at run-time) that this `Object` is in fact a dictionary using `CreateObject("Scripting.Dictionary")`. Since your variable is at that time an `Object` you can only use `vbTextCompare` or `1` (because your variable is essentially an object). Or you could even set `CompareMode = 1` directly. Yet, if you are using early binding then `vbTextCompare` will be refused and only `TextCompare` will work. – Ralph Jul 27 '18 at 10:41
@Marcucciboy2 Check out the screenshots I added to my answer below for clarification. – Ralph Jul 27 '18 at 10:55
Note that when returning a dictionary from a function I had to place this in the function – FreeSoftwareServers Aug 26 '21 at 23:38

Ralph · Answer 2 · 2018-07-27T10:55:06.383

4

I always like to set things straight for all of my coding. So, all modules and code lying on my sheets or in forms start with the following three lines before writing any additional code.

Option Base 0
Option Explicit
Option Compare Text

If I want to have something handled differently in a particular Sub for some reason, then I do so in this particular sub only and do as proposed in the comment above (example):

dict.CompareMode = BinaryCompare 'if I need a case-sensitive compare in this sub

Since VBE knows that dict is a Dictionary it can provide propositions for auto-complete. This is only possible with early-binding. With late binding VBE will not provide any auto-complete propositions.

edited Jul 27 '18 at 10:55

answered Jul 24 '15 at 14:47

Ralph

9,284
4
32
42

4

`Option Compare Text` has no effect on `Dictionary` objects. They will always perform binary/case-sensitive comparisons by default. – Bond Jul 25 '15 at 03:29
@Bond: I didn't know that. Thanks for sharing. Good to know. – Ralph Jul 27 '15 at 10:27
Very interesting. When I use the proper references and early-binding, it will only accept `TextCompare`. If I uncheck that reference and use late-binding, only `vbTextCompare` will change the value of `dict.CompareMode` – Marcucciboy2 Jul 30 '18 at 13:47
Eww early binding... I would always advise against use of early binding in VBA... If you don't your code is significantly more likely to be version dependant, and cause nasty issues later down the line. – Sancarn Apr 15 '20 at 10:57
1

@Sancarn: https://stackoverflow.com/questions/49789611/vba-early-vs-late-binding-real-life-performance-differences So, I use early binding only and if a new version is released then the existing development gets adjusted. This is by far the preferred way and clean. Besides, new version do not always support prior functions etc. So, even if you use late binding it is no certain to be version independent. – Ralph Apr 15 '20 at 12:07
@Ralph I know very well the advantages of early binding. But as said, it leads to nasty version issues: https://wordmvp.com/FAQs/InterDev/EarlyvsLateBinding.htm – Sancarn Apr 15 '20 at 12:09
@Sancarn You just referenced the same post as I did but on a different site. Please always try to link to posts on this site as other sites may go offline or the link may change. Furthermore, late binding is not always version independent (as noted before). Newer versions almost always require adjustments to code anyway when different function calls, methods, etc. are supported. – Ralph Apr 15 '20 at 12:14

Case in-sensitive dictionary

2 Answers2

Linked

Related