Transposing an array using an in-memory listbox

There are many ways of transposing an array in VBA. Here’s another fairly simple one which is not subject to the size limits of application.transpose because it uses a ‘virtual’ MSForms.Listbox control

Function TransposeIt(vData)
Dim lBound2 As Long

lBound2 = -1
If IsArray(vData) Then
' test for 1D array
On Error Resume Next
lBound2 = UBound(vData, 2)
On Error GoTo 0
' create MSForms.ListBox
With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
.Column = vData
If lBound2 = -1 Then
' for 1D, returning the Column will transpose and return 2D array
TransposeIt = .Column
' for 2D array just return the List
TransposeIt = .List
End If
End With
End If
End Function

2 thoughts on “Transposing an array using an in-memory listbox

  1. Pingback: workaround for writing a variant array to worksheet using the Transpose Function

  2. Pingback: Excel Matters » Blog Archive » Late bound MSForms.DataObject

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.