Excel is stateless and only supports primitive types. Now .NET assemblies canhave state and pass user defined objects. Therefore, in order to use .NETassemblies in Excel you need to write wrappers for the classes which passany user defined objects. Ideally, these wrappers should be produced on thefly at run time and this is what I was hoping to do. Anyone else done this?Say we have an OO bond pricing library and wish to wrap it for Excel. Whatshould the design of these wrappers be? And what design suits what users?Few methods with many parameters or many methods will few parameters?What classes and for what?

In fact, Excel is just a smart client for the "addin" service. The addinhere would correspond to other implementations of a service orientedarchitecture; such as web services. Hence, when moving otherimplementations to a service architecture the same problems willoccur. For example, moving:EJB ---- > Stateless session beans <---> Java Web services.NET assemblies ---> .NET XML Web serviceand so on...Hence, this issue is much wider than just unifying Excel and .NET:but how to more stateful OO applications to a service based design.Anybody got any ideas in this wider context?

Read whooper's discussion of Excel HandlesI would be nice if MS extended Excel ranges to let you store object references... obviously there would be issues in how/what to display in a cell, but it would simplify greatly the stuff whooper is talking about.

You are right. I saw two people who got something to work alongthese lines of putting something funny in a cell; then parse thecell giving you an object reference. You can get object referencesbut its too complicated. After all if you start parsing cells then whynot start writing C++/C# code in cells and parsing that.I'm actually working on an implementation of this exact problem.To me the core issue is that Excel does not have `state', and cannothandled stateful objects.

Is anyone using VSTO or ManagedXLL? Any experiences to share?I would like to set up a prototype using .NET and Excel 2002 and re-use our existing libraries.

QuoteOriginally posted by: DrBenYou are right. I saw two people who got something to work alongthese lines of putting something funny in a cell; then parse thecell giving you an object reference. You can get object referencesbut its too complicated. After all if you start parsing cells then whynot start writing C++/C# code in cells and parsing that.I'm actually working on an implementation of this exact problem.To me the core issue is that Excel does not have `state', and cannothandled stateful objects.Actually it isn't that hard... if I can do it (like whooper's approach, but with a focus on time series), anybody can. The trick is to make sure your anchor cell value changes when the object state changes... not hard, but it involves a lot of kludgy parsing. By the way, I built my version in straight VB/COM (with some deft hacking courtesy of the Hardcore VB book), but it works well enough and quick enough for me. A framework for stateful objects in cells.

QuoteActually it isn't that hard... if I can do it (like whooper's approach, but with a focus on time series), anybody can. The trick is to make sure your anchor cell value changes when the object state changes... not hard, but it involves a lot of kludgy parsing. By the way, I built my version in straight VB/COM (with some deft hacking courtesy of the Hardcore VB book), but it works well enough and quick enough for me. A framework for stateful objects in cells.Can you have more than one instance of the same class with your approach?Also, what does it mean in Excel to have methods which do not return anythingbut only update a state?...But the real problem is whatever can be done is should be done in a way thatpeople can easily understand it. Moreover, can you aford yourself to think in aOO way within a C based Excel.

QuoteOriginally posted by: DrBenCan you have more than one instance of the same class with your approach?Of course, that is the whole idea. There maybe singletons, there maybe one object per cell. It depends on the object and the objective.QuoteOriginally posted by: DrBenAlso, what does it mean in Excel to have methods which do not return anything but only update a state?...Create a new instance with the expected state. Yes this consumes memory, but the idea is to use Excel as a means for creating and manipulating objects in a spreadsheet fashion.For example: I have a class called "BackTest". I want to run 1000 back tests on a timeseries, but with different parameters for each back test. Solution: create 1000 "BackTest" cells, setup the parameters on the sheet then hit F9. Then use the methods to extract the result of each backtest and summarise.QuoteOriginally posted by: DrBenBut the real problem is whatever can be done is should be done in a way that people can easily understand it. Moreover, can you aford yourself to think in a OO way within a C based Excel.I don't see what the problem is... if you stick to the strict confines of OO vs procedural then you should give up now. Remember, people were building OO code in C way before C++ came on the scene. I can see that you are a major Java-type person... in my experience, Java forces people to obsess over OO purism. Procedural/composite OO is not evil. Free your mind!

Dr Ben: I checked out your XL AddIns, impressive stuff. Keep up the good work!

Thanks, its only in Beta and has a way to go, the structure is there but the functionality stillneed to be added.How do you keep track of method calls that affect the state of the object with each call. InExcel, there is no clear way to limit the number of times it updates the formula cells, so amethod which should add a value to an object, might wind up adding it several times, simlplybecause Excel is refreshing the sheet. Is it possible that you do not make stateful methodcalls which depend on how many times they are being invoked?Sorry to get going on about this but it is a pretty important issue.I am not a particular fan of anything in computers. A computers some box you program if itdoes what you want that's good if it does not then that's bad. Java,C, Assembly, VB, Office...I raelly don't care... OO, procedural, AOP who cares... I judge software by functionality andeasy of use, that's it...