Whilst visiting Microsoft Research in Cambridge I was chatting to fellow MVP Roger Govier. He mentioned that he was using this strange parameter to the Range.Value (ValueType) method, which I had been meaning to look at for a long time since it was added in Excel 2007.
So I promised him a blog post!
RangeValueDataType
The first thing to do is look at Excel VBA Help.
xlRangeValueDefault | 10 | Default. If the specified Range object is empty, returns the value Empty (use the IsEmpty function to test for this case). If the Range object contains more than one cell, returns an array of values (use the IsArray function to test for this case). |
xlRangeValueMSPersistXML | 12 | Returns the recordset representation of the specified Range object in an XML format. |
xlRangeValueXMLSpreadsheet | 11 | Returns the values, formatting, formulas, and names of the specified Range object in the XML Spreadsheet format. |
Unfortunately that did not really leave me any the wiser. So lets try looking at some values using the VBE Immediate and Locals windows.
I am using some very simple data, a mixture of strings and numbers:
And some very simple code
Sub CopyWithFormat2() Dim var As Variant Dim j As Long var = Range("A8:A11").Value(xlRangeValueDefault) Range("C8:C11").Value(xlRangeValueDefault) = var End Sub
But this changes all textual numbers to real numbers and dates!
If you change xlRangeValueDefault (10) to xlRangeValueXMLSpreadsheet (11) you get the unchanged textual numbers.
Looking at var in the immediate window this is what it contains:
<?xml version=”1.0″?>
<?mso-application progid=”Excel.Sheet”?>
<Workbook xmlns=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:o=”urn:schemas-microsoft-com:office:office”
xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:ss=”urn:schemas-microsoft-com:office:spreadsheet”
xmlns:html=”http://www.w3.org/TR/REC-html40″>
<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom”/>
<Borders/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name=”Sheet1″>
<Table ss:ExpandedColumnCount=”1″ ss:ExpandedRowCount=”4″
ss:DefaultRowHeight=”15″>
<Row>
<Cell><Data ss:Type=”String” x:Ticked=”1″>011</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=”Number”>11</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=”String” x:Ticked=”1″>11</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type=”String” x:Ticked=”1″>1-11</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>
So this is fine unless you want to do some processing of the data in var!
If you use xlRangeValueMSPersistXML (12) then you get an “Object doesn’t support this action” error message when you try to put var back on the worksheet.
And the immediate window shows this as the contents of var: looks like some kind of pivot cache format.
<xml xmlns:x=”urn:schemas-microsoft-com:office:excel”
xmlns:dt=”uuid:C2F41010-65B3-11d1-A29F-00AA00C14882″
xmlns:s=”uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882″
xmlns:rs=”urn:schemas-microsoft-com:rowset” xmlns:z=”#RowsetSchema”>
<x:PivotCache>
<x:CacheIndex>1</x:CacheIndex>
<s:Schema id=”RowsetSchema”>
<s:ElementType name=”row” content=”eltOnly”>
<s:attribute type=”Col1″/>
<s:extends type=”rs:rowbase”/>
</s:ElementType>
<s:AttributeType name=”Col1″ rs:name=” 011″>
<s:datatype dt:maxLength=”255″/>
</s:AttributeType>
</s:Schema>
<rs:data>
<z:row Col1=”11″/>
<z:row Col1=”11″/>
<z:row Col1=”1-11″/>
</rs:data>
</x:PivotCache>
</xml>
Conclusion
Well: I can’t actually see a scenario in which I could find a use for this!
Have you ever used this in anger?
Any ideas for scenarios where it would be useful?
Hi Charles Williams
I too, do not know of any use of this. I too would be interested in any . So I am glad you did a Blog on this, Thanks.
I actually came across this when trying to teach myself about , and get a good understanding of the holding, retrieving and “filling” of the Range object “values” Properties.
I suppose as a string can be returned full of information, then it gives an alternative was to get at that information as opposed to obtaining the same through the Range object properties which reveal / return that information. I cannot think immediately of an application.###
But it is possible that in some code you might find it convenient to manipulate that string with a simple , but tedious string manipulation code, which would pull out the various information , such as the cell formatting contained in that second (xlRangeValueXMLSpreadsheet (11) ) string
The “RangeValueDataType” was one thing that lead me to think that it might be better to consider the .Value “values” Property , as a Method, rather than a Property. .
I had already been thinking of it as a Method as I found it was more universally usable when using .Value for applying / assigning different “values” to a Range object. – It seemed that VBA was guessing better if I used it for applying the other “values” Properties. ( When using the other “values” Properties, I had to be more precise. ) It suggested to me more “intelligence” involved when using .Value as apposes to other “values” Properties.
Because I then noticed that .Value had these 3 arguments, it gave more weight to my suggestion that .Value could be considered a Method. This was because experienced Computer people had advised me that the taking of arguments could be considered as defining such an entity as a Method rather than a Property.
It is interesting that you can use two of the argument options to apply / assign to the Range object. I had not investigated that. I am guessing the different results are coming from different available information in those revealed strings of information. This is probably worth some more investigation. But at first glance it seems reasonable that xlRangeValueXMLSpreadsheet (11) retains the format better as that information is present.
Noe that i think about it, I had an annoying occurrence of losing format with ,Value, ( which would have defaulted to the xlRangeValueDefault (10) ),
In light of your Blog, and thinking further, I will re investigate and see if I am able to get over that with some utilising of
.Value(RangeValueDataType:=xlRangeValueXMLSpreadsheet)
If I get anywhere I will report back , as that would indeed be a scenarios where it would be useful…###
Thanks again for bringing this one up
Alan
https://www.excelforum.com/development-testing-forum/1154829-collection-stuff-of-codes-for-other-threads-no-reply-needed-8.html#post4519378
If I recall correctly, a coworker used xlRangeValueMSPersistXML to build a Recordset from a ListObject.
Function CopyToRecordset(rng As Range) As ADODB.Recordset ‘ range to recordset example
Dim xlXML As Object
Set CopyToRecordset = CreateObject(“ADODB.Recordset”)
Set xlXML = CreateObject(“MSXML2.DOMDocument”)
xlXML.LoadXML rng.Value(xlRangeValueMSPersistXML) ‘ CopyToXML
CopyToRecordset.Open xlXML
End Function
Hi Patrick,
That’s great, I was interested in such an example.
( I posted a short reply here to discus, but it didn’t not come through ??
Maybe this reply will work )
I was recently doing some unusual things with ListObject and MS Queries, – just inefficient playing around while learning aspects of getting data from external sources into an Excel File. There I did a few things a bit differently to the more conventional ADO connection/recordset approach, which I am just learning about now.
Your function gives me something else to experiment with.
I guess if I use the function to turn the Range of my VBA driven MS query produced ListObject Table into a record set, then I am really going about things in a crazy roundabout way.
But interesting from a learning point of view.
🙂
Alan
The returning XML contain next to the values .. all applied colors / styles.
I use this in order to (very) fast collect all dinstinct styles (interior or font colors) of a range.
This is much faster in comparison with a “real” range enum.
Yes – some xml “string” – attribute conversion is required .. but this is fixed and documented.
xlRangeValueXMLSpreadsheet is interesing.
I can use that to compare 2 ranges on the same sheet. If anything is differnt this will be flagged as false. This includes the column width, font color etc. Not sure how useful this in in the big scheme of things.
What possibly is more interesting is that I can export a range in xml format and then read that range back into either the same workbook or a different rworkbook.
For example take the following simple funcitons.
Public Function rangeXMLSave(ByVal dataRange1 As Range) As String
On Error GoTo errhandler
Dim oXMl As MSXML2.DOMDocument
Set oXMl = New MSXML2.DOMDocument
oXMl.LoadXML dataRange1.Value(xlRangeValueXMLSpreadsheet)
oXMl.Save “c:\temp\textrangexml.xml”
exitHere:
Set oXMl = Nothing
rangeXMLSave = “Saved @” & Format(Now(), “hh:mm:ss”)
Exit Function
errhandler:
Debug.Print Application.Caller.Parent.Name & “:” & Application.Caller.Address & ” ” & Err.Descripti
End Function
Public Function loadRangeFromXML(fileName As String, ByRef dataRange As Range) As Variant
On Error GoTo errhandler
Dim oXMl As MSXML2.DOMDocument
Set oXMl = New MSXML2.DOMDocument
oXMl.Load fileName
dataRange.Value(xlRangeValueXMLSpreadsheet) = oXMl.XML
exitHere:
Set oXMl = Nothing
Exit Function
errhandler:
Debug.Print Application.Caller.Parent.Name & “:” & Application.Caller.Address & ” ” & Err.Description
End Function
The loadRangeFromXML function can reload the data that is exported to any cells in a workbook. The font, cell colors and other properties are imported. The formulas are also imported, but only if the cells that are referenced are within the range that is export. If a formula, in the exported range, references the cell outside of that range only the resulting value is exported and then omported.
I just tried it as an alternative to getting a HTML table to paste in
_(i) a Forum Post accepting HTML
_(ii) an automation of Email sending which uses the HTML as main body option
I couldn’t seem to figure out how to convert the XML file into anything useful in HTML
On-line converters give me all sorts of files, none of which end up looking anything like a table when they are pasted in a Forum editor or opened, for example with Word…
I expect there is a lot more to it than some simple XML to HTML conversion.
I thought if such a thing was possible, then that could be useful.
I currently use the “save range as .htm file” way in Excel initially, then usually fiddle the HTML file in a text editor a bit manually before I come up with a File that comes out similar in both (i) and (ii).
( Some forums have tools to do this as another alternative for (i) )
Alan
I have used this in the past when dealing with precision over 15 dp, it helped.
When using Excel.Interop via VSTO/COM, each call to the Object Model requires marshalling, which is (extremely) slow: several milliseconds per call. If you run a For Each on a Range of cells, you easily end up with iterations taking several seconds to run; not ideal. Extracting the XML allows to not only get the array of values (which is already possible via the same property), but also the array of data types, formattings, and most importantly the array of formulas. Working with XML is easy nowadays, plenty of libraries available, including an official .Net SDK maintained by Microsoft.
Thanks Ama, I had not spotted that you could get an array of Formats – that could be very useful. You can already get an array of formulas using Var=Range.Formula etc.