Variable Defined as Double is entered in cell with only 2 decimal places

A

AJ Master

All,

I'm having trouble trying to debug why variables defined as double and
used in calculations appear with only 2 decimal places when placed in
the cell. Here is some of the code.

..
..
..
Dim dblOpEx, dblCurrent, dblAddOnExp, dblPctInc As Double
Dim iBY, iAY, i, j As Double

iAY = ActiveSheet.Range("ad536")
iBY = ActiveSheet.Range("ad537")
dblOpEx = ActiveSheet.Range("ad538")
dblPctInc = ActiveSheet.Range("ad539")
dblCurrent = ActiveSheet.Range("ad540")
dblAddOnExp = ActiveSheet.Range("ad542")

For j = 0 To iRowCount + iEmptyCount - 1

If j < iEmptyCount Then
ActiveCell.Offset(j, 1).Value = 0

Else

If iBY > Year(ActiveCell.Offset(j, -6)) Then
ActiveCell.Offset(j, 1).Value = dblCurrent
Else
ActiveCell.Offset(j, 1).Value = (dblCurrent +
dblAddOnExp) * (1 + dblPctInc) ^ (Year(ActiveCell.Offset(j, -6)) -
iAY)
End If

End If

Next j

The cell value of dblcurrent is 9.025004, but when it enters it in
cells BI704 thru BI717 it rounds it to 9.03 and this causes
calculation errors. Any idea why this is happening?

Any thoughts would be appreciated.---AJ
 
J

joeu2004

AJ Master said:
I'm having trouble trying to debug why variables defined
as double and used in calculations appear with only 2
decimal places when placed in the cell. Here is some of
the code. [....]
If iBY > Year(ActiveCell.Offset(j, -6)) Then
ActiveCell.Offset(j, 1).Value = dblCurrent [....]
The cell value of dblcurrent is 9.025004, but when it
enters it in cells BI704 thru BI717 it rounds it to
9.03 and this causes calculation errors. Any idea why
this is happening?
Click to expand...

I suspect that the cells are formatted with 2 decimal places __and__ the
"Precision as displayed" (PAD) calculation option is set.

As a work-around, if not a solution, try the following:

ActiveCell.Offset(j, 1).NumberFormat = "General"
ActiveCell.Offset(j, 1) = dblCurrent

Better still, disable PAD, and use explicit rounding wherever appropriate.
I usually deprecate the use of PAD for the very problem (among others) that
you encountered: its effect is too pervasive.

For future note, when you have a problem that specific, it is prudent to
reduce the code to the minimum necessary to demonstrate the problem. It
should have been sufficient to write:

Sub testit()
Dim dblVal As Double
dblVal = 9.025004
Cells(1, 1) = dblVal
End Sub
 
L

Living the Dream

All,

I'm having trouble trying to debug why variables defined as double and
used in calculations appear with only 2 decimal places when placed in
the cell. Here is some of the code.

.
.
.
Dim dblOpEx, dblCurrent, dblAddOnExp, dblPctInc As Double
Dim iBY, iAY, i, j As Double

iAY = ActiveSheet.Range("ad536")
iBY = ActiveSheet.Range("ad537")
dblOpEx = ActiveSheet.Range("ad538")
dblPctInc = ActiveSheet.Range("ad539")
dblCurrent = ActiveSheet.Range("ad540")
dblAddOnExp = ActiveSheet.Range("ad542")

For j = 0 To iRowCount + iEmptyCount - 1

If j < iEmptyCount Then
ActiveCell.Offset(j, 1).Value = 0

Else

If iBY > Year(ActiveCell.Offset(j, -6)) Then
ActiveCell.Offset(j, 1).Value = dblCurrent
Else
ActiveCell.Offset(j, 1).Value = (dblCurrent +
dblAddOnExp) * (1 + dblPctInc) ^ (Year(ActiveCell.Offset(j, -6)) -
iAY)
End If

End If

Next j

The cell value of dblcurrent is 9.025004, but when it enters it in
cells BI704 thru BI717 it rounds it to 9.03 and this causes
calculation errors. Any idea why this is happening?

Any thoughts would be appreciated.---AJ
Click to expand...

Hi

You could try setting the format at the same time as setting the value.

eg

If iBY > Year(ActiveCell.Offset(j, -6)) Then
with ActiveCell
.Offset(j, 1).Value = dblCurrent
.Offset(j, 1).NumberFormat = "0.000000"
End with

Set the number of decimal places to whatever suits your need.

HTH
Mick
 
Top