CODEWITHSUNDEEP
stringvbatype-conversiondouble
c.olson
c.olson

Reputation: 121

Convert VBA String to Double

I am using the very basic coding of VBA for word to create a template that pulls data from other screens in windows. When it pulls the numbers, they are formatted as strings. I now need to get the strings to be converted to doubles, in order to add/ subtract them. I have been trying everything but cannot seem to figure it out. 

Me.salesprice = Trim(scrn.GetString(11, 65, 10))
'This would be formatted as 25,000.00
Me.salestax = Trim(scrn.GetString(12, 66, 10))
Me.pastdue = Trim(scrn.GetString(14, 65, 10))
Me.assessedppt = Trim(scrn.GetString(18, 66, 10))
Me.secdep = Trim(scrn.GetString(17, 65, 10))

assessedppt = Convert.ToDouble(Me.assessedppt)
uappt = Convert.ToDouble(Me.uappt)
salesprice = Convert.ToDouble(Me.salesprice)
salestax = Convert.ToDouble(Me.salestax)
pastdue = Convert.ToDouble(Me.pastdue)
lc = Convert.ToDouble(frmDetails.lc)

totalfinance = salesprice + salestax + pastdue - secdep + assessedppt + uappt + lc
totalsalesprice = salesprice + pastdue
ppt = assessedppt + uappt

When I do this, I get the following error:

Compile error: Variable not defined, and it highlights the first Convert function.

Upvotes: 12

Views: 137528

Answers (3)

Top-Master
Top-Master

Reputation: 8745

This is based on my other answer:

In case the user is allowed to use other characters (for example, the $ sign), then the below function could be useful (basically, fail-safe version of Guilherme's answer):

'
' 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

Upvotes: 3

Darrell Kolodka
Darrell Kolodka

Reputation: 9

This hurts to see this and how verbose these solutions are! I hope this helps someone...

Dim str_Impl_Vol As String ' let's say 37.9% is the string

Dim str_Impl_Vol = Left(ActiveSheet.Cells(intCurrentRow, intCol_Impl_Vol), 
                   Len(ActiveSheet.Cells(intCurrentRow, intCol_Impl_Vol)) - 1)
                   ' Remove the %

Dim dbl_Impl_Vol As Double ' let's say we need 37.9% as 0.379

dbl_Impl_Vol = CDbl(str_Impl_Vol / 100) ' just do it!

Any questions?

Upvotes: -2

Guilherme Felipe Reis
Guilherme Felipe Reis

Reputation: 918

You're using the wrong function to convert. You need to use CDbl, in VBA we have the follow convert functions:

numberDouble = CDbl("10") 'For convert to double
numberInteger = CInt("12") 'For convert to Integer
varString = CStr("11") 'For convert to String
bool = CBool("true") 'For convert to Boolean

So if you change your Convert.toDouble, your code will looks like that:

Me.salesprice = Trim(scrn.GetString(11, 65, 10))
'This would be formatted as 25,000.00
Me.salestax = Trim(scrn.GetString(12, 66, 10))
Me.pastdue = Trim(scrn.GetString(14, 65, 10))
Me.assessedppt = Trim(scrn.GetString(18, 66, 10))
Me.secdep = Trim(scrn.GetString(17, 65, 10))

assessedppt = CDbl(Me.assessedppt.value)
uappt = CDbl(Me.uappt.value)
salesprice = CDbl(Me.salesprice.value)
salestax = CDbl(Me.salestax.value)
pastdue = CDbl(Me.pastdue.value)
lc = CDbl(frmDetails.lc.value)

totalfinance = salesprice + salestax + pastdue - secdep + assessedppt + uappt + lc
totalsalesprice = salesprice + pastdue
ppt = assessedppt + uappt

Upvotes: 27

Related Questions