How to transpose horizontal array into vertical array in VBA-CodePudding

Why does the MsgBox not function when a horizontal array has been converted to a vertical array even though both array's return the same VarType?

Horizontal Array Transposed: [Does Not Work]

Dim Horizontal_Array As Variant
Horizontal_Array = Worksheets(sh.name).Range("A1:C1")
Horizontal_Array = Application.WorksheetFunction.Transpose(Horizontal_Array)
MsgBox Join(Horizontal_Array, vbCrLf)

Desired Output: Key1 Key2 Key3

Vertical Array [Works]

Dim Vertical_Array As Variant
Vertical_Array = Worksheets(sh.name).Range("A1:A4")
Vertical_Array = Application.WorksheetFunction.Transpose(Vertical_Array)
MsgBox Join(Vertical_Array, vbCrLf)

Output: Key1 One Four Seven

Key1	Key2	Key3
One	Two	Three
Four	Five	Six
Seven	Eight	Nine

CodePudding user response：

A horizontal array needs two Transpose operations:

Dim Horizontal_Array As Variant
Horizontal_Array = Worksheets(sh.name).Range("A1:C1")

Horizontal_Array = Application.WorksheetFunction.Transpose( _
         Application.WorksheetFunction.Transpose(Horizontal_Array))
MsgBox Join(Horizontal_Array, vbCrLf)

See discussion here: Why does Join() need a double transposition of a 1-dim Long array?