Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last
|
|
|
|
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: 18823
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.
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: 18823
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.
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: 18823
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.
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: 18823
Joined: 2006-05-11
|
Are you allowed to use XLSX?
Andrei Smolin
Add-in Express Team Leader |
|
|
Posted 10 Apr, 2019 09:55:27
|
|
Top
|
|
|
Posts 1 - 10 of 11
First | Prev. | 1 2 | Next | Last
|
Forums
Topics
Search
Help
Login
Register