Is there a way to stop the execution of a UDF once it has been called?

[Arjun Shetty]

Hi,

 

I have developed a Excel Add-in using Excel-DNA version 0.30 with a couple of User Defined functions(UDF).

In all the UDFs i basically fetch data from a webservice and populate the results.

In some of the UDFs the data fetched is large and hence i make this request through a macro using ExcelAsyncUtil.QueueAsMacro rather

than make the web request using HttpWebRequest from the UDF.

 

My question : "Is there a way to stop the execution of a UDF once it has been called?"

 

Below is the scenario for which i am considering stopping the UDF execution.

The user realizes that the intended parameters were not passed and

wants to stop the execution of the UDF.

 

I have referred the posts below but i am not sure whether what i am trying to achieve is feasible.

 

https://groups.google.com/forum/?fromgroups#!searchin/exceldna/stop$20calculation|sort:relevance/exceldna/bXzOzC7-dNE/QRwYSLx99JYJ

https://groups.google.com/forum/?fromgroups#!searchin/exceldna/stop$20calculation|sort:relevance/exceldna/8zzhcX5MC8A/WP4GqSn7aSQJ

http://www.decisionmodels.com/calcsecretsh.htm

 

The link http://www.decisionmodels.com/calcsecretsh.htm mentions that one can control the users ability to interrupt calculation by

specifying what will interrupt the calculation.

Application.CalculationInterruptKey= XlAnyKey | XLEscKey | XlNokey.

 

I tried putting the below code

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

_excelApplication.CalculationInterruptKey = XlCalculationInterruptKey.xlAnyKey;

in AutoOpen() and checked if pressing any key during calculation stops the  execution but it doesn't.

 

Anyone has some idea on how this can be achieved please let me know.

 

Thanks,

Arjun

[Govert van Drimmelen]

Hi Arjun,

While executing a UDF or a macro (which is what happens when your ExcelAsyncUtil.QueueAsMacro delegate runs) you can check whether the user has pressed Esc by calling XlCall.Excel(XlCall.xlAbort, true). 

The documentation for xlAbort is here: http://msdn.microsoft.com/en-us/library/office/bb687825.aspx

Regards,

Govert

[Andrew]

Old thread but how do you call XlCall.xlAbort? where do you insert it in the code?

[Govert van Drimmelen]

Your UDF can use xlAbort during its long-running calculation to check whether the user has pressed Esc.

You call these C API functions as:

   abort = XlCall.Excel(XlCall.xlAbort)

If 'abort' is true, then your function can abandon its long-running calculation, returning control to the user.

If your long-running calculation has a big loop, that gives you a convenient place to check, otherwise you have to find strategic places in your code to check this.

-Govert