(Solved) – Why does Join() need a double transposition of a 1-dim Long array?


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 Join() and
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

'[2] Failing
Join(Numbers, "|") .

I found an interesting ► work around via a basically redundant double transposition (c.f. [1]),
as it “converts” a flat 1-dim array eventually back to the same dimension.

'[1] 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 [1] as well as the error raising code line [2],
I integrated a basic error handling showing user defined error lines (ERL), too.

VB Editor’s immediate window shows Error 5 in ERL 200:

 OK: [1] 3 elems: ~> 100|200|300
ERL: 200 Error No 5 Invalid procedure call or argument

Example call

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
    '[1] work around - why does Join() need a double transposition in a 1-dim array?
100 Debug.Print " OK: [1] " & UBound(Numbers) & " elems:" & _
                " ~> " & Join(Application.Transpose(Application.Transpose(Numbers)), "|")

    '[2] join an already existing "flat" array raises Error 5 "Invalid procedure call or argument" 
200 Debug.Print " OK [2] " & UBound(Numbers) & " elems:" & _
                " ~> " & Join(Numbers, "|")

Exit Sub

oops:     Debug.Print "ERL: " & Erl & " Error No " & Err.Number & " " & Err.Description
End Sub

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

Leave a Reply

Your email address will not be published. Required fields are marked *