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.

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

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…###

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

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