Convert TextBox.Value to Double into VBA (Excel 2013)

Question

I have TextBox into my form, where user can input a value. In VBA I need to convert the value from string to double.

I'm doing it like:

Private Sub UserForm_Initialize()

    '....some code
    Dim new_value As Double
    new_value = CDbl(TextBox6.Value)

End sub

But I'm getting the error below:

Answer 1

CDbl expects already a number but if the textbox is empty then TextBox6.Value is an empty string. CDbl can't cast an empty string into a double.

You can validate if the textbox is a numeric value first to avoid this

If IsNumeric(TextBox6.Value) Then
    new_value = CDbl(TextBox6.Value)
Else
    new_value = 0
End If

Alternatively the Val() function might be an option for you.

new_value = Val(TextBox6.Value)

Answer 2

In case the user is allowed to use other characters (for example, the $ sign), then the below function could be useful:

'
' Skips all characters in the input string except
'  the first negative-sign, digits, and the first dot
'
Function ParseNumber(ByVal s As String) As Double
    ParseNumber = 0#
    Dim char As String
    Dim i As Integer
    Dim digits$
    Dim isNegative As Boolean
    Dim isPastDot As Boolean
    For i = 1 To Len(s)
        char = Mid(s, i, 1)
        If char >= "0" And char <= "9" Then
            digits = digits + char
        ElseIf char = "-" Then
            If Len(digits) <= 0 Then
                isNegative = True
            End If
        ElseIf char = "." Then
            If Not isPastDot Then
                isPastDot = True
                digits = digits & "."
            End If
        End If
    Next i
    ParseNumber = CDbl(digits)
    If isNegative Then
        ParseNumber = 0 - ParseNumber
    End If
End Function