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?

 

[pgc01]

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

Hi

Like Fluff says it converts a 2d horizontal vector into a 1d array.

Remark: I prefer to extract directly the values with Index(), like I posted.

Instead of:

MsgBox Join(Application.Transpose(Application.Transpose(rng)), delim)

I prefer

MsgBox Join(Application.Index(rng.Value, 1, 0),delim)

 

[Domenic]

Just wanted to add that, while the first Transpose converts the 2 dimensional array into a 1 dimensional array, it changes its orientation from a horizontal one to a vertical one. The second Transpose simply re-orients the array to a horizontal one, which is required by Join.

In any case, if the range of cells is already a horizontal one, as per this particular example, PGC's alternative is easier, and seems more efficient. I prefer it too.

 

[pgc01]

Just wanted to add that, while the first Transpose converts the 2 dimensional array into a 1 dimensional array, it changes its orientation from a horizontal one to a vertical one.

Hi, Domenic

You have a typo, The first transpose changes horizontal to vertical but the array stays 2d.

Just to summarize,

.Tanspose()
- converts a 2d horizontal vector to a 2d vertical vector
- converts a 2d vertical vector to a 1d horizontal vector

or

.Tanspose()
- horizontal->vertical, 2D->2D
- vertical->horizontal, 2D->1D

 

[Domenic]

Hi PGC,

Maybe I'm missing something, so please correct me if I'm wrong.

It looks to me that the first transpose does indeed return a one-dimensional array. Let's say we have the following...

arr = Application.Transpose(Range("A1:A10"))

In this case, Ubound(arr,1) returns 10, as one would expect. But UBound(arr,2) returns a subscript out of range error. And arr(1,1) throws the same error. Also, when Application.Transpose(...) is passed to the Join function, which requires a one-dimensional array, it accepts it and returns a string, as expected.

Am I missing something?

 

[Fluff]

With an array of A1:A10 you are correct (although you wouldn't transpose twice as that will take you back to a 2d array), but with an array of A1:J1 pgc is correct.

 

[pgc01]

Hi Domenic

The Transpose will only convert a 2d array into a 1d array if you are converting vertical to horizontal.

In this case since we have a horizontal array the solution with transpose uses 2 transposes (horizontal 2d) -> (vertical 2d) -> (array 1d)

If you had a vertical array you'd only need 1 transpose: (vertical 2d) -> (array 1d)

With your example for a horizontal array:

MsgBox UBound(Application.Transpose(Range("A1:J1")), 2)

 

[Domenic]

PGC and Fluff, thank you both for clarifying this for me. I did not realize that it behaved differently for a horizontal array. I don't understand why this is the case, but I guess there must be a reason for this. Oh well.

Thanks again!

Cheers!

 

[pgc01]

I don't know either. I've thought about it and my guess is that it was a design option, like horizontal is default.

The value of a horizontal range vector is 2d, kind of makes sense, a range has always rows and columns.
The value of a horizontal array is 1d, also makes sense, just a sequence of values.

v = [a1:b1] ' 2d
v = [a1:b1*2] ' 1d

When we transpose the vertical range value the result is a horizontal array, so the result is 1d.
For the vertical array we always need both dimensions, the way we know it's vertical it's because they are in different rows in the same column.

... but just a design option, it could be vertical the default.


In the other direction, array assigned to a horizontal range, both a 2d array or a 1d array are accepted, like

Dim arr(1 To 1, 1 To 3) As Variant
arr(1, 1) = 1
arr(1, 2) = "X"
arr(1, 3) = True
Range("A1:C1") = arr

or

Dim arr(1 To 3) As Variant
arr(1) = 1
arr(1) = "X"
arr(1) = True
Range("A1:C1") = arr

or even

Range("A1:C1") = Array(1, "X", True)

It seems they chose horizontal to be default.

Just guessing, though.

 

[Domenic]

PGC, thanks for your further thoughts on this issue. I really appreciate it.


I would never have thought that the behaviour would be different. As they say, "Never assume...".


Cheers!