VBA array rounding currency numbers on its own

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
Import to array, export from array.

first part of my code... at the bolded msgbox, the data is unrounded, but in the cell it is rounded. Makes me think it is more an excel problem than VBA. The cells are formatted to accept up to 4 decimals, but it is always rounding to 2. can't figure out what is causing this rounding. none of the other inputs are being rounded, they don't normally have decimals (or more than 2 places) but I tested one and it correctly output the full decimal.

Thanks in advance, I really appreciate it.
Code:
Option Base 1
Sub processdailystats()
Application.ScreenUpdating = False
Sheets("Stats").Select

Dim x As Integer
Dim DataArray(30, 6) As Variant
Range("a5").Select

Dim numberofdays As Integer
numberofdays = 0
'count rows
For x = 1 To 365
    If ActiveCell.Value = "" Then
        x = 365
        ActiveCell.Offset(-1, 0).Select
    Else
        numberofdays = numberofdays + 1
        ActiveCell.Offset(1, 0).Select
    End If
Next x

Dim numberzero As Integer
numberzero = 0

'adds one row of data to the array
Dim ActualDays As Integer
ActualDays = 0
Dim DAY As Integer
For DAY = 1 To 30
If ActiveCell.Value = "" Then DAY = 30
ActualDays = ActualDays + 1
'date
x = 1
DataArray(DAY, x) = ActiveCell.Value
'DAU
ActiveCell.Offset(0, 8).Select
x = 2
DataArray(DAY, x) = ActiveCell.Value
'sessions
ActiveCell.Offset(0, 1).Select
x = 3
DataArray(DAY, x) = ActiveCell.Value
'new users
ActiveCell.Offset(0, 1).Select
x = 4
DataArray(DAY, x) = ActiveCell.Value
'rank1
ActiveCell.Offset(0, 10).Select
x = 5
DataArray(DAY, x) = ActiveCell.Value
'ARPDAU
ActiveCell.Offset(0, 2).Select
x = 6
If ActiveCell.Value = "-" Then DataArray(DAY, x) = numberzero Else DataArray(DAY, x) = ActiveCell.Value
ActiveCell.Offset(-1, -22).Select
Next DAY

'switch sheets in preparation to export data
Sheets("Daily Stats Review").Select
Range("b18").Select
'export time
For DAY = 1 To 7
'Date
ActiveCell.Value = DataArray(DAY, 1)
ActiveCell.Offset(0, 1).Select
'DAU
ActiveCell.Value = DataArray(DAY, 2)
ActiveCell.Offset(0, 1).Select
'sessions
ActiveCell.Value = DataArray(DAY, 3)
ActiveCell.Offset(0, 1).Select
'new users
ActiveCell.Value = DataArray(DAY, 4)
ActiveCell.Offset(0, 1).Select
'ARPDAU
[B]
MsgBox ("ARPDAU: " & DataArray(DAY, 6))
[/B]
ActiveCell.Value = DataArray(DAY, 6)
ActiveCell.Offset(0, 1).Select
'Average rank
ActiveCell.Value = DataArray(DAY, 5)
ActiveCell.Offset(-1, -5).Select
Next DAY
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
I know you said that the cell is formatted to show four decimal places, but just to double check; when you select the cell that is rounded, do you see the full four decimal places in the formula bar value?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
The value in the formula bar is not affected by the cell format. So that means the value placed in the cell by the code is already rounded. I can't tell from your code how that might work.

Cells F18:F12 are the rounded cells and they get the values from DataArray(DAY, 6). Is that correct?
 

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51

ADVERTISEMENT

Cells F18:F12 are the rounded cells and they get the values from DataArray(DAY, 6). Is that correct?

That is correct. It is also rounding some other stuff, all based on the same starting numbers. I go and do some math and get weekly/monthly averages, and these are rounded as well.

I've tried changing the numbers that are pulled into the array (it is a simple formula) but even putting normal numbers there doesn't seem to help. I've tried other things like shortening the number before the array loads it (from 10+ decimals to <5) but rounding is still occurring.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,785
Office Version
  1. 2010
Platform
  1. Windows
Have you set Precision as displayed in Excel Options?
 

Flammy

Board Regular
Joined
Apr 19, 2011
Messages
51
I should add I inherited this excel sheet from someone else so they might have changed some setting somewhere. They weren't doing anything complex (no VBA, only simple =AVERAGE sort of things) so I sort of doubt it was anything they did. Sorry for double posting, couldn't find the edit button.

EDIT: Seriously, the edit button disappears? You DON'T want us editing our first posts to include the solution, mods?
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,785
Office Version
  1. 2010
Platform
  1. Windows
Changing a post after people have responded is confusing for someone reading the thread cold. Just make a new post -- they're free.
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,402
I looks to me that if you read a cell that is formatted as currency into a variant-type array, the value in the array will have the precision of the original value. But when you write that value from the array, the written value will be rounded to two decimal currency.

A way around that is to use something like ths to write the values...
Code:
ActiveCell.Value = [COLOR="#FF0000"]CDbl([/COLOR]DataArray(DAY, 6)[COLOR="#FF0000"])[/COLOR]
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top