Proper syntax for calling a particular UDF-CodePudding

This has reference to SO question Find all other cells with same adjacent element and data reproduced below to avoid cross reference.

I have an excel spreadsheet with the following columns • A: City • B: State • C: Other cities that are in the same state as column A For example, the result may look like this:

City	State	Other cities in State
Philadelphia	Pennsylvania	Pitsburgh
Pitsburgh	Pennsylvania	Philadelphia
San Diego	California	Palo Alto, Mountain View, LA, San Jose, Houston
Palo Alto	California	San Jose, Mountain View, San Diego
Mountain View	California	San Jose, LA, Palo Alto, San Diego
LA	California	San Jose, Mountain View, Palo Alto, San Diego
San Jose	California	LA, Mountain View, Palo Alto, San Diego
Austin	Texas	Houston, Dallas
Houston	Texas	Austin, Dallas
Dallas	Texas	Dallas, Houston

It was answered by user4039065 who advised to use an UDF and the code is as follows.

Option Explicit

Function CITYJOIN(rst As Range, sst As String, rct As Range, _
                  Optional sct As String = "", _
                  Optional bIncludeSelf As Boolean = False, _
                  Optional delim As String = ", ")
    Dim r As Long
    Static dict As Object
    If dict Is Nothing Then
        Set dict = CreateObject("Scripting.Dictionary")
        dict.compareMode = vbTextCompare
    End If

    dict.RemoveAll
    'truncate any full column references to the .UsedRange
    Set rst = Intersect(rst, rst.Parent.UsedRange)
    'set the cities to the same size as the states
    Set rct = rct.Resize(rst.Rows.Count, rst.Columns.Count)

    'loop through the cells to create unique dictionary keys
    For r = 1 To rst.Cells.Count
        If LCase(rst(r).Value2) = LCase(sst) Then
            dict.Item(StrConv(rct(r).Value2, vbProperCase)) = vbNullString
        End If
    Next r

    'get rid of 'self-city'
    If Not bIncludeSelf Then
        dict.Remove sct
    End If

    'return a delimited string
    CITYJOIN = Join(dict.keys, delim)

End Function

It gives correct answer when used in worksheet as per following formula.

   =CITYJOIN(B:B,B2,A:A,A2)

My level in VBA is elementary and I want to understand the Function code fully by stepping through the code using F8 key. With this in view I coded the following Test sub.

Sub test()
Call CITYJOIN("B: B", B2, "A: A", A2)
'CITYJOIN B: B , B2, A: A , A2
End Sub

I am getting an error at the following line in Function code stating compiler error.

 CITYJOIN = Join(dict.keys, delim)

Can someone help me and provide proper code of test sub explaining the mistake in the above code of test sub. Thanks

CodePudding user response：

Please, call the function in this way:

Sub test()
  Debug.print  CITYJOIN(Range("B:B"), Range("B2").value, Range("A:A"), Range("A2").value)
End Sub

And see the result in Immediate Window (Ctrl G being in VBE (Visual Basic for Applications Editor).