davisbrc
06-13-2008, 08:25 AM
I have a visual basic code that checks the excel spreadsheet to determine if the cells have the proper values. My spreadsheet is supposed to multiply a rate "X" times energy unit "Y" to produce a dollar value "Z". In the excel spreadsheet itself the numbers work out like they are supposed to. When I have visual basic check the math, visual basic truncates the rate "X" to a three (3) decimal number. I noticed when I have the rate "X" as a custom format in excel this happens, but when I change the cell format to General, the problem goes away.
The problem I am having is this:
This what the value should be
$0.09216 * 9800 = $903.168
but this is what VBA gives me
$0.0922 * 9800 = $903.56
I am new to coding VBA so be gentle because I know things can definately be improved on. I have also tried dimensioning my rates as double but it does not work.
Thanks in advance.
For Column = colstart To colstop
Do While numbldg <= numbldgtot
For bldg = bldgstart To bldgstop
If Column = kwtotcol Then
kwenergy1col = kwtotcol - 1
kwenergy2col = kwtotcol - 2
total = Worksheets("ECM Input Table").Cells(bldg, kwtotcol)
energy1 = Worksheets("ECM Input Table").Cells(bldg, kwenergy1col)
energy2 = Worksheets("ECM Input Table").Cells(bldg, kwenergy2col)
rate1 = Worksheets("ECM Input Table").Cells(raterow, kwenergy1col)
rate2 = Worksheets("ECM Input Table").Cells(raterow, kwenergy2col)
value1 = (rate1 * energy1) + (rate2 * energy2)
The problem I am having is this:
This what the value should be
$0.09216 * 9800 = $903.168
but this is what VBA gives me
$0.0922 * 9800 = $903.56
I am new to coding VBA so be gentle because I know things can definately be improved on. I have also tried dimensioning my rates as double but it does not work.
Thanks in advance.
For Column = colstart To colstop
Do While numbldg <= numbldgtot
For bldg = bldgstart To bldgstop
If Column = kwtotcol Then
kwenergy1col = kwtotcol - 1
kwenergy2col = kwtotcol - 2
total = Worksheets("ECM Input Table").Cells(bldg, kwtotcol)
energy1 = Worksheets("ECM Input Table").Cells(bldg, kwenergy1col)
energy2 = Worksheets("ECM Input Table").Cells(bldg, kwenergy2col)
rate1 = Worksheets("ECM Input Table").Cells(raterow, kwenergy1col)
rate2 = Worksheets("ECM Input Table").Cells(raterow, kwenergy2col)
value1 = (rate1 * energy1) + (rate2 * energy2)