VBA join function

[wsjackman]

there is a function listed in 2007 VBA called Join

a macro function like

Function combine(XXX As Range)
combine = Join(XXX)
End Function

i thought would allow me to enter

=combine(A1:C1)

to give me a text field with the values in A through C separated by a space

Excel Workbook
	A	B	C
1	A	B	C
2
3	#VALUE!
Sheet5

what am I doing wrong?

 

[PA HS Teacher]

How About:

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> Combine(Rng <SPAN style="color:#00007F">As</SPAN> Range) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>  <SPAN style="color:#00007F">Dim</SPAN> C <SPAN style="color:#00007F">As</SPAN> Range<br>  <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> C <SPAN style="color:#00007F">In</SPAN> Rng.Cells<br>    Combine = Combine & C.Value & " "<br>  <SPAN style="color:#00007F">Next</SPAN> C<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[wsjackman]

How About:

Function Combine(Rng As Range) As String
Dim C As Range
For Each C In Rng.Cells
Combine = Combine & C.Value & " "
Next C
End Function

I have used functons like this before, but was just hoping that the simple form described by help on join would work.
Anything which cuts down on my chance for a typo is a real benefit.

 

[pgc01]

... but was just hoping that the simple form described by help on join would work.

Hi wsjackman

It cannot work. The help is clear, the sourcearray is a one-dimensional array containing substrings to be joined. Even if vba converted automatically the range to an array, it would be still a 2D array even in the case of an horizontal vector, like in your example.

Dim v
 
v = Range("A1:C1").Value

v is an array (1 To 1, 1 To 3).

If you want a one liner, you can extract the values in the range in your example to a unidimensional array and then use Join(), like

MsgBox Join(Application.Index(Range("A1:C1").Value, 1, 0))

but you are calling a worksheet function, which is not efficient.

A loop solution like PA HS Teacher posted is less compact but more efficient.

 

[jim may]

This worked for me...

Sub Tester2()
Dim Temp As String
Temp = Join(Array(Range("A1"), Range("B1"), Range("C1")), ", ")
Range("D1").Value = Temp
End Sub

 

[wsjackman]

It does work (with some tweaking) see below

Hi wsjackman

It cannot work. The help is clear, the sourcearray is a one-dimensional array containing substrings to be joined. Even if vba converted automatically the range to an array, it would be still a 2D array even in the case of an horizontal vector, like in your example.

Dim v
 
v = Range("A1:C1").Value

v is an array (1 To 1, 1 To 3).

If you want a one liner, you can extract the values in the range in your example to a unidimensional array and then use Join(), like

MsgBox Join(Application.Index(Range("A1:C1").Value, 1, 0))

but you are calling a worksheet function, which is not efficient.

A loop solution like PA HS Teacher posted is less compact but more efficient.

thanks !
your statement on one dimensional array reminded me of a thread which discussed the transpose function

Function combine(XXX As Range) As String
combine = Join(Application.Transpose(Application.Transpose(XXX)))
End Function

does work at least in 2007

Excel Workbook
	A	B	C	D
1	All	answers	are	useful
2
3	All answers are useful
Sheet5

 

[TheNoocH]

just to add a little flexibility if you want to run with a different seperator/delimiter other than a space....

Function combine(rng As Range, Optional delim As String = " ") As String
    combine = Join(Application.Transpose(Application.Transpose(rng)), delim)
End Function

Excel Workbook
	A	B	C
1	A	B	C
2
3	A B C	A, B, C	A*****B*****C
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A3	=combine(A1:C1)
B3	=combine(A1:C1,", ")
C3	=combine(A1:C1,"*****")

 

[wsjackman]

just to add a little flexibility if you want to run with a different seperator/delimiter other than a space....

Function combine(rng As Range, Optional delim As String = " ") As String
    combine = Join(Application.Transpose(Application.Transpose(rng)), delim)
End Function

Excel Workbook
	A	B	C
1	A	B	C
2
3	A B C	A, B, C	A*****B*****C
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A3	=combine(A1:C1)
B3	=combine(A1:C1,", ")
C3	=combine(A1:C1,"*****")

that was one of the reasons i liked the join function possibility

 

[jung2]

Can anyone explain why range is transposed twice in this code?
combine = Join(Application.Transpose(Application.Transpose(rng)), delim)

 

[Fluff]

It turns a 2d array into a 1d array, so that you can use JOIN