compile error: ByRef argument type mismatch, What am I missing?

Question

I'm not understanding what the problem is.

I have the following code:

Public Sub SetupForm()
    Dim wbMain As Workbook
    Dim wsToner As Worksheet
    Set wbMain = ActiveWorkbook
    Set wsToner = wbMain.Sheets("Toner")
    With DashboardForm
        'Parent nodes
        Dim Brands() As String
        Brands() = GetTonerBrand(wsToner)

The last line is calling the following function:

Private Function GetTonerBrand(wsSheet As Worksheet) As String
    Dim col, Counter
    Dim LastCol
    Counter = 0
    Dim LastRow
    Dim Brands() As String
    With wsSheet
        LastRow = .Cells(.Rows.Count, 2).End(xlUp).Row
        Dim RowCount
        RowCount = LastRow
    End With
    Dim dict
    Set dict = CreateObject("Scripting.Dictionary")
    Do While RowCount > 3
        If Not dict.exists(wsToner.Cells(RowCount, 2).Value) Then
            dict.Add wsToner.Cells(RowCount, 2).Value, 0
        End If
        RowCount = RowCount - 1
    Loop
    ReDim Brands(0 To dict.Count)
    Brands() = dict.keys()
    GetTonerBrand Brands
End Function

When I attempt to run this I get the error below:

Compile error: ByRef argument type mismatch

I thought that if I update the type of the array and function then it would work.

So I changed the Function to String and the Brands() arrays as well were changed to String. Then I get an error:

Compile error: Can't assign to array

in the SetupForm sub on the line Brands() = GetTonerBrand(wsToner)

Obviouly I'm missing something, I just don't see what it is.

UPDATE

I have seen this other question with a similar name, it isn't helpful.

Maybe the last line of GetTonerBrand should be `GetTonerBrand = Brands`. — , Nov 14 '17 at 16:34
The second to last line, it should be `GetTonerBrand = Brands` — newacc2240, Nov 14 '17 at 16:35
`GetTonerBrand` says it returns a string but your returning a string array, so also change to `As String()` — Alex K., Nov 14 '17 at 16:38
You seem to be trying to assign a string to an array (Brands() = GetTonerBrand(wsToner)). Brands() is an array, but your function is just a string. — mooseman, Nov 14 '17 at 16:44

Excelosaurus · Accepted Answer · 2017-11-14T18:29:49.253

Good points were made in the comments to your question, but none addressed the fact that VBA won't magically cast your dictionary keys, a Variant array, to a String array.

I suggest you modify your function to return a Variant array. In your calling code, you'd have to modify your declarartion:

'Parent nodes
Dim Brands() As Variant

In the code below, notice that superflous variables were eliminated, while remaining variables were declared with a suitable type. At the end, to return the value, the function's name is assigned the dictionary's keys.

Private Function GetTonerBrand(wsSheet As Worksheet) As Variant()
    Dim row As Long
    Dim tonerBrands As Object
    Dim tonerBrand As String

    With wsSheet
        row = .Cells(.Rows.Count, 2).End(xlUp).row
    End With

    Set tonerBrands = CreateObject("Scripting.Dictionary")

    Do While row > 3
        tonerBrand = CStr(wsToner.Cells(row, 2).Value)
        If Not tonerBrands.Exists(tonerBrand) Then
            tonerBrands.Add tonerBrand, 0
        End If
        row = row - 1
    Loop

    GetTonerBrand = tonerBrands.Keys()
End Function

A cool thing about variant arrays is that you can iterate through them using a variant-type variable, with a simple For Each.

compile error: ByRef argument type mismatch, What am I missing?

1 Answers1