Range.Value produces different results in VBA and C#

Question

I have noticed that Range.Value produces different results in VBA and C# for date values.

For example if a cell has value 5/13/1988 with NumberFormat d/m/yyyy, in VBA Range.Value will return "5/13/1988" whereas in C# it returns "5/13/1988 12:00:00 AM"

Range.Value2 is the same(32276) in both languages.

Does anyone know why VBA and C# are producing inconsistent results in this situation?

Note that I am aware that I could use a combination of Range.Value2 and Range.NumberFormat and then format the value in C#, but I am interested why the behavior is inconsistent.

Answer 1

Simple. In the Excel Interop (you ref in your VSTO project) MS have obviously mapped Date cell values to the .Net DateTime data type.

Best choice for them, you wouldn't want to use like a Date .Net data type (remember VB classic only has Date data types not DateTime).

See how excel handles data under the hood here: https://stackoverflow.com/a/13983731

Answer 2

VB.Net and C# Handle it differently.

Excel 2013

Sub Sample()
    MsgBox Sheet1.Range("A1").Value
End Sub

---

VB.Net 2013

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    '~~> Opens an existing Workbook. Change path and filename as applicable
    xlWorkBook = xlApp.Workbooks.Open("C:\Users\Siddharth\Desktop\Delete Later\Sample.xlsx")

    '~~> Display Excel
    xlApp.Visible = True
    xlWorkSheet = xlWorkBook.Sheets("Sheet1")

    MessageBox.Show(xlWorkSheet.Range("A1").Value)

    '
    '~~> Rest of the code
    '
End Sub

---

C# 2013

C# Unfortunately handles it like you mentioned.

You can do this to get the desired result

    private void button1_Click(object sender, EventArgs e)
    {
        Microsoft.Office.Interop.Excel.Application xlexcel;
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;

        object misValue = System.Reflection.Missing.Value;
        xlexcel = new Excel.Application();

        xlexcel.Visible=true ;

        xlWorkBook = xlexcel.Workbooks.Open(
                    "C:\\Users\\Siddharth\\Desktop\\Delete Later\\Sample.xlsx", 
                    0, true, 5, "", "", true, 
                    Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 
                    "\t", false, false, 0, true, 1, 0);

        // Set Sheet 1 as the sheet you want to work with
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        String cellvalue = xlWorkSheet.Cells[1, 1].Value.ToString("MM/dd/yyyy", 
                           CultureInfo.InvariantCulture);

        MessageBox.Show(cellvalue);

        //
        //~~> Rest of the code
        //
    }

Answer 3

using System.Threading;
using System.Globalization;
// Put the following code before InitializeComponent()
// Sets the culture to US English
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US"); 
// Sets the UI culture too
Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");