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

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

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

"AJ Master" wrote:
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?

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

Variable Defined as Double is entered in cell with only 2 decimalplaces
 

On 3/08/2012 9:23 AM, AJ Master wrote:
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


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