Define array to obtain data from range as "double" var type

Question

I'm fairly new to VBA, so please bear with me. I want to tell VBA to get an array from a range of cells. The user will paste a column of data into cell C2 so cells below C2 will be populated. The number of cells populated is up to the user.

I am also going to need each of the elements in the array to be taken as doubles as I'm going to make operations with them.

Therefore if the list is

1.2222
2.4444
3.5555

Then I need the array to preserve the decimal points. How do I do this? This is what I've got this fur, with no luck:

Set ThisWS = Excel.ActiveWorkbook.Worksheets("Hoja1")
Dim InputValues() As Double 'Define Array
Dim LRow As Long             'Define length of array
With Sheets("Hoja1")
    LRow = .Range("C" & .Rows.count).End(xlUp).Row
End With
InputValues = ThisWS.Range("C2:C" & LRow).Value 'Error 13: data type doesn't match
End Sub

Thanks!

Answer 1

Excel.ActiveWorkbook. isn't needed in Excel, it is implied. I didn't need to type cast the cell value CDbl(.Cells(x, "C")).

Sub Example()
    Dim InputValues() As Double
    Dim lastRow As Long, x As Long

    With Worksheets("Hoja1")
        lastRow = .Range("C" & .Rows.Count).End(xlUp).Row

        ReDim InputValues(lastRow - 2)

        For x = 2 To .Range("C" & .Rows.Count).End(xlUp).Row
            InputValues(x - 2) = CDbl(.Cells(x, "C"))
        Next
    End With

End Sub

---

This example is more efficient but won't make a noticeable difference unless you are working with a very large amount of data.

Sub Example2()
    Dim InputValues() As Double, vInputValues As Variant
    Dim x As Long

    With Worksheets("Hoja1")
        vInputValues = .Range("C2", .Range("C" & .Rows.Count).End(xlUp)).Value2

        ReDim InputValues(UBound(vInputValues) - 1)

        For x = 1 To UBound(vInputValues)
            InputValues(x - 1) = CDbl(vInputValues(x, 1))
        Next
    End With

End Sub