CODEWITHSUNDEEP
vbaexcel
Nick Anderegg
Nick Anderegg

Reputation: 1016

Stopping Decimal Truncation in Visual Basic

How can I stop a decimal from being truncated in Visual Basic, and keep it as a full decimal rather than a truncated number in scientific notation?

I have this little bit of code from am Excel Macro that I'm modifying for someone (only the Debug.Print statements are mine):

rttime = asht.Cells(i, timecol).Value
Debug.Print "rttime=" & rttime
Debug.Print "To string: " & CStr(CDec(rttime))
rtdelta = rttime - wavonset_value 'searchcol="AOISlide2"
Debug.Print "rtdelta=" & rtdelta

The Debug.Print statements give me:

rttime=1.343301E+12
To string: 1343301000000
rtdelta=0

So basically what is happening here is the values are being pulled from the Excel Worksheet in scientific notation, and everything not shown in that notation is getting truncated.

The actual number should be 1343300687515.39, but it is being forced down to 1.343301E+12, and even if I force it out of scientific notation, it's still giving me 1343301000000.

How can I get it to be the actual number with decimal points and all? This aspect is practically required because in some instances, there might only be a difference of 20 or so, but since it's truncating to the millions place, it's completely useless.

Edit: Here's the entire function for reference

Function GetAOIRowStart(asht As Worksheet, startrow&, endrow&, searchstr$, searchcol&, 

AOItime_start As Single) As Long
Dim i&
Dim rtdelta As Single
Dim rttime As Single
Dim wavonset_value As Single
Dim timecol&


timecol = 4
wavonset_value = asht.Cells(startrow, timecol).Value 'tettime
Debug.Print "wavonset_value=" & wavonset_value

i = startrow
Do Until i >= endrow

    'scan down TeTTime' column to find nearest look of interest
    rttime = asht.Cells(i, timecol).Value
    Debug.Print "rttime=" & rttime
    Debug.Print "To string: " & CStr(CDec(rttime))
    rtdelta = CDec(Right(rttime, 9)) - CDec(Right(wavonset_value, 9)) 'searchcol="AOISlide2"
    Debug.Print "rtdelta=" & rtdelta
    If rtdelta >= AOItime_start Then
         Exit Do
    End If


    i = i + 1
Loop

GetAOIRowStart = i
'Debug.Print asht.Cells(i, sound_playing_col).Value
End Function

Upvotes: 1

Views: 1335

Answers (1)

Barranka
Barranka

Reputation: 21047

Your issue has to do with the size of the variable. If rttime is Single, which is not big enough to hold the data you need. Changing the variable type to Double should fix the problem.

As a thumb rule, unless you have very tight memory constraints, use Long to hold integer values and Double to hold floating-point values. Reserve the Decimal type for very precise calculations. I also suggest you avoid Variant types.

Upvotes: 1

Related Questions