Why does Join() need a double transposition of a 1-dim Long array?
Due to MS Help
the Join() function requires a sourcearray as “one-dimensional array containing substrings to be joined” (btw the help site makes no difference whether it is a Variant or Long).
Note: In the VBE glossary
an array is defined as set of sequentially indexed elements having the same intrinsic data type.
It’s no problem to connect 1-dim Variant arrays via
it’s even possible to join numbers as well as they seem to be internally interpreted as “convert us to strings”.
Issue with a 1-dim array declared as Long
In some cases I want to restrict the elements type to
Long and avoid the
Variant solution mentioned above. –
Declaring a “flat” array – here:
Numbers() – as Long, however raises Error 5 “Invalid procedure call or argument”,
if you try to connect results via a simple
' Failing Join(Numbers, "|") .
I found an interesting ► work around via a basically redundant double transposition (c.f.
as it “converts” a flat 1-dim array eventually back to the same dimension.
' work around Join(Application.Transpose(Application.Transpose(Numbers)), "|")
What’s the internal difference how VBA treats both cases and why does
Join()need a double transposition of a 1-dim Long array here?
Example call to join a “flat” array declared as Long
In order to show the workaround code line
 as well as the error raising code line
I integrated a basic error handling showing user defined error lines (
VB Editor’s immediate window shows Error 5 in ERL 200:
OK:  3 elems: ~> 100|200|300 ERL: 200 Error No 5 Invalid procedure call or argument
Sub JoinArr() Dim Numbers() As Long ' provide for long array Numbers() FillNumbers 3, Numbers ' call sub procedure to assign 3 numbers to array Nums ' Numbers is now an array of 3 numbers On Error GoTo oops ' work around - why does Join() need a double transposition in a 1-dim array? 100 Debug.Print " OK:  " & UBound(Numbers) & " elems:" & _ " ~> " & Join(Application.Transpose(Application.Transpose(Numbers)), "|") ' join an already existing "flat" array raises Error 5 "Invalid procedure call or argument" 200 Debug.Print " OK  " & UBound(Numbers) & " elems:" & _ " ~> " & Join(Numbers, "|") Exit Sub oops: Debug.Print "ERL: " & Erl & " Error No " & Err.Number & " " & Err.Description End Sub
FillNumbers called by above main procedure
Sub FillNumbers(ByVal n As Long, arr) ReDim arr(1 To n) arr(1) = 100 arr(2) = 200 arr(3) = 300 End Sub