Passing Dictionary ByRef Into a Private Sub in excel VBA userform module gives ByRef type mismatch e-CodePudding

I am creating a userform that collects different sets of information into different Dictionaries for processing. The Dictionaries are declared at the userform module level, and initialised in another procedure on activation of the userform. In order to streamline my code, I have a subroutine that inserts items into appropriate dictionaries. I have added Microsoft Scripting Runtime to VBA References Here are the code examples

At the userform module declaration

Option Explicit
Option Compare Text
Dim Ref_Dict, Client_Dict, Service_Dict As Scripting.Dictionary

The Dictionaries are initialised within another procedure in the same module. This module is called on activation of the form. Part of the activation module is

Set Ref_Dict = New Scripting.Dictionary
Set Client_Dict = New Scripting.Dictionary
Set Service_Dict = New Scripting.Dictionary

This is the Procedure that inserts items into appropriate dictionary

Private Sub Update_Dict(ByRef Dict As Scripting.Dictionary, ByVal Item As Variant, ByVal Key As Variant)
Dict(Key) = Item
End Sub

The above procedure is called from Textbox or ComboBox _AfterUpdate procedure (as the case may be) eg

Private Sub ComboBox_1_AfterUpdate()
Dim Item, Key As Variant
Item = Me.ComboBox_1.Value
Key = "Name"
Update_Dict Ref_Dict, Item, Key
End Sub

Entering a value into ComboBox_1 throws up error "ByRef Arguement type mismatch.

However, If I replicate the same scenario in a regular module, It runs perfectly. Please what am I doing wrong?

CodePudding user response：

You are a victim of a typical VBA pitfall.

AS Rory wrote in the comments, when you write

Dim Ref_Dict, Client_Dict, Service_Dict As Scripting.Dictionary

you declare only Service_Dict as a Dictionary. The other two variables are declared as Variant. As a Variant can contain anything (any data type), you can assign a dictionary object to it as you do in your activation routine.

However, a Variant holding a Dictionary is not the same as a Dictionary, therefore you get the "Type Mismatch" error.

Simple to correct, provide the data type for all of your variables:

Dim Ref_Dict As Scripting.Dictionary, Client_Dict As Scripting.Dictionary, Service_Dict As Scripting.Dictionary