ByRef argument type mismatch error on Join()

Question

I've looked through the ByRef errors reported here, but still can't find the error of my ways.

I have this, where OrderedCaseList() is a function of type Variant. It's called correctly and returns an array as expected.

However, when I try to compile I get "ByRef argument type mismatch" on the Join(arr... command. I have no idea why...

Private Sub worksheet_Activate()
Dim s As String
Dim arr() As String
Dim var As Variant
Dim i As Long, j As Long

var = OrderedCaseList(True)
ReDim arr(0 To UBound(var))

j = UBound(var)

For i = 0 To j
  arr(i) = var(i)
Next

s = Join(arr, ",")
Range("c16").Validation.Add Type:=xlValidateCustom, Formula1:=s

End Sub

The variable watch output when commenting out the last two lines of code is:

OrderedCaseList, Type: Variant/Variant
Expression:OrderedCaseList(0), Value:"Case 1", Type:Variant/String
etc.

Var, Type@ Variant/Variant
Expression: Var(0), Value:"Case 1", Type:Variant/String
etc.

arr, Type:String(0 to 3)
Expression:arr(0), Value:"Case 1", Type:String

Any help is appreciated.

Answer 1

Your code looks fine and I wasn't able to break it no matter how I set up the function OrderedCaseList (which I don't think is the problem anyway).

Is there a possibility you referenced a 3rd party dll that contains a JOIN function as well?

You could verify you are calling the VBA function by changing this line:

s = Join(arr, ",")

to this:

s = VBA.Strings.Join(arr, ",")

and see if it compiles