Excel Freeze when UDF is kept running for a long time

[Arjun Shetty]

Hi,

 Excel freezes sometimes after having used the UDFs in the Addin.

My issue is similar to the issue mentioned in this post https://groups.google.com/forum/#!topic/exceldna/MUw98OI2GcY

I am not able to reproduce the issue by  locking my screen when an Excel instance is running my ExcelDna add-in  as described in

https://groups.google.com/forum/?fromgroups#!searchin/exceldna/freeze/exceldna/7Lu2rff_bc4/QaOdGTFdSSoJ

Any update on this issue? If anyone faced this issue in version 0.30 please advise.

 Other details.

In my  Addin I have used Excel-DNA version 0.30 .

I have couple of methods with signature

[ExcelFunction(IsHidden = false, IsThreadSafe = true)]

public static object method1(string inputfield1, string inputfield1)

where I return a single value(string or integer)

and a method with signature

[ExcelFunction(IsHidden = false)]

public static object method2(string securityIdentifier, object requiredfields)

where I return a array.

Multithreading is allowed for method1 and not for method2.

 In method1 i do not try to access  ExcelDnaUtil.Application inside the UDF which can cause exception since multi-thread calculation is allowed.

Instead I queue the task using ExcelAsyncUtil.QueueAsMacro(MyMacro) and in the macro MyMacro use ExcelDnaUtil.Application to update the statusbar.

 In method2 I access the  ExcelDnaUtil.Application inside the UDF as it is allowed since it is not marked as threadsafe.

I have a ribbon button through which i force recalculation for all the formulas in the sheet.

 Apart from this i use

ExcelReference caller = XlCall.Excel(XlCall.xlfCaller) as ExcelReference;

 I am not sure if I am doing something wrong that is causing excel to freeze or whether it is still a bug in Excel DNA version I am using.

Please let me know if any ideas.

 

Thanks in Advance,
Arjun

[Govert van Drimmelen]

Hi Arjun,

You certainly can't use the Excel COM API (ExcelDnaUtil.Application) in a multi-threaded calculation context.

In addition, I think your assumption that access to the COM API from a non-multithreaded calculation context is safe might be wrong - I'm not sure you can assume it is safe even though it seems to work sometimes, possibly depending on what calls you make. I've had conflicting reports about this and nothing specific I can point to, but I normally advise against it. As far as I know, Excel does not expect the COM API to be called from a .xll UDF.

As an experiment, you might investigate whether adding an IsMacrotType=true flag to the function makes any difference. I don't know.

I'd suggest you either replace the COM API calls in your UDF with calls to the C API, or use the ExcelAsyncUtil.QueueAsMacro to run the code in a macro context where use of the COM API is supported. Sadly, the Excel team has not made all features of the COM API are available via the C API, so we're sometimes a bit stuck since the async macro will only run after the calculation completes.

Regards,

Govert

[Arjun Shetty]

Hi Govert,

 

In my Addin I use the ExcelAsyncUtil.QueueAsMacro to run the code in a macro context and use the COM API as you have suggested below. I do not access the  COM API  in any other way apart from this.

 

“I'd suggest you either replace the COM API calls in your UDF with calls to the C API, or use the ExcelAsyncUtil.QueueAsMacro to run the code in a macro context where use of the COM API is supported. Sadly, the Excel team has not made all features of the COM API are available via the C API, so we're sometimes a bit stuck since the async macro will only run after the calculation completes.”

 

The issue I get sometimes(Not easily reproducible.The excel freezes : Not able to select a cell in the sheet, cursor appears as + , happens when the sheet is kept open for a longer duration ) is that Excel does not respond

after having used the udfs in the Addin.

 

There is no exception thrown or any error notification when this issue occurs.

Please let me know if I can find the reason why this issue occurs.

Regards,

Arjun

[Govert van Drimmelen]

Hi Arjun,

Do I understand correctly that you have modified your add-in from your initial post, so that it is no longer true that:

  "In method2 I access the  ExcelDnaUtil.Application inside the UDF as it is allowed since it is not marked as threadsafe." ?

And now you still experience this problem with the modified add-in?

If so, there is not much I can suggest apart from trying to make a simpler case that is reproducible.

I have not had other reports of problems related to the ExcelAsyncUtil.QueueAsMacro mechanism, but I also don't know how widely it is used.

You could try to attach a debugger to a frozen Excel instance, and try to see if that shows anything interesting.

Regards,

Govert

[Arjun Shetty]

Hi Govert,

 

Sorry for confusing you by saying that i am accessing  COM API only through ExcelAsyncUtil.QueueAsMacro as in method1.

Basically UDFs of type method2 is not as frequently used in testing as UDFs of type method1 and

the Excel freeze issue was noticed after having used the UDFs of type method1.

 

I have now modified the add-in(removed calls to COM Api in method2) from my initial post as per your suggestion. I am continuing to test the addin to see if the issue still exists.

Since the issue was not easily reproducible at the first place , i will be testing it further.

 

I would like to replace two of the COM API calls with calls to the C API.

 

Please do let me know if you know how the below can be achieved by making calls to the C API.

 

_excelApplication = (Microsoft.Office.Interop.Excel.Application)ExcelDnaUtil.Application;

_excelApplication.ActiveWorkbook.Application.Cursor = Microsoft.Office.Interop.Excel.XlMousePointer.xlWait;

_excelApplication.StatusBar = "Requesting data...";

 

Thanks,

Arjun

On Friday, July 19, 2013 2:48:58 PM UTC+5:30, Arjun Shetty wrote:

[Arjun Shetty]

Hi Govert,

I faced Excel freeze issue recently with 2 different scenarios and  both these issues where resolved by adding  IsMacrotType=true flag to the function.

Scenarios

1)Excel freeze was happening when a range of values was passed as input to the formula

   Eg:When  =MyMethod(a1,"outputfFeild",TODAY()-7 )  is called instead of =MyMethod("inputValue","outputfFeild" , "1/20/2014")  .

   This issue was happening consistently and also across all the machines on which i tested.

 

2)

a.  Close all Internet Explorer  windows.

b.  Open up Excel and type in any formula. The formula makes a webrequest and fetches the data.

c.  Open a IE window and open any website and then after 4 -5 seconds click the opened excel instance.

d.  Excel will freeze.

This issue does not happens on all the test machines (happens in 2 machines out of 4 machines) but happens consistently in the machines in which this issue occurs.

By trail and error, i found that after  adding IsMacrotType=true flag to the function this issue gets resolved :)

 

Would like to know why this issue happens in some machines but not in other . Also would like to know what the flag IsMacroType is meant for ?

 

Regards,

Arjun

 

 

 

 

 

[Govert van Drimmelen]

Hi Arjun,

The IsMacroType=true option has only one effect - when Excel-DNA registers the function with Excel using the xlfRegister call it is registered with a # suffix for the pxTypeText parameter. See the xlfRegister documentation here: http://msdn.microsoft.com/en-us/library/office/bb687900.aspx . This page gives the following details:

Registering Worksheet Functions as Macro Sheet Equivalents (Handling Uncalculated Cells)

Placing a # character after the last parameter code in pxTypeText gives the function the same calling permissions as functions on a macro sheet. These are as follows:

• The function can retrieve the values of cells that have not yet been calculated in this recalculation cycle.
  
• The function can call any of the XLM information (Class 2) functions, for example, xlfGetCell.
  
• If the number sign (#) is not present: evaluating an uncalculated cell results in an xlretUncalced error, and the current function is called again once the cell has been calculated; calling any XLM information function other than xlfCaller results in an xlretInvXlfn error.

Setting IsMacroType=true together with some parameter marked as [ExcelArgument(AllowReference=true)] would cause Excel to handle the function as volatile.

I usually recommend that users don't mark a function as IsMacroType=true unless that have a specific reason to do so - e.g. wanting to access those information functions.

I'm not sure how this relates to the behaviour you report - it's hard to tell without knowing more about your functions. Certainly the Excel freezing is not expected or something I've heard of generally. I presume it has something to do with the implementation of your functions and how they interact with Excel.

If you can post a small example in a .dna file that reproduces the issue you report, I'd be happy to have a closer look.

Regards,

Govert

[Arjun Shetty]

Hi Govert,

 

Thank you for the quick response.

I will be sending a sample .dna file after i am able to reproduce this issue on a couple of machines.

 

Steps to reproduce:

a. Close all Internet Explorer windows.

b. Open up Excel and type in any formula. The formula makes a webrequest and fetches the data.

c. Open a IE window and open any website and then after 4 -5 seconds click the opened excel instance.

d. Excel will freeze.

 

This isssue does not happen in all machines. I tested in 4 machines with Excel 2010/2013 and Windows XP/Windows7 . The issue happens only on 2 machines.

So probably you too may have to test it on couple of machines to reproduce the issue.

 

I have used the  IsMacroType=true  for the sole reason of fixing the Excel freeze issue. I tried this since you had suggested me to try this in the 1st reply to the thread.

 

Regards,

Arjun