How can I make my excel formulas calculate after values have been added programatically

2
votes

I have a .net program that updates values in an excel sheet programmatically using an OLEDB connection. my excel sheet has some simple SUM formulas that sum the values that are programmatically updated. The problem is that since the values are updated while the excel sheet is closed my formulas do not calculate when the sheet is opened. When I press calculate sheet button the formulas are still not executed (because excel does not recognize that the new values have been added, possibly?). The only way I can get them to execute is if I click on the cell holding the formula (As if to modify the formula) then press enter(making excel reevaluate the cells). I have calculation option set to automatic and my data types are correct (general for the formulas and number for the number values). Is there any way I can make the spreadsheet calculate the formulas when I open it?

3
excelvba
What library is being used in the .Net? Is it VB.Net or C#? Are the formulas showing as text-that-looks-like-a-formula? This is rogue behavior; there is something wrong with the .Net value updates. - user4039065
Im just updating the values via a sql statement with an OLeDb Connection. I am using C#. The formulas are not updated from the .net program, they were just added regularly in excel. - user3839756
I should also add that the values from the program appear in excel as the correct value and datatype - user3839756

3 Answers

4
votes

Try pressing ctrl+alt+f9 which should force a full calculation and not just a recalculation (like the calculate button does) that looks at cells that have changed.

More info on calculation material can be found here.

For vba I believe it is

Application.CalculateFull
0
votes

Maybe this will work

Private Sub Workbook_Open()
    Worksheets(1).Calculate
End Sub
0
votes

Just in case someone runs into this problem: To do a full recalculation in vba you can use Application.CalculateFullRebuild to force a full calculation.