VBA Converting type of array string to double without type mismatch RRS feed

  • Question

  • If I define Array1 as String and Array2 as Double and i As Long

    then say Array2(i) = CDbl(Array1(i)) then I get a type mismatch error

    Can get rid of error by changing all array types to Variant but then Variant is inefficient right?

    Code below

    Dim CountOriginalTimePeriods As Long
    Dim OriginalTimePeriods() As String
    Dim TimePeriods() As Double
    Dim i As Long
        CountOriginalTimePeriods = Range(Selection, Selection.End(xlToRight)).Count - 2
    ReDim OriginalTimePeriods(1 To CountOriginalTimePeriods) As String
    ReDim TimePeriods(1 To CountOriginalTimePeriods) As Double  
        For i = 1 To CountOriginalTimePeriods
        TimePeriods(i) = CDbl(OriginalTimePeriods(i))   'This is where I get the problem
        Next i

    Monday, April 22, 2013 7:28 PM

All replies

  • You will get a mismatch error if the string cannot be coerced to a value

    These fail: cdbl(""), cdbl("1a")

    but these work: cdbl("1"), cbdl("1.1")

    In your loop as posted your arrray contains empty strings "", which will CDbl cannot convert, though FWIW Val() could and return 0

    Peter Thornton

    Monday, April 22, 2013 10:08 PM