arrays - 为什么 Join() 需要一个 1-dim Long 数组的双重转置？

Question

为什么 Join() 需要一个 1-dim Long 数组的双重转置？

由于MS Help
Join() 函数需要一个 sourcearray 作为“包含要连接的子字符串的一维数组”(顺便说一句，帮助站点无论是 Variant 还是 Long 都没有区别)。

注意:在 VBE glossary
数组被定义为一组具有相同内在数据类型的按顺序索引的元素。

通过Join()连接一维Variant数组没问题和
甚至可以连接数字，因为它们似乎在内部被解释为“将我们转换为字符串”。

声明为 Long 的 1-dim 数组出现问题

在某些情况下，我想将元素类型限制为 Long并避免 Variant上面提到的解决方案。 -
声明一个“平面”数组 - 这里:Numbers() - 作为 长 , 但是会引发 错误 5 "无效的过程调用或参数",
如果您尝试通过简单的连接结果

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

我发现了一个有趣的 ► 解决通过一个基本冗余的 双换位 (引用 [1] ) ,
因为它最终将平坦的 1-dim 数组“转换”回相同的维度。

'[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?

加入声明为 Long 的“平面”数组的示例调用

为了显示解决方法代码行 [1]以及引发错误的代码行 [2] ,
我还集成了一个基本的错误处理，显示用户定义的错误行 ( ERL)。

VB 编辑器的即时窗口显示 ERL 200 中的错误 5:

 OK: [1] 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
    '[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

子FillNumbers由上述主程序调用

Sub FillNumbers(ByVal n As Long, arr)  
    ReDim arr(1 To n)
    arr(1) = 100
    arr(2) = 200
    arr(3) = 300
End Sub

Answer 1

正在尝试 Join() Long 的数组s 将失败:

Sub JoinTestFails()
    Dim Numbers(0 To 2) As Long, msg As String
    Numbers(0) = 0
    Numbers(1) = 1
    Numbers(2) = 2
    With Application.WorksheetFunction
        msg = Join(Numbers, "|")
    End With
    MsgBox msg
End Sub

TRANSPOSE()的双重使用通过生成 Variant 的一维、基于 1 的数组来解决这个问题。年代:

Sub JoinTest()
    Dim Numbers(0 To 2) As Long, msg As String

    Numbers(0) = 0
    Numbers(1) = 1
    Numbers(2) = 2

    With Application.WorksheetFunction
        Arr = .Transpose(.Transpose(Numbers))
        msg = LBound(Arr) & "**" & UBound(Arr) & vbCrLf
        msg = msg & Join(.Transpose(.Transpose(Numbers)), "|") & vbCrLf & TypeName(Arr)
    End With
    MsgBox msg
End Sub

对我来说， TRANSPOSE 的这种用法是不直观的。我宁愿做 Variant数组:

Public Function MkVar(arr() As Long) As Variant
'   make a variant array from a long array

    Dim temp() As Variant, i As Long
    ReDim temp(LBound(arr) To UBound(arr))
    For i = LBound(arr) To UBound(arr)
        temp(i) = arr(i)
    Next i
    MkVar = temp

End Function

接着:

Sub JoinTest2()
    Dim Numbers(0 To 2) As Long, msg As String

    Numbers(0) = 0
    Numbers(1) = 1
    Numbers(2) = 2

    arr = MkVar(Numbers)

    msg = LBound(arr) & "**" & UBound(arr) & vbCrLf
    msg = msg & Join(MkVar(Numbers), "|") & vbCrLf & TypeName(arr)

    MsgBox msg
End Sub