How to stop Excel from freezing when using a UDF that takes a while to return a value?

[Steven John Lally]

Hello,

I am creating an add-in that requires retrieving data from a server.

Data will be retrieved base on what parameters the user has passed into the UDF.

However, it takes a while to establish a connection to the server then getting that data. This, unfortunately makes the Excel UI freeze.

The problem is we're expecting our users to use a lot of the UDFs. And having all the freezing time add up will discourage our users to accept our new system.

Is there any way that the UDF can get the data and do calculations while the user is still able to interact with the UI?

Best Regards,

Steven

[Govert van Drimmelen]

Hi Steven,

Yes - you're looking for 'async' functions.

You'll find a lot of information and samples on this by searching the Google group, CodePlex site and GitHub samples.

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To post to this group, send email to exce...@googlegroups.com.
Visit this group at https://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Steven John Lally]

Hello Govert,

I have taken a look at this page and have tried following it to create an asynchronous function.

However when I try to run the function, the cell shows #N/A.

Please have a look at my code:

This is part of my Functions.cs file:

public static string SMPLoop(double time)

        {

            double value = 1;

            string returnString = "DONE!";

            for(int i = 0; i < time * 1000000000; i++)

            {

                value++;

            }

            return returnString;

        }

        public static object SMPLoopAsync(double time)

        {

            return ExcelAsyncUtil.Run("SMPLoopAsync", time, delegate{ return SMPLoop(1); });

        }

Also I have a ThisAddIn.cs file which contains the ThisAddIn class which implements the IExcelAddIn interface.

In the AutoOpen event handler. I have added the line ExcelAsyncUtil.Initialize(); and it tells me that it is obsolete.

Best Regards,

Steven

[Govert van Drimmelen]

Hi Steven

#N/A is what we expect to see by default while it runs.

Maybe it's still running, or you are in Manual calculation mode and need to recalculate manually to see the result.

-Govert

--

[Steven John Lally]

Hello Govert,

Even after removing the for loop that simulated the delay, it still returns #N/A

Best Regards,

Steven

[Govert van Drimmelen]

Manual mode?

Press F9 to recalculate.

-Govert

--

[Govert van Drimmelen]

Other possible problem is if 'time' is changing with every call, e.g. If you use NOW() for that parameter.

Can't have a volatile input like that fir an async function - will start a new calculation for every call, and never complete.

-Govert

[Steven John Lally]

Hello Govert,

Sorry, I don't understand how time would change because when I use the functions I just type =SMPLoopAsync(1) in the cell.

Also, I checked my calculation settings and it is on Automatic Mode.

Best Regards,

Steven

[Steven John Lally]

Also, I have copied the code of the second code block in the Excel-DNA Asynchronous Functions Documentation page and used it in my code for testing.

https://exceldna.codeplex.com/wikipage?title=Asynchronous%20Functions&referringTitle=Documentation

It still doesn't seem to work:

public static object SleepAsync(string ms)
        {
            return ExcelAsyncUtil.Run("SleepAsync", ms, delegate
            {
                Debug.Print("{1:HH:mm:ss.fff} Sleeping for {0} ms", ms, DateTime.Now);
                Thread.Sleep(int.Parse(ms));
                Debug.Print("{1:HH:mm:ss.fff} Done sleeping {0} ms", ms, DateTime.Now);
                return "Woke Up at " + DateTime.Now.ToString("1:HH:mm:ss.fff");
            });
        }

Best Regards,

Steven

[Steven John Lally]

Perhaps I have forgotten an important step?

Do I have to set ComVisible to true in the assembly?

Best Regards,

Steven

[Govert van Drimmelen]

Perhaps you RTD Throttle Interval has been set to something strange (it's a global value).

Go into the VBA IDE and into to immediate window type 

      ? Application.RTD.ThrottleInterval 

to see what the current value is.

The default value is 2000 (which means 2 seconds).

What do you have there?

-Govert

--

[Steven John Lally]

Hello Govert,

I have typed it into the immediate window. However I get an error message saying:

error CS0103: The name 'Application' does not exist in the current context

Best Regards,

Steven

[Govert van Drimmelen]

This is in the VBA IDE of Excel (press Alt+F11 in Excel).

-Govert

--

[Steven John Lally]

Hello Govert,

It returned the value 2000.

Best Regards,

Steven

[Steven John Lally]

I'm not sure  if this information would be useful.

Everytime I run the function for the first time I get a security warning saying Real-time data servers have been disabled.

However, once I enable it then run the formula again, the result is still the same.

Best Regards,

Steven

[Govert van Drimmelen]

Hi Steven,

That security warning is the problem!

Excel-DNA's async functions are implemented using the Excel RTD feature.

I've never heard of that exact message, and it certainly does not come from Excel-DNA. So perhaps you have some third party software blocking it, or some other setting prevents it.

You'll have to figure out how to enable RTD servers on your Excel for your async functions to run.

Let us know if you find out more.

-Govert

--

[Steven John Lally]

Hello Govert,

You were right. It was the security warning.

To stop it I did the following: 

On Excel 2016

File -> Options -> Trust Center -> Trust Center Settings -> Add-ins

Then I unticked Require Application Add-ins to be signed by Trusted Publisher

Thank you very much for your time and help.

Best Regards,

Steven