How transform Variant to Double format and vice versa in VBA

Question

When I want to import data using VBA I use following command

Dim FinalArray As Variant
ArrayData = Range("DATA").Value

I also tried doing with the loop, but got error in this place (newData (0, newii))


Dim Data As Variant 
Dim newData As Double 
Dim i As Long 

Data= Range("horiz2").Value

For i= 0 To 11 Step 1
newData (0, newii) = Data(1, i+1)
Next i

When I run this code, I have data stored as Variant/Variant (1 to 1, 1 to 12) type. At the same time, I notice that while doing some calculations inside the macro, I have a table X where the same values are in Double(0 to 0, 0 to 11) type.

How can I import data from a range in Double format - (Double(0 to 0, 0 to 11) to Variant/Variant (1 to 1, 1 to 12))
How can I transform the table in Double format to Variant (Variant/Variant (1 to 1, 1 to 12) to Double(0 to 0, 1 to 12))?

create new arrays of the bound you want and loop the primary and put it in the new. There is no one step procedure to do this. — Scott Craner, Apr 01 '21 at 15:35
`Variant` is not a `Variant` subtype, the runtime type of your 2D variant array would be `Variant/Double`. Use the `CDbl` function to convert to a `Double`; writing a `Double` into a `Variant` array will make the value `Variant/Double` in that array. As for the transformations, they'd need nested loops, which can be inefficient. Consider modifying your code to work with the 1-based 2D `Variant` array, and use the `VarType` function to validate the underlying `Variant` subtype of each element before attempting a conversion that could be illegal. — Mathieu Guindon, Apr 01 '21 at 16:39

score 0 · Answer 1 · answered Apr 01 '21 at 16:28

You will need to loop the arrays to transform them as needed. Here are some helper functions to aid in this endeavor:

Private Function VariantArrayToDoubleArray(ByRef VariantArray As Variant) As Double()
   Dim i As Integer
   Dim j As Integer
   Dim da() As Double
   
   ReDim da(LBound(VariantArray, 1) - 1 To UBound(VariantArray, 1) - 1, _
            LBound(VariantArray, 2) - 1 To UBound(VariantArray, 2) - 1)
   
   For i = LBound(VariantArray, 1) To UBound(VariantArray, 1)
      For j = LBound(VariantArray, 2) To UBound(VariantArray, 2)
         da(i - 1, j - 1) = VariantArray(i, j)
      Next
   Next
   
   VariantArrayToDoubleArray = da
End Function

Private Function DoubleArrayToVariantArray(ByRef DoubleArray() As Double) As Variant
   Dim i As Integer
   Dim j As Integer
   Dim va() As Variant
   
   ReDim va(LBound(DoubleArray, 1) + 1 To UBound(DoubleArray, 1) + 1, _
            LBound(DoubleArray, 2) + 1 To UBound(DoubleArray, 2) + 1)
   
   For i = LBound(DoubleArray, 1) To UBound(DoubleArray, 1)
      For j = LBound(DoubleArray, 2) To UBound(DoubleArray, 2)
         va(i + 1, j + 1) = DoubleArray(i, j)
      Next
   Next
   
   DoubleArrayToVariantArray = va
End Function

Here's how to use the helper functions:

Private Sub Test()
   Dim va(1 To 1, 1 To 12) As Variant
   va(1, 1) = 4
   va(1, 2) = 42
   va(1, 3) = 52
   
   Dim da() As Double
   da = VariantArrayToDoubleArray(va)
   
   Dim va2 As Variant
   va2 = DoubleArrayToVariantArray(da)
End Sub

How transform Variant to Double format and vice versa in VBA

1 Answers1

Linked