CODEWITHSUNDEEP
excelvba
user3839756
user3839756

Reputation: 813

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

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?

Upvotes: 2

Views: 1202

Answers (3)

user3839756
user3839756

Reputation: 813

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.

Upvotes: 0

gtwebb
gtwebb

Reputation: 3011

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

Upvotes: 5

Brian
Brian

Reputation: 2108

Maybe this will work

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

Upvotes: 0

Related Questions