CalculateFull does not update the value in the cell accordingly

Add-in Express™ Support Service
That's what is more important than anything else

CalculateFull does not update the value in the cell accordingly
#VALUE! is not updated by the correct value, even if the excel function has no error 
Dirk




Posts: 73
Joined: 2017-10-05
Hello

I have a strange problem: regarding the CalculateFull function from the ExcelApplication.
1) I open an excel sheet that contains a formula. The value of that formula is "#VALUE!" which signal an error.
1a) The formula is correct in excel should be able to calculate the correct value. This seems to be an problem of excel.
1b) The formula is simple and uses only excel functions.
2) If I update the sheet using a macro which simply execute Application.CalculateFull the formula is updated and shows the correct value.
3) If I then call CalculateFull from inside our COM Add-In in Excel, which is trigger by a menu click or by an key press the value of the cell is reset to "#VALUE!". The error exists again.
4) If I call the macro again, the error has gone and the value of the formula is correct.
5) If I execute step 3) again the error is there again.
6) More strange: If I execute a COM-Add-In function from a macro:
ExcelApplication.COMAddIns.Item("<OurAddIn>").Object.ExecuteCalculateFull
The value in the cell is updated correctly. The #VALUE! error is removed.
The ExecuteCalculateFull function in our COM add-in does exactly the same as if it is trigger by the menu click or the key pressed and simply call CalculateFull. I removed all additional code from our event handler. It only execute CalculateFull.

My questions:
What is the difference between a macro that calls Application.CalculateFull and ((Excel._Application)ADXAddinModule.HostApplication).CalculateFull()?
We use the ADXAddinModule HostApplication property and cast it to Excel._Application.
Why is the value reset to #VALUE! in the cell even if the formula is correct and can be evaluated without error?

Further I should mention:
1) The file I use in the scenario is a XLS file
2) We use AddInExpres version 9.2.4638.0
3) We use Excel Office 365 version 16.0.11231.20122 32 Bit

No we cannot use a xlsx file.

Best regards
Dirk
Posted 10 Apr, 2019 01:58:27 Top
Dirk




Posts: 73
Joined: 2017-10-05
My idea for that problem:
Because the CalculateFull function inside our COM AddIn executes correctly if it is trigger by a macro I assume that the thread that activates the function is the problem.
In the case 3) CalculateFull is activate by a user click and case 6) the CalculateFull is activated by a macro.
What thread does execute the CalculateFull if it is activate by a click handler? Is there a proxy to the excel application object used by ADXAddinModule.HostApplication?
The generally CalculateFull works well, because other formula like =RAND() (which return a random number) shows a new number every time the our COM-AddIn execute CalculateFull
Also I tried different way to force excel to recalculate the formula: Toggle EnableCalculation false to true on a sheet has the same effect: the value #VALUE! is set to that cell which before was shown correctly
I checked the physical thread id using AppDomain.GetCurrentThreadId() and CurrentThread.ManagedThreadId. Both are the same in each case.
Posted 10 Apr, 2019 02:09:17 Top
Dirk




Posts: 73
Joined: 2017-10-05
If I use Ctrl-Alt-F9 to force excel to recalculate the value is reset to #VALUE! exactly as if our add-in calls CalculateFull.
Posted 10 Apr, 2019 02:31:55 Top
Andrei Smolin


Add-in Express team


Posts: 17239
Joined: 2006-05-11
Hello Dirk,

I can't answer these questions off the top of my head. How do I reproduce this issue? Can you send me your test project to the support email address; see {Add-in Express installation folder}\readme.txt.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Apr, 2019 05:13:36 Top
Dirk




Posts: 73
Joined: 2017-10-05
I've sent the email.
Thanks.
Posted 10 Apr, 2019 06:39:58 Top
Andrei Smolin


Add-in Express team


Posts: 17239
Joined: 2006-05-11
Hello Dirk,

Thank you for the file. I assume this is an issue in Excel itself. Select the cell with the error, click Formulas| Formula Auditing | Error Checking | Error Checking. In the Error Checking dialog box, click Show Calculation Steps and click the Evaluate button to see how SEARCH(text, range) returns #Value and IFERROR propagates this error (despite the goal of the IFERROR function).

I confirm the VBA macro clears the error and CTRL+ALT+F9 restores it.

If select the cell, click in the Formula bar (as if I'm going to edit the formula) and press Enter, that formula starts working as expected. I assume an equivalent of this is to set the Range.Formula property of that cell.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Apr, 2019 07:26:41 Top
Dirk




Posts: 73
Joined: 2017-10-05
OK. Thanks. I too think it's an Excel problem, but hoped you know a solution based on your excel experience.
Our problem is that there are thousands of formulas , each generate and similar to this. We cannot ask the user to change it manually.
I think it I also not very performance to find all error formula in all sheets and fix them finally by pragmatically update the formula property of the range.
So I'm looking for a trick to cheat excel to behave correctly. May be by setting a property in excel application object or workbook / worksheet object. Do you have any other idea?

Best regards
Dirk
Posted 10 Apr, 2019 08:16:07 Top
Andrei Smolin


Add-in Express team


Posts: 17239
Joined: 2006-05-11
Dirk,

In my Excel 2016, I save the workbook as XLSM, restart Excel, open the XLSM, press CTRL+ALT+F9 - the issue is gone.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Apr, 2019 09:16:25 Top
Dirk




Posts: 73
Joined: 2017-10-05
Ok. Thanks. We are unfortunately not allowed to change to format to xlsm. But, do not investigate further.
Posted 10 Apr, 2019 09:43:30 Top
Andrei Smolin


Add-in Express team


Posts: 17239
Joined: 2006-05-11
Are you allowed to use XLSX?

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 10 Apr, 2019 09:55:27 Top