Binding a C# variable to an excel cell

Question

I'm using COM interop in C# to display data into an Excel spreadsheet. Assume I have a data structure for 'Student Details' in C# and this is displayed appropriately in Excel.

Now, is it possible to change that value of a particular variable in C# when the corresponding cell in Excel is edited?

For example, as soon as the the marks of Student x is altered in the Excel sheet the variable corresponding to marks in C# must be altered.

If required VBA code could also be used! I don't know whether such a thing is possible, but it would be great if I could find an answer here.

Answer 1

Here's an example of how you can do that with an event handler:

Excel.DocEvents_ChangeEventHandler eventChange_CellData;
int someStudentsScore = 99;

private void Load_Workbook()
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlBook = xlApp.Workbooks.Add();
    Excel.Worksheet xlSheet = (Excel.Worksheet)xlBook.Sheets.Item[1];

    xlSheet.Name = "Student Data";

    // Your code for populating data somewhere here....

    eventChange_CellData = new Excel.DocEvents_ChangeEventHandler(DataChange);

    xlSheet.Change += eventChange_CellData;

}

private void DataChange(Excel.Range target)
{
    if(target.Address == "$A$1")
    {
        // If A1 is changed, then change variable.
        someStudentsScore = target.Value2;
    }
}

The VBA equivalent of this would be in the Worksheet's module:

Public someStudentsScore

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$1" Then
    '// You would probably have to create a wrapper of some kind to get your C#
    '// variable into the VBA routine(s)...
    someStudentsScore = Target.Value
End If

End Sub