Cdbl crashing when it shouldn't

[JeffCorkern]

The following statement crashes with a "Type mismatch" error in a userform event-handler:

CDbl(DOTextbox.Value)

I can't find out why it's crashing. Msgbox TypeName(DOTextbox.Value) returns "String." MsgBox CDbl(DOTextbox.Value) also crashes with a "Type mismatch" error. The general help just says CDbl(expression) but doesn't specify what the "expression" has to be. It also doesn't list what error CDbl returns when it's unable to convert "expression".

(Excel 2010 VBA no longer has highly specific context-specific help and simply kicks you to a Web page where you wander around a lot and maybe you find the answer but mostly you don't. I find this EXTREMELY aggravating.)

Here's the code, for what it's worth. The CStr is me trying to beat VBA into submission. It's not working. It's crashing on the Select Case Statement.

Private Sub OKButton_Click()


Select Case CDbl(CStr(DOTextBox.Value)) <> 0 And CDbl(CStr(TemperatureTextBox.Value)) <> 0


Case True
ActiveCell.Value = CDbl(DOTextBox.Value)
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value & " " & CDbl(TemperatureTextBox.Value) & "C"
Case False
If CDec(DOTextBox.Value) = 0 Then
MsgBox DOTextBox.Value & " is not a number. Please enter a number."
DOTextBox.SetFocus
End If


If CDec(TemperatureTextBox.Value) = 0 Then
MsgBox TemperatureTextBox.Value & " is not a number. Please enter a number."
TemperatureTextBox.SetFocus
End If
End Select


End Sub

 

[circledchicken]

DOTextbox.Value

What is an example string where it gives you the error?

(Excel 2010 VBA no longer has highly specific context-specific help and simply kicks you to a Web page where you wander around a lot and maybe you find the answer but mostly you don't. I find this EXTREMELY aggravating.)

You can change to the Offline version - in the bottom right corner of the help box change the state to 'Show content only from this computer'.

 

[JeffCorkern]

The light begins to dawn.

It crashes when I feed it a non-convertible string. I feed it "8.38", everything works just fine. I feed it "fred", it crashes. This is because CDbl is returning an error code instead of the string CDbl has to have. Geez, if I had context-specific help, it would tell me what error code is coming back when it crashes.

There is offline help, but it's still not context-specific.

 

[shg]

You can test first:

Private Sub OKButton_Click()
    Dim dDO         As Double
    Dim dTmp        As Double

    If IsNumeric(DOTextBox.Value) Then
        dDO = CDbl(DOTextBox.Value)
    Else
        DOTextBox.SetFocus
        MsgBox "Oops #1!"
        Exit Sub
    End If

    If IsNumeric(TemperatureTextBox.Value) Then
        dTmp = CDbl(TemperatureTextBox.Value)
    Else
        TemperatureTextBox.SetFocus
        MsgBox "Oops #2!"
        Exit Sub
    End If
    
    ActiveCell.Value = dDO
    ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value & " " & dTmp & "C"
End Sub

 

[mikerickson]

You might prefer Val to CDbl. Val("zys") = 0

 

[shg]

I think Val is not international-aware, if the OP cares.

 

[JeffCorkern]

"IsNumeric" is the Magic Function I needed. I had a routing using "Val", but I didn't like that because entering 0 would've caused an error message, although it is actually a number.

This routine does what I want:

Private Sub OKButton_Click()


Select Case IsNumeric(DOTextBox.Value) And IsNumeric(TemperatureTextBox.Value)


Case True
ActiveCell.Value = Val(DOTextBox.Value)
ActiveCell.Offset(0, 3).Value = ActiveCell.Offset(0, 3).Value & " " & Val(TemperatureTextBox.Value) & "C"
ActiveCell.Offset(1, 0).Select
Case False
If Not (IsNumeric(DOTextBox.Value)) Then
MsgBox DOTextBox.Value & " is not a number. Please enter a number."
DOTextBox.SetFocus
End If


If Not (IsNumeric(TemperatureTextBox.Value)) Then
MsgBox TemperatureTextBox.Value & " is not a number. Please enter a number."
TemperatureTextBox.SetFocus
End If
End Select


'clear values
DOTextBox.Value = ""
TemperatureTextBox.Value = ""
DOTextBox.SetFocus


End Sub

Just FYI, I am replacing manual data entry---writing Dissolved Oxygen (DO) concentration and sample temperature on paper(how very last-millennium)---with electronic data entry into an old tablet PC that can be carried around the lab. Much faster and more efficient.

Thanks for the help, people.

 

[shg]

You're welcome, good luck.