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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Have you set Precision as displayed in Excel Options?
 
Upvote 0
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?
 
Upvote 0
Changing a post after people have responded is confusing for someone reading the thread cold. Just make a new post -- they're free.
 
Upvote 0
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]
 
Upvote 0

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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
Back
Top