Excel VBA adding appears to be rounding values that I'm adding??

[RAYLWARD102]

All my variables are Doubles.
I have formula like: a X b = Calculated_Value
Then I keep a running total of the Calculated_Values: TotalValuesCombined = TotalValuesCombined + Calculated_Value
TotalValuesCombined = 140395826 (what VBA has calculated)
If I dump all values to spread sheet and manullay sum them, they sum to 137046355.3
What can I do about this? Like said, all variables are doubles. I think the adding is some how rounding and adding; is there a way to make it stop?

 

[StephenCrump]

This doesn't look like a rounding issue.

If VBA calculates 140 million and Excel calculates 137 million, it suggests that the individual values you are calculating in VBA aren't all being dumped into Excel, i.e. there is an issue with what your code is doing.

Suggest you post your code.

 

[RAYLWARD102]

Hopefully you can show me the error of my ways;
I'm posting the loop which calculates with vba and creates a formula using same values to be calculated from an excel sheet.

    Dim TotalBTURecoredPeriod As Double
    Dim BTU_This_Record As Double
    Dim Percent_This_Record As Long
    Dim BTU_Calc As Double
        
        For x = 1 To UBound(T, 2)
            BTU_This_Record = 0: Percent_This_Record = 0: BTU_Calc = 0
            BTU_This_Record = T(fieldcol(T, "btumax"), x)
            Percent_This_Record = T(fieldcol(T, "percenton"), x)
            If BTU_This_Record > 0 Then BTU_Calc = ((Percent_This_Record / 300) * BTU_This_Record)
            'TotalBTURecoredPeriod will = 140395826 after loop completes
            TotalBTURecoredPeriod = TotalBTURecoredPeriod + BTU_Calc 'Get all consumed BTU's total
            'below formula dumped into excel, then manually summed will = 137046355.3
            T(fieldcol(T, "formula"), x) = "((" & Percent_This_Record & "/ 300) * " & BTU_This_Record & ")"
        Next x

 

[StephenCrump]

If BTU_This_Record <=0, your VBA code sets BTU_Calc to zero

BTU_Calc = 0
'...
If BTU_This_Record > 0 Then BTU_Calc = ((Percent_This_Record / 300) * BTU_This_Record)

But your Excel formula doesn't do this. it calculates the formula for all values of BTU_Calc.

You could put the Excel formula inside the If BTU_This_Record <=0 block. Or adjust the formula.

 

[RAYLWARD102]

I ran the code without the ‘if’ statement and still same result; if was only on 0 vales anyways. Still not seeing a smoking gun here.

 

[StephenCrump]

Just a first guess. You'll need to provide some more code ...

- What is T?
- What is the code for fieldcol?
- How are you dumping the results into Excel?

 

[RAYLWARD102]

T is a 2D array variant
Fieldcol is a function for locating a column in the 2D array, by field name.
I'm dumping the results into excel like ws.cells(1,1).value = TotalBTURecoredPeriod
and for the others, it's in a loop; the output loads about 150 cells with the formula mentioned earlier. The values presented by those formulas, sum to 137046355.3; the TotalBTURecordedPeriod = 140395826
I suspect that adding a double, many times in the provided loop, is some how only adding to a certain decimal place; different from when I add the same values in the spread sheet via SUM. I'm wondering what I can do to get the TotalBTURecoredPeriod (doulbe value) to add all decimal places; both should have the same value as they are adding the same things

 

[StephenCrump]

I suspect that adding a double, many times in the provided loop, is some how only adding to a certain decimal place; different from when I add the same values in the spread sheet via SUM.

137 million vs 140 million is not a rounding issue.

It means there's a mismatch between what you're summing in VBA and what you're dumping into Excel to add using the Sum function.

Without seeing your code, it's impossible to diagnose further. But perhaps try running this test, with modifications to your code highlighted in red:

[COLOR=#ff0000][B]Dim test() As Double

'....

ReDim test(1 To UBound(T))[/B][/COLOR]

'...

For x = 1 To UBound(T, 2)
    BTU_This_Record = 0: Percent_This_Record = 0: btu_calc = 0
    BTU_This_Record = T(fieldcol(t, "btumax"), x)
    Percent_This_Record = T(fieldcol(t, "percenton"), x)
    If BTU_This_Record > 0 Then btu_calc = ((Percent_This_Record / 300) * BTU_This_Record)
    [COLOR=#ff0000][B]test(x) = btu_calc[/B][/COLOR]
    'TotalBTURecoredPeriod will = 140395826 after loop completes
    TotalBTURecoredPeriod = TotalBTURecoredPeriod + btu_calc 'Get all consumed BTU's total
    'below formula dumped into excel, then manually summed will = 137046355.3
    T(fieldcol(t, "formula"), x) = "((" & Percent_This_Record & "/ 300) * " & BTU_This_Record & ")"
Next x

'Dump btu_calc's - this is what you're summing in VBA.  Do these values match what you're summing in Excel?
'Range A1 for illustration purposes - choose a range somewhere out of the way
[COLOR=#ff0000][B]Range("A1").Resize(,UBound(T)).Value = test
[/B][/COLOR]

I am also guessing that lbound(T) is 1?

 

[RAYLWARD102]

(1) you can clearly see in my example, the 'double' variables are having a value assigned to them and some arithmetic is performed. The arithmetic is totaled at TotalBTURecoredPeriod = TotalBTURecoredPeriod + btu_calc (will equal 140395826 after the loop has completed)

(2) you can clearly see in my example, the exact same array values are being used to formulate a formula text, that is an exact representation of the arithmetic being performed in above statement (1). When these formula's are dumped into an excel spreadsheet, they can be summed via selecting the values; they mysteriously equal 137046355.3; not 140395826.
The spreadsheet formatting is 'General', the values created by all of the formulas are mostly 4-6 decimal place values. there are 1769 cells filled with formulas.

I don't know how to be any clearer; I see (1) and (2) as the same arithamtic; they should equal the same when summed. The only thing I can think of, is that when I'm adding TotalBTURecoredPeriod = TotalBTURecoredPeriod + btu_calc, that it some how is only adding to a certain decimal place that I'm unaware of; essentially rounding.

So after 1769 calculations, vba gives me a highly rounded value vs the worksheet function, excel gives me.

and, the removing of "If BTU_This_Record > 0 Then" makes no difference to the situation.

 

[StephenCrump]

Thanks. Yes, the code you've shown is perfectly clear. The code not shown is the unknown.

Did you try my test code? That will dump the values of btu_calc into Excel. If you SUM() these in Excel you should get 140,395,826 , i.e. the same as the VBA calc?