VBA join function

wsjackman

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
there is a function listed in 2007 VBA called Join

a macro function like

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

i thought would allow me to enter

Code:
=combine(A1:C1)

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

Excel Workbook
ABC
1ABC
2
3#VALUE!
Sheet5


what am I doing wrong?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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>
 
Upvote 0

wsjackman

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
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.
 
Upvote 0

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,888
... 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.

Code:
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

Code:
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.
 
Upvote 0

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,484
This worked for me...

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

wsjackman

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
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.

Code:
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

Code:
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

Code:
Function combine(XXX As Range) As String
combine = Join(Application.Transpose(Application.Transpose(XXX)))
End Function
does work at least in 2007

Excel Workbook
ABCD
1Allanswersareuseful
2
3All answers are useful
Sheet5
 
Upvote 0

TheNoocH

Well-known Member
Joined
Oct 6, 2004
Messages
3,482
just to add a little flexibility if you want to run with a different seperator/delimiter other than a space....

Code:
Function combine(rng As Range, Optional delim As String = " ") As String
    combine = Join(Application.Transpose(Application.Transpose(rng)), delim)
End Function
Excel Workbook
ABC
1ABC
2
3A B CA, B, CA*****B*****C
Sheet1
Excel 2007
Cell Formulas
RangeFormula
A3=combine(A1:C1)
B3=combine(A1:C1,", ")
C3=combine(A1:C1,"*****")
 
Upvote 0

wsjackman

Well-known Member
Joined
Jun 19, 2008
Messages
2,465
just to add a little flexibility if you want to run with a different seperator/delimiter other than a space....

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


Excel Workbook
ABC
1ABC
2
3A B CA, B, CA*****B*****C
Sheet1
Excel 2007
Cell Formulas
RangeFormula
A3=combine(A1:C1)
B3=combine(A1:C1,", ")
C3=combine(A1:C1,"*****")


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

jung2

New Member
Joined
Jun 18, 2018
Messages
1
Can anyone explain why range is transposed twice in this code?
combine = Join(Application.Transpose(Application.Transpose(rng)), delim)
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,716
Office Version
  1. 365
Platform
  1. Windows
It turns a 2d array into a 1d array, so that you can use JOIN
 
Upvote 0

Forum statistics

Threads
1,187,003
Messages
5,961,067
Members
438,516
Latest member
Fintrics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top