CODEWITHSUNDEEP
vbaexcel
Lauratoyos
Lauratoyos

Reputation: 31

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

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!

Upvotes: 3

Views: 1952

Answers (4)

user6432984
user6432984

Reputation:

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

enter image description here

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

Upvotes: 1

Slai
Slai

Reputation: 22876

In VBA you can assign .Value and .Value2 arrays only to a Variant

As a side note if the range is formated as table, you can just do something like 

Dim InputValues() ' As Variant by default
InputValues = [transpose(Hoja1!Table1[Column3])] ' Variant(1 to number of rows in Table1)

Upvotes: 0

user3598756
user3598756

Reputation: 29421

you can simply go like follows

Option Explicit

Sub main()
    Dim InputValues As Variant 'Define Array

    With Excel.ActiveWorkbook.Worksheets("Hoja1") ' refer to wanted worksheet
        InputValues = .Range("C2", .Cells(.Rows.Count, 3).End(xlUp)).value 'fill array with values in column "C" cells from row 2 down to last non emtpy one
    End With
End Sub

should you ever need to handle array values as of Double type, then you can use CDbl() function

Upvotes: 0

Chrismas007
Chrismas007

Reputation: 6105

Set ThisWS = Excel.ActiveWorkbook.Worksheets("Hoja1")
Dim CurRow As Long
Dim LRow As Long             'Define length of array
    LRow = ThisWS.Range("C" & Rows.count).End(xlUp).Row
Dim InputValues(1 to LRow - 1) As Double 'Define Array

For CurRow = 2 to LRow
    InputValues(CurRow - 1) = ThisWS.Range("C" & CurRow).Value
Next CurRow
End Sub

Upvotes: 0

Related Questions