title: Why does Join() need a double transposition of a 1-dim Long array?
body: <p><strong>Why does Join() need a double transposition of a 1-dim Long array?</strong></p>
  
  <p>Due to <a href="https://docs.microsoft.com/de-de/office/vba/language/reference/user-interface-help/join-function" rel="nofollow noreferrer">MS Help</a> 
  the Join() function requires a sourcearray as "one-dimensional array containing substrings to be joined" <em>(btw the help site makes no difference whether it is a Variant or Long)</em>. </p>
  
  <p><em>Note: In the <a href="https://docs.microsoft.com/de-de/office/vba/language/glossary/vbe-glossary#array" rel="nofollow noreferrer">VBE glossary</a> 
  an array is defined as set of sequentially indexed elements having the same intrinsic data type.</em></p>
  
  <p>It's no problem to connect 1-dim Variant arrays via <code>Join()</code> and 
  it's even possible to join numbers as well as they seem to be internally interpreted as "convert us to strings".</p>
  
  <p><strong>Issue with a 1-dim array declared as Long</strong></p>
  
  <p>In some cases I want to restrict the elements type to <code>Long</code> and avoid the <code>Variant</code> solution mentioned above. - 
  Declaring a "flat" array  - here: <code>Numbers()</code> - as <strong>Long</strong>, however raises <strong>Error 5</strong> "Invalid procedure call or argument", 
  if you try to connect results via a simple </p>
  
  <pre><code>'[2] Failing
  Join(Numbers, "|") .
  </code></pre>
  
  <p>I found an interesting ► <strong>work around</strong> via a basically redundant <strong>double transposition</strong> (c.f. <strong><code>[1]</code>)</strong>, 
  as it "converts" a <em>flat</em> 1-dim array eventually back to the same dimension. </p>
  
  <pre><code>'[1] work around
  Join(Application.Transpose(Application.Transpose(Numbers)), "|")
  </code></pre>
  
  <p><strong>Question</strong></p>
  
  <blockquote>
    <p>What's the internal difference how VBA treats both cases and why does <code>Join()</code> need a double transposition of a 1-dim Long array here?</p>
  </blockquote>
  
  <hr>
  
  <p><strong>Example call to join a "flat" array declared as Long</strong></p>
  
  <p>In order to show the workaround code line <code>[1]</code> as well as the error raising code line <code>[2]</code>, 
  I integrated a basic error handling showing user defined error lines (<code>ERL</code>), too.</p>
  
  <p><em>VB Editor's immediate window shows Error 5 in ERL 200:</em></p>
  
  <pre><code> OK: [1] 3 elems: ~&gt; 100|200|300
  ERL: 200 Error No 5 Invalid procedure call or argument
  </code></pre>
  
  <p><strong>Example call</strong></p>
  
  <pre><code>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] " &amp; UBound(Numbers) &amp; " elems:" &amp; _
                  " ~&gt; " &amp; 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] " &amp; UBound(Numbers) &amp; " elems:" &amp; _
                  " ~&gt; " &amp; Join(Numbers, "|")
  
  Exit Sub
  
  oops:     Debug.Print "ERL: " &amp; Erl &amp; " Error No " &amp; Err.Number &amp; " " &amp; Err.Description
  End Sub
  </code></pre>
  
  <p><strong>Sub <code>FillNumbers</code> called by above main procedure</strong> </p>
  
  <pre><code>Sub FillNumbers(ByVal n As Long, arr)  
      ReDim arr(1 To n)
      arr(1) = 100
      arr(2) = 200
      arr(3) = 300
  End Sub
  </code></pre>