Convert Textbox.Value into a double Variable

Question

This question has been asked before but the answers have not helped.

I am trying to convert a textbox.value in a userform to a double Variable using Cdbl. I get a type mismatch error.

I want to convert it to a double because I am trying to use those values to calculate several values.

I found a code that says that you first need to check whether the values are numeric but I am not sure how to implement it. I kept it below as comments.

Private Sub CommandButton10_Click() 'to find the revaluation Gain or loss
    
    Dim Revaluation_surplus As Double
    Dim Revaluation_loss As Double
    Dim Fair_Value As Double
    Dim Carrying_Amount As Double
    
    Revaluation_surplus = CDbl(UserForm1.TextBox9.Value)
    Revaluation_loss = CDbl(UserForm1.TextBox10.Value)
    Fair_Value = CDbl(UserForm1.TextBox8.Value)
    Carrying_Amount = CDbl(UserForm1.TextBox7.Value)
    
    If Fair_Value > Carrying_Amount Then
        Revaluation_surplus = (Fair_Value - Carrying_Amount)
    ElseIf Fair_Value < Carrying_Amount Then
        Revaluation_loss = (Carrying_Amount - Fair_Value)
    End If
        
    'If IsNumeric(TextBox9.Value) Then
    'Revaluation_surplus = CDbl(UserForm1.TextBox9.Value)
    'Else
    'Revaluation_surplus = 0
    'End If

End Sub

Answer 1

Try

CDbl(Val(UserForm1.TextBox9.Value))

For example

?CDbl(Val("  1  .  2 ")) '~~> This will give you 1.2
?CDbl(Val("  "))         '~~> This will give you 0

Having said that, my recommendation would actually be to restrict the input in the textbox. Make it accept only numbers and decimal. This way you do not have to do any validation post input. For example

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case vbKey0 To vbKey9, vbKeyBack, vbKeyClear, vbKeyDelete, _
        vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown, vbKeyTab
            If KeyAscii = 46 Then If InStr(1, TextBox1.Text, ".") Then KeyAscii = 0
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

If there are many textboxes then you can use Control Array to make all textboxes to accept only numbers and decimal.

Answer 2

You could implement it like this:

Private Sub CommandButton10_Click()
    'to find the revaluation Gain or loss
    
    Dim Revaluation_surplus As Double
    Dim Revaluation_loss As Double
    Dim Fair_Value As Double
    Dim Carrying_Amount As Double
    
    If IsNumeric(TextBox9.Value) Then
        Revaluation_surplus = CDbl(TextBox9.Value)
    Else
        Revaluation_surplus = 0
    End If
    If IsNumeric(TextBox10.Value) Then
        Revaluation_loss = CDbl(TextBox10.Value)
    Else
        Revaluation_loss = 0
    End If
    If IsNumeric(TextBox8.Value) Then
        Fair_Value = CDbl(TextBox8.Value)
    Else
        Fair_Value = 0
    End If
    If IsNumeric(TextBox7.Value) Then
        Carrying_Amount = CDbl(TextBox7.Value)
    Else
        Carrying_Amount = 0
    End If
    
    If Fair_Value > Carrying_Amount Then
        Revaluation_surplus = (Fair_Value - Carrying_Amount)
    ElseIf Fair_Value < Carrying_Amount Then
        Revaluation_loss = (Carrying_Amount - Fair_Value)
    End If

End Sub

With this code I didn't get any errors, no matter the input in the TextBoxes...

As a side note, you are not doing anything with the calculated values yet. To, for example, display them in the TextBoxes, you'd have to insert something like

TextBox9.Value = Revaluation_surplus
TextBox10.Value = Revaluation_loss

after you perform the calculations. Also, at this stage getting the values from TextBox9 and TextBox10 isn't even needed, so your final code might look like this:

Private Sub CommandButton10_Click()
    'to find the revaluation Gain or loss
    
    Dim Revaluation_surplus As Double
    Dim Revaluation_loss As Double
    Dim Fair_Value As Double
    Dim Carrying_Amount As Double
    
    If IsNumeric(TextBox8.Value) Then
        Fair_Value = CDbl(TextBox8.Value)
    Else
        Fair_Value = 0
    End If
    If IsNumeric(TextBox7.Value) Then
        Carrying_Amount = CDbl(TextBox7.Value)
    Else
        Carrying_Amount = 0
    End If
    
    If Fair_Value > Carrying_Amount Then
        Revaluation_surplus = (Fair_Value - Carrying_Amount)
    ElseIf Fair_Value < Carrying_Amount Then
        Revaluation_loss = (Carrying_Amount - Fair_Value)
    End If

    TextBox9.Value = Revaluation_surplus
    TextBox10.Value = Revaluation_loss

End Sub