ExcelFunction Getting Called Twice

Mar 27, 2012 at 3:06 PM

Hi,

I've defined one excel function as below.

 [ExcelFunction(Name = "GetUserData", Description = "Populate User Data.", Category = "User", IsMacroType = true)]
        public static object GetUserData()
        {
           
            try
            {
                object[,] result = null;
                string url = Utilities.Instance.GetUserData();
                XmlDocument xmlResult = Utilities.Instance.GetUserDataFromService(url);
                result = Utilities.Instance.PopulateDetailsFromWSResult(xmlResult);
                >>  return XlCall.Excel(XlCall.xlUDF, "Resize", result);
	       //instead of returning an object[,] if i want to do something else over here, code is being called twice
	       //e.g if i'm creating new ExcelSheet object over here, since its getting called twice,it's creating two sheet object over here
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return ExcelDna.Integration.ExcelError.ExcelErrorGettingData;
            }   
        }

It works fine, while displaying the data. But one thing i've noticed that, I've put the debug point there & i can see that control is coming twice when i call this function. However, by making using of 'Resizer' class, it's displaying the data correctly. But it's creating an issue while i want to do something else.

Is it default behaivour of ExcelDNA/Resizer class, because in all my Excel Function, i'm getting the control more than twice.

Regards.

Leo

Coordinator
Mar 27, 2012 at 9:43 PM
Edited Mar 27, 2012 at 9:57 PM

Hi Leo,

Yes - when the target range is resized the function will be called again. 

Could you perhaps cache the service results, so that the second call would be fast? Maybe just a dictionary where the url is the key, and each cache entry has the results and some expiry time.

If you really want to do other things, like create a new sheet, after the servise call, it might be better to put this in a macro that is run from a ribbon button or something, rather than as a worksheet function.

Regards,

Govert