Using Dictionary.Exists in a UserForm property is giving Error 404 object required

Question

I am trying to use Class properties Get and Let in a UserForm called UBidStatus to fill a dictionary called DicOption.
Everything works fine until the If Not DicOption(OptName).Exists line (Error 404 object required).
NOTE: The code is working if I replace the whole code in the Public Property Let by DicOption.Add key:=OptName, Item:=OptValue.
This is the code in the Userform Class that I am trying to fix.

'Userform Class Module
Private DicOption As scripting.Dictionary

Public Property Get ProjectOption(ByVal OptName As String) As String
    ProjectOption = UBidStatus.ProjectOption(OptName)
End Property

Public Property Let ProjectOption(ByVal OptName As String, ByVal OptValue As String)
    If Not DicOption(OptName).Exists Then
        DicOption.Add key:=OptName, Item:=OptValue
    Else
        DicOption(OptName) = OptValue
    End If
End Property

Public Sub UserForm_Initialize()
    Set DicOption = New scripting.Dictionary
End Sub

Private Sub UserForm_Terminate()
    Set DicOption = Nothing
End Sub

Public Sub ExchangeToDicOption()
    Dim LR As Long
    Dim Rg As Range
    Dim ws As Worksheet
    Dim i As Long
    Dim a As String
    Dim b As String

    Set ws = ActiveWorkbook.Worksheets(2)
    Set Rg = ws.Columns(2)

    DicOption.RemoveAll

    LR = Rg.Find(What:="*", Lookat:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, MatchCase:=False).Row

    If LR > 1 Then
        For i = 2 To LR
            a = Cells(i, 1)
            b = Cells(i, 2)
           UBidStatus.ProjectOption(a) = b
        Next i
    End If
End Sub

http://stackoverflow.com/documentation/vbscript/8232/dictionary-objects#t=201702101504303204093 — Barney, Feb 10 '17 at 15:04
I can't see what is my error refering to this document. I tried using `DicOption("OptName").Exists` but it didn't worked either. — Gab, Feb 10 '17 at 15:13
See the example under section "Check if key Exists in Dictionary" — Barney, Feb 10 '17 at 15:22

score 10 · Accepted Answer · edited Feb 10 '17 at 15:18

10

The Exists method is called like this:

Dictionary.Exists(Key)

So try

 Public Property Let ProjectOption(ByVal OptName As String, ByVal OptValue As String)
    If Not DicOption.Exists(OptName) Then
        DicOption.Add key:=OptName, Item:=OptValue
    Else
        DicOption(OptName) = OptValue
    End If
End Property

edited Feb 10 '17 at 15:18

Mathieu Guindon

69,817
8
107
235

answered Feb 10 '17 at 15:14

SgtStens

290
2
7

score 7 · Answer 2 · edited Feb 10 '17 at 16:03

7

A Scripting.Dictionary will implicitly call its Add method if you assign to a key that doesn't exist, so if the goal is to "add or replace" the value at the specified key, you could simply replace:

If Not DicOption(OptName).Exists Then
    DicOption.Add key:=OptName, Item:=OptValue
Else
    DicOption(OptName) = OptValue
End If

with:

DicOption.Item(OptName) = OptValue

edited Feb 10 '17 at 16:03

Mathieu Guindon

69,817
8
107
235

answered Feb 10 '17 at 15:24

user3598756

28,893
4
18
28

3

I'd definitely upvote, if this answer explained why the `Exists` check is redundant. – Mathieu Guindon Feb 10 '17 at 15:26
@Mat'sMug, my real "Why" would be "because I once found it works"!. I have some _deeper_ feeling about that but I'm not into things as I very well know _you_ are and my explanation would probably result incorrect in some way or another. So I limited myself to a "suggestion" in a topic where there was an already accepted and heavily upvoted answer – user3598756 Feb 10 '17 at 15:43
@Mat'sMug I guess dictionary object will add automatically a new key if the keys does not exist already? – Gab Feb 10 '17 at 15:44
yeah, exactly that _deeper_ feeling of mine – user3598756 Feb 10 '17 at 15:45
This is really interesting. I know from experience that the Add method will fail if the key already exists, which is why the OP applied the conditional around it. Why then can you directly assign it with the Item method? I'll +1 your answer for showing me an easier way, even though I wish I knew why, but sometimes....you know, VBA. (sigh) – SgtStens Feb 10 '17 at 16:07
1

@SgtStens, It _must be _ because `Add()` method would try to _add_ a duplicate while `Item()` method forces that `If Exists-Then Update-Else Add` under the hood – user3598756 Feb 10 '17 at 16:11
@SgtStens IKR! The instinctive behavior would have been to have it fire a "key not found" error instead of playing tricks behind our backs... FWIW that specific implementation and (its quirks) is on VBScript (the type is defined in the *Microsoft Scripting Runtime* type library), not VBA per se... not that VBA doesn't *play tricks behind our backs* though ;-) – Mathieu Guindon Feb 10 '17 at 16:25

Using Dictionary.Exists in a UserForm property is giving Error 404 object required

2 Answers2