Excel 365, Evaluate, and the Type Mismatch error

If you have a version of Excel that no longer requires you to array enter array formulas by using Ctrl+Shift+Enter, you may discover an unexpected side effect if you write code that uses Evaluate with a formula containing one or more ROW() or COLUMN() worksheet functions. Any such code will now always return an array, even if the result is just one value.

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