CODEWITHSUNDEEP
vbaexcel
Vignesh Subramanian
Vignesh Subramanian

Reputation: 7289

Passing a variant as parameter to a function in VBA

I have a function

Function convertToDict(arrayIp As Variant) As Object
Dim dict As Object
Set dict = CreateObject("scripting.dictionary")
For Each element In arrayIp

    If dict.exists(element) Then
        dict.Item(element) = dict.Item(element) + 1
    Else
        dict.Add element, 1
    End If
Next
End Function

I tried to call this function from a sub

Dim dict As Object
varray = Range("B4:B" & finalRow - 1).Value
dict = convertToDict(varray)

But it throws error:

Run time error 450, wrong number of arguments or invalid property

what is the mistake that I have done here?

I am passing a variant and result is an Object.

Upvotes: 2

Views: 3737

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Because you are dealing with Objects you need Set in both the function and the sub:

Function convertToDict(arrayIp As Variant) As Object
    Dim dict As Object
    Set dict = CreateObject("scripting.dictionary")
    For Each element In arrayIp

        If dict.exists(element) Then
            dict.Item(element) = dict.Item(element) + 1
        Else
            dict.Add element, 1
        End If
    Next
    Set convertToDict = dict
End Function

Sub qwerty()
    Dim dict As Object
    finalRow = 10
    varray = Range("B4:B" & finalRow - 1).Value
    Set dict = convertToDict(varray)
End Sub

Upvotes: 2

Degustaf
Degustaf

Reputation: 2670

Since dict is an object, you need to use Set when assigning to it. Try 

Set dict = convertToDict(varray)

Upvotes: 1

Related Questions