Precision of Excel Spreadsheet Calculation different from Excel VBA Calculation

Question

I am working on a spreadsheet that is performing calculations on very small numbers, on the order of 1E-14. I had a draft of my process that leverages the spreadsheet and does not store any data in VBA variables. I do not like that approach and wanted to take the entire process behind the scenes into VBA.

What I am noticing is I cannot get the two approaches to match. Same operations different results. I am working with Excel 2013

An example in a cell 
=B42*COS(2*PI()*E$41*A42) 

or if I plug in values taking all figures reported i get the following 
0.156434465*Cos(2*3.14159265358979*0.012*0.05) 

this results in 
1.56433353400222E-01

If I do the same thing in VBA

gsum = Round(data(i + 1, 1), 10) * Cos(2 * pi * Freq * i * dt)

or 
0.156434465*Cos(2*3.14159265358979*0.012*1*0.05) 
i get a different answer 
Watch:Round(data(i + 1, 1), 10) * Cos(2 * Pi * Freq * i * dt):    
0.156433353359992 : Variant/Double : Calculation.realFT

I am not sure how to combat this. Somehow I lose a little each time. Everything on the VBA side is declared of type double except for I.

Any ideas on how i can get VBA to match the spreadsheets precision?

I know the differences are small but I am doing a lot of operations and they add up. Also I am confined to Excel for the application.

UPDATE

So I was looking at this some more and i have narrowed that the problem to the muliplication step between the Cosine output and the cell value. In both the excel and Excel VBA versions I can see that they are both the same but I am stuck with the following

In Excel 
0.999992893893247000000*0.156434465= 1.56433353400222E-01

In Excel VBA 
0.999992893893247000000*0.156434465= 1.56433353359991861557855E-01

who is right?

It is possible that Excel's cosine is better than VBA's. VBA is old (no change since the late 1990s) but Excel itself has been constantly updated. Maybe also a 32 vs 64 bit issue. — John Coleman, Oct 01 '15 at 17:00
@JohnColeman Hmmm, It seems VBA won't let me use the worksheet function ala WorksheetFunction.Cos. Anyway around this? — TheCodeNovice, Oct 01 '15 at 17:15
I *think* that `Application.Evaluate("0.156434465*Cos(2*3.14159265358979*0.012*1*0.05)")` will use Excel's cos() (rather than VBA's). But -- in my case (Excel 2010) I can't detect any difference between the VBA and the Excel evaluation of the expression -- they both return 0.156433353359992. Charles Williams is a guru when it comes to high-precision Excel VBA. You might enjoy his blog: https://fastexcel.wordpress.com/ . This is also cool: https://newtonexcelbach.wordpress.com/ — John Coleman, Oct 01 '15 at 18:12
Application.Evaluate gives me the VBA answer, maybe the same pathway? If you put the two values in two cells and multiply them then I get the other value. Is that the same for 2010 — TheCodeNovice, Oct 01 '15 at 18:20
Python can multiply long integers while retaining precision. I did this multiplication `999992893893247*156434465` in Python with the result `156433353359991861557855`, suggesting that VBA is correct. By the way VBA is giving 24 decimals of precision indicating 55 binary bits! — xidgel, Oct 01 '15 at 18:28

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

A few suggestions

Change variable type from double to currency or decimal (variant), or string

Double: 8 bytes

-1.79769313486232e+308 to -4.94065645841247E-324 for negative values
 4.94065645841247E-324 to  1.79769313486232e+308 for positive values

~~Currency: 8 bytes~~

~~-922,337,203,685,477.5808 to 922,337,203,685,477.5807~~

Decimal: 12 bytes (declared as Variant, then use conversion function CDec)

+/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use
+/-  7.9228162514264337593543950335 (28 decimal places)

Be aware of how to extract data from ranges to VBA:

Public Sub showData()
    Const NL As String = vbCrLf & vbCrLf & vbTab
    Const FR As String = "0.156434465 * Cos(2 * 3.14159265358979 * 0.012 * 0.05)"

    Dim v1 As Variant, v2 As Variant, v3 As Variant, v4 As Variant, v5 As Variant

    v1 = CDec((1 / 3) + (1 / 3) + (1.00059999999977E-03))
    v2 = CDec(CDec(1 / 3) + CDec(1 / 3) + CDec(1.00059999999977E-03))
    v3 = 0.156434465 * Cos(2 * 3.14159265358979 * 0.012 * 0.05)
    v4 = CDec(0.156434465 * Cos(2 * 3.14159265358979 * 0.012 * 0.05))
    v5 = CDec(CDec(0.156434465) * CDec(Cos(CDec(2 * 3.14159265358979 * 0.012 * 0.05))))

    With ActiveSheet.Cells(1, 1)
        MsgBox "Formula: " & vbTab & .Formula & NL & _
                "Text:   " & vbTab & .Text & NL & _
                "Value:  " & vbTab & .Value & NL & _
                "Value2: " & vbTab & .Value2 & vbCrLf & NL & _
                "VBA 1:  " & vbTab & v1 & NL & _
                "VBA 2:  " & vbTab & v2 & vbCrLf & vbCrLf & vbCrLf & _
                "Formula:  " & vbTab & FR & NL & _
                "VBA 3:  " & vbTab & v3 & NL & _
                "VBA 4:  " & vbTab & v4 & NL & _
                "VBA 5:  " & vbTab & v5, , "Precision"
    End With
End Sub

Number precision: 15 digits

Largest allowed positive number: 1.79769313486231E+308

Largest allowed negative number: -1E-307

Smallest allowed negative number: -2.2251E-308

Smallest allowed positive number: 2.229E-308

More info about the differences between .Text .Value and .Value2 in this link

Make sure you use .Value2 ("raw" data) if you don't work with dates or currency

Reference to MS article "Floating-point arithmetic may give inaccurate results in Excel"

The new data type adds more sigfigs but still off. Is it possible the spreadsheet is wrong or the way Excel does math is different between the two — TheCodeNovice, Oct 01 '15 at 18:13
You may find [Floating-point arithmetic](https://support.microsoft.com/en-us/kb/78113) useful — paul bica, Oct 01 '15 at 18:35

Precision of Excel Spreadsheet Calculation different from Excel VBA Calculation

1 Answers1