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!


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”
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom”/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
<Worksheet ss:Name=”Sheet1″>
<Table ss:ExpandedColumnCount=”1″ ss:ExpandedRowCount=”4″
<Cell><Data ss:Type=”String” x:Ticked=”1″>011</Data></Cell>
<Cell><Data ss:Type=”Number”>11</Data></Cell>
<Cell><Data ss:Type=”String” x:Ticked=”1″>11</Data></Cell>
<Cell><Data ss:Type=”String” x:Ticked=”1″>1-11</Data></Cell>

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:rs=”urn:schemas-microsoft-com:rowset” xmlns:z=”#RowsetSchema”>
<s:Schema id=”RowsetSchema”>
<s:ElementType name=”row” content=”eltOnly”>
<s:attribute type=”Col1″/>
<s:extends type=”rs:rowbase”/>
<s:AttributeType name=”Col1″ rs:name=” 011″>
<s:datatype dt:maxLength=”255″/>
<z:row Col1=”11″/>
<z:row Col1=”11″/>
<z:row Col1=”1-11″/>


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?




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

5 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

    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


  2. 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

      • alanelston says:

        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.

  3. moep says:

    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.

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