Parameterized Properties in C# and the mystery of Value2 in Excel

Article
09/06/2004

One of the mysteries in Excel customization code that I see written by C# developers is the use of the "Value2" property to set the value of a Range in Excel. Why do C# developers use this wacky Value2 property? Most VB programs you see written against Excel use the Value property instead of the Value2 property.

The documentation for Value2 says this about the difference between Value2 and Value: "The only difference between this property [Value2] and the Value property is that the Value2 property doesn’t use the Currency and Date data types. You can return values formatted with these data types as floating-point numbers by using the Double data type."

OK--fair enough. But I think most C# developers that use Value2 aren't really doing it because it returns Double for Currency and Date data types.

Let me also point out a second difference between Value2 and Value. Value is a parameterized property meaning it takes a parameter. The parameter it takes is an optional enumeration constant that can return the value of the range in either an XML recordset format (when you pass it the value Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueMSPersistXML) or Excel's XML format (when you pass it the value Microsoft.Office.Interop.Excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet).

For a very simple range that is set to the string "Hello" you get back the following from the Value property when you pass the XML recordset format enumeration:

And if you pass the Excel XML format enumeration when calling Excel's Value property, you get this:

<?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="https://www.w3.org/TR/REC-html40">
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1">
<Row>
<Cell><Data ss:Type="String">Hello</Data></Cell>
</Row>
</Table>
</Worksheet>
</Workbook>

So that describes the differences between Value and Value2--yet it doesn't really answer the original question. Why do C# programmers tend to gravitate towards Value2 and VB.NET programmers tend to gravitate towards Value?

My theory is that it has nothing to do with the different features of Value and Value2 and everything to do with the fact that Value is a parameterized property.

C# doesn't support calling parameterized properties as properties. So when you go to use the Excel object model and you are using intellisense on the Range object, the property called "Value" is nowhere to be seen. Instead, Value2 appears in intellisense where you would expect to see Value because Value2 is not parameterized, therefore C# developers tend to use Value2 instead. This is a good example of Intellisense driving your programming decisions :)

The Value property is actually callable from C# but as a method--the method is named "get_Value". So when you are looking for a property you may have seen used in a VBA or VB.NET program from C#, it is best to start typing the name you think it is called and if intellisense doesn't pop up the property, back space and type "get_" to see properties that have been renamed to methods because they are parameterized. Also, to set these parameterized properties in C# there are separate methods called "set_Value" where 'Value' is the name of the property you are going to set.

Here is some code that illustrates these ideas. Note that I use Type.Missing in this code to omit optional parameters I don't want to specify. VB.NET of course is better here and lets you omit them altogether--this is why the VB programmer uses Value happily, maybe never even realizing it has an optional parameter. In this code, I call several properties--Item, Range, and Value, all of which are parameterized and accessed via methods in C# :get_Item, get_Range, and get_Value. Also note that for a property that sets a value, I now have to call it like a method passing the value I want to set the property to as the last parameter to the set_* method. This is shown in the set_Value example below. You can also see that the code to set a range's value via Value2 is much more attractive in C# than the code that uses Value.

Excel.Worksheet sheet = (Excel.Worksheet)thisWorkbook.Sheets.get_Item(1);
Excel.Range range = (Excel.Range)sheet.get_Range("$A$5", Type.Missing);
Excel.Range range2 = (Excel.Range)sheet.get_Range("$A$6", Type.Missing);

range.set_Value(Type.Missing, "Long Hello");
range2.Value2 = "Short Hello";

MessageBox.Show((

string)range.get_Value(Excel.XlRangeValueDataType.xlRangeValueMSPersistXML));
MessageBox.Show((string)range.get_Value(Excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet));

For comparison (painful comparison if you are a C# developer), consider the same code written in VB.NET. There is a ton of nice syntactical sugar going on here. Note that I don't have to make any of the explicit casts I had to make in the C# code. Also note that I didn't have to specify the "get_Item" or even "Item" property at all when getting a sheet. This is because VB.NET supports the notion of default properties. The "Item" property is the default property on the Worksheets object, so I can omit it completely and just specify the parameter--the index of the sheet I want. Also note that I don't have to use any of the "get_" or "set_" syntax for parameterized properties since VB.NET supports them directly. I don't have to explicitly provide "Type.Missing" for parameters I'm omitting like the second parameter to the Range property of of Worksheet. And note that the syntax to call Value or Value2 is identical providing I don't care about specifying the optional parameter for Value.

Dim sheet As Excel.Worksheet = ThisWorkbook.Sheets(1)
Dim range As Excel.Range = sheet.Range("$A$5")
Dim range2 As Excel.Range = sheet.Range("$A$6")

range.Value = "Long Hello"
range2.Value2 = "Short Hello"

MsgBox(range.Value(Excel.XlRangeValueDataType.xlRangeValueMSPersistXML))
MsgBox(range.Value(Excel.XlRangeValueDataType.xlRangeValueXMLSpreadsheet))

Parameterized Properties in C# and the mystery of Value2 in Excel

Additional resources