Assign Range to Array then use JOIN function

[Loomah]

Hi all
This is driving me up the wall!

I've just discovered this JOIN function (ok, 5-6 years after it was
introduced but hey!)
I'm trying to get to grips with this in Excel by assigning a range of cells
to it. I'm not too hot on using arrays but this is another step out of my
reach.

I can create an array but VBA online help states that the array for the join
function should be one dimensional.

Below is the code I have been using - including all thoughts and debugging
and different attempts commented out.

How do I ensure I have one row of data in a 1D array to use with the Join
function?

'Create comma delimited list using JOIN function
Sub a()
Dim iLastCol As Integer
Dim vArrData() As Variant
Dim sRes As String
Dim i As Integer

iLastCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column

'This initializes a 1D array ok
ReDim vArrData(1 To iLastCol)

'This generates array with 2 dimensions - why??
vArrData() = Range(Cells(1, 1), Cells(1, iLastCol)).Value

''This confuses me completely!! - see watch
'vArrData() = Array(Range(Cells(1, 1), Cells(1, iLastCol)).Value)

''This array works fine
'vArrData() = Array(2, 94, 100029, "Loom", "edge", #12/16/1965#)

'Test if array is created
Debug.Print "Array created : " & IsArray(vArrData)

'what's in the array(s)?
'2D array
On Error Resume Next
'I don't even understand how I got to the
'UBOUND element of this loop!
For i = LBound(vArrData) To UBound(vArrData, 2)
If Err.Number <> 0 Then
Debug.Print "2D", Err.Number, Err.Description
Exit For
End If
Debug.Print vArrData(1, i)
Next
On Error GoTo 0

'1D array
On Error Resume Next
For i = LBound(vArrData) To UBound(vArrData)
Debug.Print vArrData(i)
If Err.Number <> 0 Then
Debug.Print "1D", Err.Number, Err.Description
Exit For
End If
Next

sRes = Join(vArrData, ",")
Debug.Print "join", Err.Number, Err.Description
'5; Invalid procedure call or argument
MsgBox sRes

End Sub


TIA
Happy Friday
;-)

 

[Tom Ogilvy]

Sub a()
Dim iLastCol As Integer
Dim vArrData As Variant
Dim sRes As String
Dim i As Integer

iLastCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column


'This generates array with 1 dimension??
vArrData = Application.Transpose(Application.Transpose( _
Range(Cells(1, 1), Cells(1, iLastCol)).Value))

sRes = Join(vArrData, ",")

MsgBox sRes

End Sub

 

[Bob Phillips]

Hi Loomah,

You could always move the 2D array to a 1D array, and then join that. This
is a simple example

Dim myarray2D

myarray 2D= Range("A1:A3")

Dim myarray1D

myarray1D = Application.Transpose(myarray2D)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Loomah]

Tom
Thanks for that. Simple really!!
However, is there a simple explanation as to why Transpose works and why
it's called twice in your example? I've seen Bob's suggestion (thanks Bob!)
but still have trouble understanding.

Bear in mind my comment about being hopeless with arrays, my original
problem was I was generating a "1 by x" array. In my mind transposing that
would just give me a "x by 1"

puzzled!
;-)

 

[Tom Ogilvy]

Bob's suggestion works for a vertical array, but your array is horizontal.

When you pick up a contiguous multicell array from a worksheet, it is always
2D. I would assume this is for consistency. So you don't have to determine
how many dimensions it has if you pick up a row, column or rectangular area.

Why transposing works to remove the dimension - I can't say. I just know
that it does. If the array is vertical, one transpose does it. If it is
horizontal, two transposes do it.

Note that in most verisons of excel, transpose is restricted to working with
an array of 5461 elements or less. for bigger arrays, you will get a type
mismatch error.

 

[Loomah]

Tom
Thanks for the info, I really appreciate it. I especially like the 'I don't
know why but it does' bit!!
Have a great weekend
;-)