I've defined one excel function as below.
[ExcelFunction(Name = "GetUserData", Description = "Populate User Data.", Category = "User", IsMacroType = true)]
public static object GetUserData()
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)
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.
Mar 27, 2012 at 9:43 PM
Edited Mar 27, 2012 at 9:57 PM
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.