CODEWITHSUNDEEP
c#excel
user1427887
user1427887

Reputation: 51

Excel c# Issue - formula not being recalculated

I am working on a document-level excel project. This is the third version.

One of the worksheet contains a list object (data fed from a database). Then there are cells contains formulas like the following

this.Range["F15"].Formula = "=COUNTIFS(HotList_ListObject[Product],E15,HotList_ListObject[Got_Interest],\"<>\")"

previously, I created the table by just drag-and-drop. then simply created a formula in the worksheets. and the formulas were recalculated as data in the listobject (HotList_ListObject above).

this time, I created the table by the code. Because Hotlist_ListObject is not on the worksheet until the application runs, I supply the formulas after creating the listobject by code.

I looked through the internet and did a couple of testings.

When I press F9, SHIFT+F9 and CTRL+ALT+F9, it doesn't recalculate.

Only when I press CTRL+SHIFT+ALT+F9, it recalculates

the explantion I found for CTRL+SHIFT+ALT+F9 is

Rechecks dependent formulas, and then calculates all formulas in all open workbooks, regardless of whether they have changed since last time or not.

It seems that because the formula is supplied in the worksheet by code, it does not create links to dependent formulas or something so it won't recalculate. (something like this, I suppose)

My users are not going to press the button every time they modify/add/delete records in the list.

what can I do to resolve this? How to tell Excel that the formulas need to recalculate every-time any thing changes in the listobject.

Thanks for any advice in advance!

Kind regards Mark

Upvotes: 0

Views: 1565

Answers (2)

Robert Ilbrink
Robert Ilbrink

Reputation: 7963

Did you check the options settings? This might be reset to manual by a macro (check with macro editor using Alt+F11 for xlCalculateManual and make sure the macro is not stopped before Application.Calculation = xlCalculationAutomatic is executed to set calaculations back to automatic. Turning calculations off is an often used method to speed macros up, but when a macro is exited half-way, this manual calculation setting will stay.

Here is the manual setting.

enter image description here

Upvotes: 0

Jeremy Thompson
Jeremy Thompson

Reputation: 65692

Does setting the Calculation to Manual:

xlApp.Calculation = XlCalculation.xlCalculationManual;
xlApp.ScreenUpdating = false;
xlApp.DisplayAlerts = false;
xlApp.UserControl = false;
xlApp.EnableEvents = false;

Then populating your Hotlist_ListObject (that is not on the worksheet until the application runs and) then set calculation to Automatic:

xlApp.Calculation = XlCalculation.xlCalculationAutomatic;
xlApp.ScreenUpdating = true;
xlApp.DisplayAlerts = true;
xlApp.UserControl = true;
xlApp.EnableEvents = true;

Resolve the problem? If not could you please post some bare bones code to illustrate the issue.

Upvotes: 1

Related Questions