If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Variable Defined as Double is entered in cell with only 2 decimal places
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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

#1
August 3rd 12, 12:23 AM posted to microsoft.public.excel.programming
 AJ Master external usenet poster Posts: 26
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

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
#2
August 3rd 12, 01:02 AM posted to microsoft.public.excel.programming
 joeu2004[_2_] external usenet poster Posts: 637
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

#3
August 3rd 12, 12:54 PM posted to microsoft.public.excel.programming
 Living the Dream external usenet poster Posts: 93
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
>
>
> 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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post appended a cell, but need to keep decimal places [email protected] Excel Discussion (Misc queries) 2 April 11th 07 10:31 PM integers entered are converted into decimal places e.g. 1 = 0.01 Alan H Excel Discussion (Misc queries) 1 February 19th 07 09:38 PM Round variable to 4 decimal places achidsey Excel Programming 4 September 25th 05 02:43 PM Entering numbers with variable decimal places. Jack Excel Worksheet Functions 8 February 2nd 05 04:35 AM variable as Currency two decimal places Max Bialystock Excel Programming 2 May 10th 04 10:48 PM

All times are GMT +1. The time now is 07:27 PM.