I'm trying to make excel 2013 webservice function volatile using formula like =webservice("url")*Rand or by using vba codes like calculatefull or calculateRebuildFull. I even tried to assign value using vba to that particular cell like Range("A1").Value = "=webservice(""url"")" it works fine for some minutes but then gives error and it is very deadly error as it seems, cell shows #VALUE error but excel pops up this message "excel ran out of resources while attempting to calculate one or more formulas as a result these formulas can not be calculated" due to this many of my cell which has formula (circular reference formula) are left with errors. I can not avoid circular references as they are very important in some of my calculations. Please help me if anyone has any idea about this errors and how to solve them.
1 Answers
You must consider that the webservice functions make calls to webservices, which are slow compared to the other Excel functions.
I don't know how MSFT have implemented that precisely, but I expect they have some sort of time-out period in there which prevents Excel from hanging because the calculation engine is waiting for a call to a webservice. After reaching a timeout I can imagine the function returning a #Value! error.
To avoid #Value errors propagating through your circular references, you must redesign your model so you can reset the errors, perhaps even revert to using VBA to drive the model so you can e.g. have your model use a previous result if a web function times out. See http://www.jkp-ads.com/Articles/circularreferences00.asp for some pointers on how to design and work with circular models in Excel.
