Wrong DataType parsed into Function

[jacko2401]

I have a function that starts as follows:

Function Timings(StartTime As Double, EndTime As Double, Times As Range, Calc As Long)

StartTime will refer to a cell in Column A that holds a date and time
EndTime will refer to a cell in Column B that holds a date and time
Times is the range of cells that hold times of the day I am testing against. The cells are within a range defined called Hours.
Calc refers to different criteria that I am testing again. This is a number list between 1 to 5.

The function works well if the cell that StartTime or EndTime refer to cell values that are date/time. If the data is anything different, I get a #VALUE error such as if the cell contents is text. I tried to trap the error to see if the data inputted in the cell is not date/time and thus cannot be converted to a Double but it always throughs up #VALUE and the function seems as though it fails when it . Is there anyway to trap the error of wrong data type in the first the first few lines of code in the function?

Example

Column A Column B Column C Formula
05/01/2013 12:00 06/01/2013 09:00 =Timings(A1,B1,Hours, 1)
25/02/2013 15:00 30/02/2013 14:30 =Timings(A2,B2,Hours, 1)
03/01/2013 16:00 abc =Timings(A3,B3,Hours, 1)

Row 2 will return #VALUE becuase 30/02/2013 14:30 is an invalid date (30th Feb does not exist so Excel seems to pickup as text)
Row 3 will return #VALUE because it is text and cannot be converted to a double data type

 

[pgc01]

Hi

For ex., for StartTime (same for others)

- declare StartTime in the Function header as Variant instead of Double. This way the function will have no problem if the parameter is text instead of a date

- If you want to check that StartTime is a Double value, start the Function with, for ex.:

If VarType(StartTime) <> vbDouble Then Exit Function

It's up to you if you want to do extra testing, like StartTime is a text value but convertible to datetime, etc.

 

[jacko2401]

Hi

For ex., for StartTime (same for others)

- declare StartTime in the Function header as Variant instead of Double. This way the function will have no problem if the parameter is text instead of a date

- If you want to check that StartTime is a Double value, start the Function with, for ex.:

If VarType(StartTime) <> vbDouble Then Exit Function

It's up to you if you want to do extra testing, like StartTime is a text value but convertible to datetime, etc.

That worked using variant. I then used cdbl to convert it to a double after making check on datatype. CDbl (StartTime) converts it ok. Need to di a bit more testing but seems to di the trick. Hopefully excel wont cause any trouble determining the datatype from setting it as variant.

 

[AlphaFrog]

Another method is to declare StartTime and EndTime as Range then test their values using the IsDate function. IsDate tests for Date and\or Time.

[COLOR=darkblue]Function[/COLOR] Timings(StartTime [COLOR=darkblue]As[/COLOR] [B]Range[/B], EndTime [COLOR=darkblue]As[/COLOR] [B]Range[/B], Times [COLOR=darkblue]As[/COLOR] Range, Calc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR])
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsDate(StartTime) [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'StartTime is not a time[/COLOR]
    [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] IsDate(EndTime) [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'EndTime is not a time[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
End [COLOR=darkblue]Function[/COLOR]

 

[mikerickson]

Or you could Excel do all the converting and testing

Function Timings (startTime As Date,...)

If you put =Timings(A1) in a cell and A1 contains a date or time (or a string representation of a date/time) all the conversion will be handled automaticaly. If A1 contains other than a date/time, a #VALUE error will be returned.

 

[jacko2401]

Another method is to declare StartTime and EndTime as Range then test their values using the IsDate function. IsDate tests for Date and\or Time.

[COLOR=darkblue]Function[/COLOR] Timings(StartTime [COLOR=darkblue]As[/COLOR] [B]Range[/B], EndTime [COLOR=darkblue]As[/COLOR] [B]Range[/B], Times [COLOR=darkblue]As[/COLOR] Range, Calc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR])
    
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] IsDate(StartTime) [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'StartTime is not a time[/COLOR]
    [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] IsDate(EndTime) [COLOR=darkblue]Then[/COLOR]
        [COLOR=green]'EndTime is not a time[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
End [COLOR=darkblue]Function[/COLOR]

I've read a lot about defining data types right and not to use Variant where possible so defining StartTime and EndTime As Range seems to allow me to confirm whether of not the data type of the Value of the cells is a date as per the following.

Function Timings(StartTime As Range, EndTime As Range, Times As Range, Calc As Long)

If Not TypeName(StartTime.Value) = "Date" Or Not TypeName(EndTime.Value) = "Date" Then
Timings = "Not a valid date"
Exit Function
End If

However, when I try to convert this to a Double data type, I get a #VALUE so think the range object cannot be converted to Double. The code I have tried is:

StartTime = CDbl(StartTime.Value)
EndTime = CDbl(EndTime.Value)

IS there a better way and/or limitation to what I have tried in getting StartTime and EndTime values to be Double datatypes?

 

[AlphaFrog]

I've read a lot about defining data types right and not to use Variant where possible so defining StartTime and EndTime As Range seems to allow me to confirm whether of not the data type of the Value of the cells is a date as per the following.

Function Timings(StartTime As Range, EndTime As Range, Times As Range, Calc As Long)

If Not TypeName(StartTime.Value) = "Date" Or Not TypeName(EndTime.Value) = "Date" Then
Timings = "Not a valid date"
Exit Function
End If

However, when I try to convert this to a Double data type, I get a #VALUE so think the range object cannot be converted to Double. The code I have tried is:

StartTime = CDbl(StartTime.Value)
EndTime = CDbl(EndTime.Value)

IS there a better way and/or limitation to what I have tried in getting StartTime and EndTime values to be Double datatypes?

Cannot assign a Double-type to a Range-type

Do as mikerickson suggests, or try something like this if you really want a Double-type instead of a Date-type.

[COLOR=darkblue]Function[/COLOR] Timings(StartTime [COLOR=darkblue]As[/COLOR] Range, EndTime [COLOR=darkblue]As[/COLOR] Range, Times [COLOR=darkblue]As[/COLOR] Range, Calc [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR])
    
    [COLOR=darkblue]Dim[/COLOR] dblStart [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR], dblEnd [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Double[/COLOR]
    
    [COLOR=darkblue]If[/COLOR] IsDate(StartTime) [COLOR=darkblue]Then[/COLOR] dblStart = [COLOR=darkblue]CDbl[/COLOR](CDate(StartTime))
    [COLOR=darkblue]If[/COLOR] IsDate(EndTime) [COLOR=darkblue]Then[/COLOR] dblEnd = [COLOR=darkblue]CDbl[/COLOR](CDate(EndTime))
    
End [COLOR=darkblue]Function[/COLOR]

 

[jacko2401]

Thanks. That is working how I intended it to. The reason for not letting Excel just come back with #VALUE is that there could be a few different reasons for it not to be a date. I just wanted to capture the different reasons and return a short explanation so that it explains what to change.

In terms of the solution, is it right that you can convert a range to a date using CDate and then convert from a date to a double using CDbl? However, you cannot convert a range directly to a Double? I used the combination as you demonstrated and get the results I needed. Thanks so much for your help. Think I need to get a better understanding of objects and data types!

 

[AlphaFrog]

Thanks. That is working how I intended it to. The reason for not letting Excel just come back with #VALUE is that there could be a few different reasons for it not to be a date. I just wanted to capture the different reasons and return a short explanation so that it explains what to change.

In terms of the solution, is it right that you can convert a range to a date using CDate and then convert from a date to a double using CDbl? However, you cannot convert a range directly to a Double? I used the combination as you demonstrated and get the results I needed. Thanks so much for your help. Think I need to get a better understanding of objects and data types!

CDate() is converting the Range.Value to a date. This is used in case the Range.Value is a Text-date. Then it converts it to Double.

What you had tried to do previously is to assign a Double to a Range object. You could put a Double in a Range.Value e.g.; Range("A1").Value = CDbl(x) which is not the same as trying to assign a double to a range object.

Clear as mud...right?

 

[jacko2401]

Yeah kinda. Thank you so much. I need to get my head clear between the object type and the value! Think I need a little more practice and exposure to these scenarios.