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:

ValueType1

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!

ValueType2

If you change xlRangeValueDefault (10) to xlRangeValueXMLSpreadsheet (11) you get the unchanged textual numbers.

ValueType3

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?

 

 

 

Advertisements
This entry was posted in Formatting, VBA and tagged , . Bookmark the permalink.

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

  1. alanelston says:

    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

  2. If I recall correctly, a coworker used xlRangeValueMSPersistXML to build a Recordset from a ListObject.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s