Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 436
Gab Avatar asked Feb 10 '17 15:02

Gab


People also ask

How do you fix an Object required error in VBA?

The simplest ways to fix the problem is as follows: Locate the offending line of code (if not highlighted, use debug mode) Identify whether you've referenced any objects which aren't declared. Look for any of the functions which may be causing an error and identify they are called correctly (with the correct syntax)

What is Object required error in VBA?

In rare cases, this error occurs when you have a valid object but are attempting to perform an invalid action on the object. For example, you may receive this error if you try to assign a value to a read-only property. Check the object's documentation and make sure the action you are trying to perform is valid.


2 Answers

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
like image 123
SgtStens Avatar answered Nov 13 '22 11:11

SgtStens


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
like image 33
user3598756 Avatar answered Nov 13 '22 12:11

user3598756