Convert VBA String to Double

Question

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.

`Convert.ToDouble` is VB.NET, which is a completely different language. In VBA the function is `CDbl`. — Comintern, Feb 27 '17 at 19:12

score 25 · Answer 1 · answered Feb 27 '17 at 19:34

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

There are a lot more [conversion functions](https://msdn.microsoft.com/en-us/library/office/gg278896.aspx) than the 4 mentioned, but I guess they are the main ones. — YowE3K, Feb 27 '17 at 20:28

Top-Master · Answer 2 · 2022-06-05T01:54:19.297

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

If interested in the other way around (converting numbers to string) check [FormatNumber(...)](https://stackoverflow.com/a/56815604/8740349) — Top-Master, Jun 30 '19 at 04:52

score -1 · Answer 3 · edited Aug 11 '19 at 20:32

-1

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?

edited Aug 11 '19 at 20:32

Paritosh Singh

6,034
2
14
33

answered Aug 11 '19 at 20:10

Darrell Kolodka

11
1

This is a new answer on a very old question that doesn't really add anything new, and isn't even self contained. I do not see how this adds anything of value here, the top voted answer sufficiently coverts the main question already. – Paritosh Singh Aug 11 '19 at 20:35

Convert VBA String to Double

3 Answers3

Linked