vba excel error “by ref argument type mismatch” when passing key to function as string argument

Tag: excel , vba , excel-vba , argument-passing Author: allenfiedin Date: 2013-10-26

I'm getting an "by ref argument type mismatch" error on the following sub test():

Public Function GetOtherDict(k As String, dict As Dictionary) As Dictionary

    Dim otherDict As New Dictionary

    curItem = dict.Item(k)
    otherDict.Add curItem, curItem

    Set GetOtherDict = otherDict
End Function

Public Sub Test()

    Dim dict As New Dictionary
    dict.Add "a", 1
    dict.Add "b", 2

    For Each k In dict.Keys

        Dim otherDict As Dictionary
        Dim curKey As String
        curKey = k 
        Set otherDict = GetOtherDict(k, dict)

    Next

End Sub

When I call the function GetOtherDict with the curKey argument instead of the k argument the error disappears.

Can you please tell me why do I need this redundant declaration?

Best Answer

Also you have declared k As String in the function so the function expects that you pass a String to it. Since you have not declared k in Sub Test(), k will be considered as a Variant and hence you are getting that "by ref argument type mismatch" Error.

It doesn't give you an error when you pass curKey because curKey is defined as String in Sub Test() which is what the function expects...

Another TIP: Please use Option Explicit at the end of the code.

comments:

Thanks. I just debugged and saw that a foreach itteration varaible must be variant or object. previously I thought that since it itterates dictionary keys, there will be some automatic string cast.
Does "Option Explicit On" forces me to declare all varibles with the Dim keyword?
Yes. When Option Explicit appears at the top of your code then you must explicitly declare all variables either by using the Dim or ReDim statements.

Other Answer1

I havent programmed vba for a long time but try

Public Function GetOtherDict(k As String, dict As Dictionary) As Dictionary

     Dim otherDict As New Dictionary

     curItem = dict.Item(hub)
     otherDict.Add curItem, curItem

     return otherDict
End Function