VBA array rounding currency numbers on its own

[Flammy]

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.

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

 

[shg]

If you read a cell formatted as currency into a variant using the Value property, it will get converted to data type Currency, which has exactly 4 decimals (it's a scaled integer). That wouldn't account for getting rounded to two places, though.

 

[AlphaFrog]

The two decimal rounding happens when you write from a Variant-Currency variable. I don't know why. But I tried it and it seems to happen.

 

[shg]

Try this:

Sub x()
    Dim v As Variant
    
    With Range("A1")
        .Formula = "=rand()"
        .NumberFormat = "$0.00"
        v = .Value
        MsgBox v & vbLf & .Value2
    End With
End Sub

 

[AlphaFrog]

...so read the values into the array using the .Value2 property.

[COLOR="#006400"]'date[/COLOR]
x = 1
DataArray(DAY, x) = ActiveCell.Value[COLOR="#FF0000"]2[/COLOR]

 

[shg]

Certainly, but I don't see how that resolves the rounding to two decimals mystery.

 

[AlphaFrog]

Try this...

Sub x()


    Dim v As Variant
    
    With Range("A1")
    
        .Formula = 0.5555
        .NumberFormat = "$0.00"
        v = .Value  'v is a type Variant-Currency
        
        .Offset(, 1).Value2 = v [COLOR=#008000]'B1 is not rounded to two decimals[/COLOR]
        .Offset(, 2).Value = v  [COLOR=#008000]'C1 is rounded to two decimals[/COLOR]


    End With
    
End Sub

 

[Flammy]

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...

ActiveCell.Value = [COLOR=#FF0000]CDbl([/COLOR]DataArray(DAY, 6)[COLOR=#FF0000])[/COLOR]

Thanks, works perfectly... that is a rather interesting 'feature'

Thanks for sticking with it, helped a lot.

 

[AlphaFrog]

Thanks, works perfectly... that is a rather interesting 'feature'

Thanks for sticking with it, helped a lot.

This could work as well...

ActiveCell.Value2 = DataArray(DAY, 6)

 

[shg]

Try this...

You taught me one, thank you!

 

[AlphaFrog]

@shg; you're welcome. me learnded one two.