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

Answer 2

WorksheetFunction.Transpose is declared to return a (single) Variant:

Function Transpose(Arg1) As Variant

A single Variant can contain all sorts of things, including an array of other data types.

The compiler knows that, so it does not raise a compile-time error immediately. It has to wait till runtime to see if, by any chance, that returned Variant happens to contain an array of Doubles, which is the only thing that your d() can contain.

It so happens that no, the Variant that Transpose returns does not contain an array of Doubles. It contains an array of Variants. Your d() cannot contain that, so a runtime type mismatch error occurs.

If Transpose were declared as explicitly returning an array of variants:

Function Transpose(Arg1) As Variant()

then instead of a runtime error 13 you would get a compile time error "Can't assign to array", because this time the compiler does not have to wait till runtime to check if the returned value is in fact compatible with d(). It can see straight away that it isn't.

The only way you would be able to assign to a d() As Double is if you had a function that explicitly returns a Double() (not even Single() or Long()), or a function that returns a single Variant that at the time of the call will happen to contain an array of Double():

Sub testDELput()
    Dim d() As Double
    
    d = returns_double_array_as_double_array()    'Works
    d = returns_double_array_as_variant()         'Works
    
    'd = returns_variant_array_as_variant()       'Run-time error 13: Type mismatch
    'd = returns_variant_array_as_variant_array() 'Compile-time error: Can't assign to array
End Sub

Private Function returns_double_array_as_double_array() As Double()
    Dim arr() As Double
    returns_double_array_as_double_array = arr
End Function

Private Function returns_double_array_as_variant() As Variant
    Dim arr() As Double
    returns_double_array_as_variant = arr
End Function

Private Function returns_variant_array_as_variant() As Variant
    Dim arr() As Variant
    returns_variant_array_as_variant = arr
End Function

Private Function returns_variant_array_as_variant_array() As Variant()
    Dim arr() As Variant
    returns_variant_array_as_variant_array = arr
End Function

Answer 3

My experience in the past has been similar, and I default to using the 'Variant' data type in situations where I'm programmatically changing the values contained in the variables you've denoted c() and d().

I understand that you're eluding to the fact that you would have expected the 'Double' data types being used should have otherwise allowed you to store the elements being declared.

So whilst 'Variant' can store different value types including integer, string, and dates etc. the 'Double' data type is specific to representing double-precision floating-point numbers.

Your code as far as I can tell is likely yielding integer numbers in the final result and NOT floating point numbers hence the Type mismatch error e.g. c(5) = 10 * 5 yields 50 (integer) and not 50.00 (double). You need to either coerce the result to be a float type e.g. 10 * 5.00 = 50.00 or dim c() and d() as an Integer.

Simply stating the data type as Double is likely not performing this conversion from Integer to Double automatically as one might assume.