If you paste variant array to Excel range (bulk paste), Excel will see date values from variant array as integers
If Sheet1.Range("A1:A10") contains dates, then following VBA code will not produce expected results…
Dim vArray as Variant
vArray = Sheet1.Range("A1:A10").Value
Sheet2.Range("A1:A10").Value = vArray
After you run ReadAndPasteArray(), Sheet2 will contain integer values instead of dates.
This problem exists only on Excel 2010 (32bit and 64bit).
I could manually check for data type and format each destination cell, but the purpose of pasting array to a range is to significantly increase the speed of data transfer between VBA and Worksheets.
The Past command copies the values AND the formatting from one range to another. It is the formatting which says to display the values as dates.
You code isn't actually using the Paste command. It is only assigning the values.
If you use the Copy and Paste commands you would get both values and formatting together.
Simon Jones http://pcpro.co.uk
Thank you for the reply. However, I disagree with you.It is not the point to copy/paste from a range to a range. The idea is to build array in memory and paste it to a Range with correct datatypes.
In Excel versions starting from 2000 up to 2007 , when you paste array that contains date values, destination range would contain date values.
The code snipset below would display correct formatting on Excel 2000-2007, but it doesn’t on Excel 2010 .
Sub CreateArrayAndPaste() Dim vArray As Variant ReDim vArray(1 To 5, 1 To 3) As Variant Dim MyCurr As Currency MyCurr = 100.2 Dim lCounter As Long For lCounter = 1 To 5 vArray(lCounter, 1) = Now() ' datatype is Date vArray(lCounter, 2) = True ' datatype is boolean vArray(lCounter, 3) = MyCurr ' datatype is currency Next Sheet1.Range("A1:C5").Value = vArray End Sub
The goal is to build array quickly in memory and then paste entire array in order to avoid multiple referencing of Excels Range object.
This bulk operation is here for speed.
However, on Excel 2010 dataype is not preserved.
But you are still not using the Paste command. You are explicitly assigning the VALUES to cells.
It is the FORMATTING of a cell which says that its value should be FORMATTED as a Date. The underlying value is just the number of days since 01/01/1900 - IE a number. That is what you are seeing.
If you want a number to be seen as a date you have to change the FORMAT of the cell.
The Paste command sets both the value and the format. You are not using the Paste command therefore you have to set the format yourself.
Unfortunately Excel's support for the Clipboard in VBA is very limited. While you can find out the type of data in the clipboard there is no way to clear or set the data in it. If you have access to Visual Basic or Visual Studio you can use that to add your data to the Clipboard in the right format to then be able to use Excel's Paste method to set the values and formatting in one operation.
Alternatively, see here http://www.cpearson.com/excel/Clipboard.aspx for details of using the Clipboard support from the MSForms library in Excel and a set of functions for manipulating the Clipboard.
However, if you know, or can find out, the datatypes in your array you may be better taking the hit and setting the formatting in code.
Simon Jones http://pcpro.co.uk
- Edited by Simon Jones [MSDL] Saturday, June 26, 2010 2:48 PM extra info
Thank you for the explanation, but you are missing two key points here:1. The previous VBA code worked in Excel 2000 – 2007 (you can test it, and Excel will paste formatting along with a values). It is broken in Excel 2010 . Many Excel add-ins and VBA code that used this feature will not work correctly in Excel 2010.
I am trying to elevate this problem as a bug in Excel 2010, so Microsoft could fix it in the future, or offer a workaround.
2. Paste option, as you suggested, will not work. You can Paste from a range to a range, but you cannot build array in memory and use Paste method of Range object.
I disagree with some of your statements.
First I do not have to (at least I did not have to) use a Paste method to paste number formats. I also did not have to use clipboard to paste data.
SomeRange.Value = SomeVariantArray , performed two operations (until Excel 2010):
1. Assign values to each cell in SomeRange
2. Set formatting for each cell in SomeRange based on data types in variant array. (please let me know if you disagree )
#2 is very important, and it is the essence of this bulk operation. An element in Variant array doesn't contain only a value. It contains information about datatype. Previous Excel versions used that information to set correct formatting.
Again, I am trying to elevate this as a bug in Excel 2010, because it worked in previous Excel versions.
Regarding your comments:
"It is the FORMATTING of a cell which says that its value should be FORMATTED as a Date". I agree.
"You are explicitly assigning the VALUES to cells.". I disagree. I am explicitly assigning values and implicitly number formats based on data types stored in array.
Right, now you are being more explict but still confusing things by talking about "PASTING" when you are doing no such thing.
Yes, I have tested your code for assigning a Variant array to a range in Excel 2003 and I can confirm that it does set the data format as well as the values.
The same code run in Excel 2010 only sets the values, not the format. This is a change in the behaviour and could be considered a bug. Microsoft may have intentionally changed the behaviour and so the outcome might be "by design" and therefore not a bug. I can't find any help text or blog postings which make me think this is "by design" but nor can I find any posting which says it is a bug.
I would suggest you raise a support incident about this problem so Microsoft engineers can see it for themselves and offer a fix or workaround.
Simon Jones http://pcpro.co.uk
Excel considers that the dates must be in the american format.
Two solutions (thanks to Bill Manville and Rory Archibald) :
Use value2 instead of value when manipulating dates in an array
or impose a date format which is unambigous
Tblo(2)= Format(range("A2").Value, "dd-mmm-yyyy")
Misange - www.excelabo.net
- Proposed as answer by Misange Friday, January 28, 2011 1:00 PM
There was an issue raised over here:
This issue has been closed as "won't fix"... they have more important things to see to...
could anyone please suggest a workaround....