Excel macro problem, 2 vars same but subtract <> 0 2

[ls62]

Hi,

I have a vba macro in excel that loads a text file, parses through the lines and adds up two different values from the records.

When I look at the values they are exactly the same, but with I use them in a 'if' statement or set another variable to the subtracted result I get some wierd 9.1023xxx number.

Can someone help me figure out what's wrong in my macro.

Ex: DepAmt= 5746.49
DebAmt= 5746.49
v=DepAmt-DebAmt
: v : 9.09494701772928E-13 : Variant/Double

======
Here's the part of my code that parses and totals:
Dim Msg, T As String
Dim PayDt
Dim DepAmt As Double, DebAmt As Double, Tmp As Double

For R = 1 To RW
T = Range("A" & R).Value 'Record Id
'
' Batch Header 'Get pay Date
'
If T = "520" Then
PayDt = Mid(Range("B" & R).Value, 67, 6) 'Dt
End If

'
' Debit Record (TRansfer)
'
If T = "627" Then 'Debit record
Tmp = Mid(Range("B" & R).Value, 27, 10)
DebAmt = DebAmt + (Tmp / 100) 'Debit $
End If

'
' Employee Deposit amounts
'
If T = "632" Or T = "622" Then 'Deposit record
Tmp = Mid(Range("B" & R).Value, 27, 10)
DepAmt = DepAmt + (Tmp / 100) 'Deposit $
End If

Next R

'
' Verify Deposit and Debit amounts Match
'
v = DepAmt - DebAmt 'test
If (DepAmt - DebAmt) > 0 Then 'GOT A PROBLEM
Msg = "Employee Deposits of [$" & DepAmt & "] does not match Employer" & _
" debit [$" & DebAmt & "]. Call Lee/Gail."
x = MsgBox(Msg, 0)
GoTo Exitout
End If

 

[TonyJollans]

This is due to the inherent inaccuracies of floating point arithmetic. You have a couple of easy options:

[ul][li]Round the result[/li][li]Use a different data type (currency)[/li][/ul]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[ls62]

Tony,

That's weird. I went and took out the 'Tmp' variable declaration (which I believe made it string/variable) and it now works. The two variable subtract and =0.

All the numbers being put into 'Tmp' orginally were like '12455', '34552' and I was only div by 100 so why would I get wierd floating point problems? What data type would be best to use for my variable dim's in this case?

I think originally I didn't have any of these dim'd and had problems.. that's why I started setting them up as dim'd variables.

Thanks for your help.
LEE

 

[TonyJollans]

Hi Lee,

I'm not sure I entirely follow what you've done but floating point arithmetic is inherently inaccurate and, while you may have succeeded for the moment there is no guarantee that different inputs won't bring the problem back in the future.

The inaccuracies are in decimal positions far lower than you are interested in and, to be sure all is well, you would be best to round your numbers off.

It's best to declare all your variables (and use Option Explicit so the compiler forces you to) - if you want variants, they have their uses but still declare them - if you don't, sooner or later you'll have a problem caused by a typo. If you're using non-integers you have to use a type that will work with them. Apart from floating point, that's currency (or decimal - but that's rather specialised).

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[ls62]

Tony,

I too must be missing something, I can't believe that the math processing in the macro is this bad. I'll give you a example of the data in the column I'm parsing:

abcde0001234575xxyyss

i'm trying to parse out the '0001234575' part which is actually 12345.75. I know my record characters are not exactly the same in my sample above but you get the idea. I'm using the following statements to do it:

Tmp = Mid(Range("B" & R).Value, 27, 10)
DebAmt = DebAmt + (Tmp / 100) 'Debit $

ARe you saying I still have to round this off because it can't do 1234575/100 and get the correct value of 12345.75? That it might come up with 12345.7532121 or something?

LEE

 

[pbrodsky]

This is an issue with the way numbers are stored in a computer. Each number needs to be stored in a fixed spaces (fixed number of bits). In order to handle arbitrary sizes of numbers and fractions, there are several standard number formats available - represented in a programming language as data types. In VB, these are typically 'integer', 'long', 'currency', 'single', or 'double'. Each has limitations on what numbers can be stored and the accuracy of the numbers.

The bottom line is that a number with a fraction might NOT have an exact representation in a particular data type, and you might need rounding.

 

[ls62]

Tony,

Ok, I understand. So there is a 'Dim DepAmt as currency'... I just figured double would work as well, now I know better. I will switch to 'currency' and see how that works.

Thanks for your help.

LEE

 

[TonyJollans]

Hi Lee,

Your example should be OK but you've got to remember that computers work in binary, so 0.75 (decimal) is 0.11 (binary)
but some quite simple numbers can be difficult. For example 0.2 decimal is 0.001001001001001... in binary - it can't be properly represented and an approximation must be used. The end result of a calculation which should be 0.2 might actually be 0.20000000....00001 or something and rounding to an appropriate number of places (after converting - or on conversion - to a non-fp type) avoids having to deal with the tiny increments.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[TonyJollans]

Looks like I missed a couple of posts there ..

Currency is of a type known as a scaled integer and is specially treated to do accurate decimal arithmetic. Be aware, however, that values stored in Excel cells are floating point values so you still have the potential for error if that's where your data come from.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[