Excel Range.Value(ValueType): What is this parameter?

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″&gt;
<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?