This may be of interest to someone here.

AD, ZhLet's say I want to configure Excel so that I can work with a bunch of commands. Each command has its own specific data, has possible dependencies and updates certain sheets. Is this documented?

QuoteOriginally posted by: CuchulainnAD, ZhLet's say I want to configure Excel so that I can work with a bunch of commands. Each command has its own specific data, has possible dependencies and updates certain sheets. Is this documented?I am not sure if this is the right thread for this question. Perhaps you want to start a new thread. From what I have seen people tend to use this thread to ask for help with xlw or provided feedback.

xlw 4.0 has now been released

Boost uBLAS vector as default implementation for xlw::MyArrayIt is straightforward to switch the default implementation ( std::vector<double> ) of xlw::MyArray to boost::numeric::ublas::vectorThe solution is to simply build a version of XLW in which MyArray is implemented as boost::numeric::ublas::vector. The source for XLW is easily installable from the XLW installer by checking the appropriate option. Building your own version of the xlw library is very straight forward. The steps for Visual Studio are as follows.Assuming you already have Boost installed.1. Install XLW 4.0 along with the XLW source for your Dev environment. You need to check the appropriate option in the installer2. Goto the XLW directory containing the project file for building XLW. For me this is C:\Program Files\XLW\xlw-4.0.0f0\xlw\build and open the project file for your dev environment.3. For the project 4_0_0f0 goto Properties->C/C++->General and append the Boost Include directory to the list of include directories. For me this is C:\Program Files\boost\boost_1_394. Goto Properties->C/C++->Preprocessor and append the macro USE_XLW_WITH_BOOST_UBLAS to the Preprocessor Definitions.5. Rebuild the Solution/Workspace6. Repeat steps 3,4 & 5 for all your XLL projects and rebuild.This Youtube video clip demonstrates the above steps.

Persistent Objects in XLWIt has been asked several times on a number of forums and mailing lists how to implement the feature of persistent objects in XLW. The standard response from the XLW developers, and rightly so in my view, is to use ObjectHandler. However sometimes what is required is a very small and an extremely light-weight solution and some other times just a demonstration of how it could possibly be done. For these requests I have coded a VERY simple implementation of the feature just to illustrate the concept.The example XLW xll project can be downloaded from here It is a project for VS2008 but wouldn't take much effort to translate it for other IDE's. It consists of 2 Excel functions CreateObject and RetrieveObject.CreateObject: Takes 2 parameters. The first is the name of the object and the second is a range in Excel (xlw::CellMatrix) which is the object to be stored. It returns the name of the object appended with '#n' where n is a number (explained later).RetrieveObject :Takes the name of the object to be retrieved and returns it. If given the string 'matrix#100' as the name, then the function will actual consider the name of the object to be 'matrix'. Everything after the '#' is ignored.Whats with the '#' ?Consider a function foo which takes as a parameter an object name and then internally retrieves that object and acts on it. If the object is a range of cells on your spreadsheet then since foo only takes the name of the object as an input it will never know if you change anyone of those cells. If however the CreateObject function, used to create an object from those cells, always returns 'name#N', where N is incremented everytime it is called then foo will think it's input has changed. ('name#97' goes to 'name#98') and it will be forced to re-compute. Internally howevere the '#N' will be ignored. So it's a trick to force a function to calculate which needs to re-calculate but otherwise wouldn't given Excels behaviour. I picked up this little trick from a previous employer The Demo addin provided is far from an industrial quality, as said before is purpose is to be only illustrative. Moreover no consideration has been given to thread-safety, object types, clearing the cache and lot's of other important features that would be desireable.

QuoteThe Demo addin provided is far from an industrial quality, as said before is purpose is to be only illustrative. Moreover no consideration has been given to thread-safety, object types, clearing the cache and lot's of other important features that would be desireable. And version control is probably #1. If this s/w demo starts to 'grow' you find that you are attempting to create a boost Serialisation which can serialise and recreate arbitary UML object networks, including smart pointers. And you can have intrusive or non-intrusive versions.These days one should avoid creating databases; it just takes too long and it has already been done.

By 'persistent' I mean in memory only, across xll function calls, for the duration of that Excel instance and not across multiple Excel instances.

QuoteOriginally posted by: AlexesDadBy 'persistent' I mean in memory only, across xll function calls, for the duration of that Excel instance and not across multiple Excel instances.Ah.You are using the wrong term IMHO. QuotePersistence in computer science refers to the characteristic of data that outlives the execution of the program that created it. Without this capability, data would only exist in RAM, and would be lost when this RAM loses power, such as on computer shutdown.This is achieved in practice by storing the data in non-volatile storage such as a hard drive or flash memory.

QuoteOriginally posted by: CuchulainnAh.You are using the wrong term IMHO. Entirely possible. I used the word 'persistent' in it's literal sense, which would hopefully mean something to people that have asked for the feature.

QuoteOriginally posted by: AlexesDadQuoteOriginally posted by: CuchulainnAh.You are using the wrong term IMHO. Entirely possible. I used the word 'persistent' in it's literal sense, which would hopefully mean something to people that have asked for the feature.I think this is more a 'shared memory' (memory mapped files) issue in Windows?

I'd be interested to know how much demand there is for this feature. If we really want to take it seriously we would have to add exposing classes to the interface generator...

To my mind, the ability to call functions from excel and the ability to retain and query stateful objects are two separate pieces of functionality, and should be dealt with in separate libs. xlw does the first job only and does it well. We might want to write xlls which have only static functions and no object orientation, and conversely we might want a generic object cache concept in software other than xlls.Thus I think xlw not try to be all things to all men. If you have the means to call c++ from excel (xlw) and an object cache library (I haven't looked at ObjectHandler yet but it sounds like it does the job) then provided both are designed well, the plumbing between them should be lightweight and trivial.

QuoteOriginally posted by: mjI'd be interested to know how much demand there is for this feature. If we really want to take it seriously we would have to add exposing classes to the interface generator...In my everyday work having an object cache similar in spirit to the example I posted ealier is indispensible. All too often you want to deliver Excel functions that require parameters encapsulating market data. By their very nature these parameters can be very large, spanning huge cell ranges on spreadsheets. This also leads to large clumsy spreadsheets, a significant overhead in the function call, tighter dependency between sheets holding market data and sheets responsible for pricing. Almost always the huge cell ranges of raw market data (vol surfaces, yield curves, repo curves, swaption vol cubes etc) have to be converted into internal market data objects ( i.e they are used to instantiate instantances of classes representing market data objects). Moreover these objects are reasonably static. Requiring a pricing function to have to continually convert cell-ranges into appropriate market data objects on every call can be cumbersome and to me appears both unnecessary and unnatural. It is not particulary difficult or complicated to overcome these drawbacks of a pure functional interface. Consider the following setup.You have a (key,value) container class that is easily constructable from a cell-range. ArgumentList in XLW is more than adequate for this.All your market data classes are constructable from your (key,value) container.All your market data classes are convertible to your (key,value) container, such the the resulting (key,value) container can be used to re-construct a market data object of the same typeFor each market data object xxxx_object you have an internal cache Cache_xxxx, where you can add or remove objects of type xxxx_object with a given name.For each market data object xxxx_object you have a method Create_xxxx_object(string name, ArgumentList args) which instantiates xxxx_object with args and inserts it into Cache_xxxx.For each market data object xxxx_object you have a method Clone_xxxx_object(string source_name, dest_name) which instantiates a xxxx_object by cloning the object with name source_name and inserts it into Cache_xxxx with name dest_name .For each market data object xxxx_object you have a method Retrieve_xxxx_object(string name) which returns a cell-range encapsulating the ArgumentList resulting from the conversion of the xxxx_object with name name from Cache_xxxx.A typical function which requires as input a parameter of type xxxx_object would then have the following spec A signature func (CellMatrix & xxxx_input) If input is a single cell containing a string s, then retrieve from Cache_xxxx the object with name sIf input is NOT a single cell containing a string then consider it the ArgumentList for instanciating a new xxxx_object With this setup it would then become possible to have dedicated workbooks holding (possibly retrieving from a database) all the market data with numerous calls to Create_xxxx_object for various xxxx, whereby forcing a compute of the whole workbook (F9) would as a consequence trigger the population of the Caches. It would then be possible to close this workbook as it would no longer be required since all the market data is alread in memory (cached). To execute a pricing the user would only need to open a pricing wookbook on which the pricing function would have as input the names of the market data objects. In the context for these features for XLW, if it is decided they have a significant added value, I think the best way forward would be too ship XLW with an abstract API for the caching along with a default simple but sufficient XLW implementation. Having an abstract API would allow the user to switch in an alternative caching implementation in a similar way to how it is possible to switch in a different implementation for MyArrayAn very simple example can be downloaded here.

QuoteOriginally posted by: samyonezTo my mind, the ability to call functions from excel and the ability to retain and query stateful objects are two separate pieces of functionality, and should be dealt with in separate libs. Getting stateful information from UDFs within one Excel instance is quite normal. For example, you can have one function to create an interest yield curve and another to query discount factors. Conceptually, people can even argue classic functions such as RND() is stateful.In my view, the boundary of Xll UDF is that it can only be passively invoked and return some "values". This distinguishes it from other Excel related technologies such as RTD and add-ins.