'Passing Dictionary ByRef Into a Private Sub in excel VBA userform module gives ByRef type mismatch error
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?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
