VBA difference between "Double" and "Variant" array types when transposing

Question

I would like someone to explain why VBA code works if arrays are defined as Variant type but not if they are defined as Double type, when transposing the array. I have this simple test code:

Option Base 1
Sub testDELput()
    Dim c() As Variant
    Dim d() As Variant
    Dim i As Integer
    ReDim c(5)
    For i = 1 To 5        'assign test values to c
        c(i) = 10 * i
    Next
    ReDim d(5, 1)
    d = Application.WorksheetFunction.Transpose(c)
End Sub

This code works fine, d is a column vector with the elements of c However, if I change the array declarations to Double, i.e.:

Dim c() As Double
Dim d() As Double

I got the following error:

I cannot understand why. Can anybody explain please?

The same happens if I redim c like this:

ReDim c(1, 5)

Thanks for the attention

Answer 1

Your understanding about array becomes strange if you do not know what Transpose function returns.

As Microsoft states here, it returns a Variant!

Sub testDoubleArray()
  Dim d() As Double, i As Long

  ReDim d(5, 1)
  For i = 1 To 5
        d(i, 1) = i * 100
  Next i
  
  Debug.Print Join(Application.Transpose(d), "|") 'just to visually see its content (after transforming in 1D array - Variant type)
End Sub

Then, in the example you try, the mismatch error comes from the fact that you cannot load a Double array with a Variant type one:

Sub testDELput()
    Dim c() As Double
    Dim d() As Variant 'because this is what Transpose returns!
    Dim i As Integer
    ReDim c(5): Debug.Print LBound(c): Stop
    For i = 1 To 5        'assign test values to c
        c(i) = 10 * i
    Next
    ReDim d(5, 1)
    
    With Application
       Debug.Print Join(.Transpose(.Transpose(c)), "|"): Stop '1D Variant type
        d = .Transpose(c)
    End With
    
    Debug.Print LBound(d), UBound(d), UBound(d, 2)
End Sub

You can load a Double array from another Double one, as you can see in the next sub:

Sub testDoubleArray()
  Dim d() As Double, c() As Double, i As Long

  ReDim d(5, 1)
  For i = 1 To 5
        d(i, 1) = i * 10
  Next i
  
  Debug.Print Join(Application.Transpose(d), "|") 'just to visually see its content (after tranforming in 1D array)
  ReDim c(5, 1)
  c = d
  
  Debug.Print Join(Application.Transpose(c), "|")
End Sub