Pulling a currency variable through my arrays drops 3rd decimal?

[ker_01]

My code is hundreds or maybe even 1000+ rows, so I won't waste bandwidth by
posting it all; I'll give a brief description and then post relevant code. I
appreciate any suggestions you might have. Programming in XL2003/XP but
workbook will also be used in XL2007/XP.

I have some currency values in Sheet16, including some that are to the third
decimal ($.125, $.150, $.175, etc.). I pull those values into an small array
(range?) to process, then paste them into a second (large)array/range, then
finally paste the large array/range back to fill an entire worksheet.
Somewhere in the translation, I lose the last decimal I need to figure
out why, and fix it so that later calculations based on these numbers will be
accurate.

I appreciate any review/advice/corrections-
Thanks,
Keith


'Create the big array/range so I can later fill it:
Dim GraphDataArray As Variant
Sheet11.Activate
Sheet11.Range("A1:Z65000").Select
Selection.Clear 'start with a blank array/range
GraphDataArray = Sheet11.Range("A1:Z1000").Value

{snip}

'Grab the currency values
Dim SubTemp1 As Range
Set SubTemp1 = Sheet16.Range(TempUseCol & "10:" & TempUseCol & "14")

{snip}
'example of pushing one of these currency values into the large array
GraphDataArray(VXD + 24, 16 + 1) = SubTemp1(1)

{snip}

'Push the grid back out to the worksheet:
Sheet11.Range("A1:Z1000").Value = GraphDataArray

 

[ker_01]

Some follow-up testing; using debug.print, it appears that the 3rd decimal is
retained even up to placing it in the large array;
Debug.Print GraphDataArray(VXD + 25, 17 + EachThresh) just before pasting
the values back into Sheet16 shows the expected values.

So, that narrows down the location of the problem (pasting the array back
into Sheet16), but I'm still at a loss as to why it would drop the extra
decimals.

For what it's worth, the large array (GraphDataArray) contains a variety of
different formats- currency, numbers, numbers with decimals, text, formulas...

Thanks,
Keith

 

[ker_01]

Yet even more data:

The source data being pulled into SubTemp1 is most often low percents (2%,
4.5%) and those are showing up correctly (.02, .045). Only a few of my
"records" (which is what gets pulled into SubTemp1) use currency instead of a
percent, and those are the ones being forcibly rounded to 2 decimal places.

Set SubTemp1 =... is used in a loop, so in some cases it will be filled with
the percents, and in other cases it will be filled with the currency values.
I haven't specified a format for SubTemp1, so I would expect it to just pull
as general, thereby pulling well more than 3 decimal places.

Still welcoming advice,
Keith

 

[Charles Williams]

You need to use .value2 rather than the default .value

GraphDataArray = Sheet11.Range("A1:Z1000").Value2

If you use .Value there is an implicit under-the-covers conversion to VBA
Currency datatype which causes the problem

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com