my join function doesn't work

JOIN and SPLIT are possible in VBA but are not standard functions.
You could use something like this:

Please Login or Register  to view this content.

Change the cells to suit your needs. And change the 5 in the 5-COUNTA to the number of cells in the row.

For the A, B, and c columns it would be:

Please Login or Register  to view this content.

Oops just saw you want a VBA function?
Let me work on that.

What was the error you were receiving?

Type mismatch

The Type mismatch is because you are trying to assign an array to a range.

Here is a UDF.

Please Login or Register  to view this content.

I somewhat but not completely understand your code.
I think if you would change your code to be applicable in the example Excel sheet that I attached then I will undertand.

In the example Excel sheet I tried to combine the array A1:C1 in cell F1.

You can in use it in two ways:
1) in the sheet itself in F1 enter =joincell(A1:C1)
2) in VBA

Please Login or Register  to view this content.

I finally got it done. It took a while to sink in.

I am still a beginner in VBA. I took a couple TurboPascal classes 20 year ago. And this is the first time I defined a function.

Please Login or Register  to view this content.