ChrisSpicer

I'm looking to create a two stage process to my calculations: I want to create a UDF that will generate an object, then another UDF that will reference that object.

As a specific example, I want to calculate the yield of a bond. There a several pieces of static data required to do so (maturity, coupon, etc), plus some volatile data (namely price). I would like to use my first function to create a "bond object", then have my second function reference that and the price.

My first thought was to have a hashtable into which I put the objects as they were created, then returned the key to the spreadsheet. The second function would then use that key to find the object it needed. However, I can't figure out any way of knowing when to remove my objects from the hashtable.

Does anyone know of an elegant solution to my problem, using C#, Visual Studio 2005 and Excel 2003 I have seen this done in C++, but I'm not aware how it was implemented.






Re: Visual Studio Tools for Office Referencing Other Objects in C#

ChrisSpicer

I'm looking to create a two stage process to my calculations: I want to create a UDF that will generate an object, then another UDF that will reference that object.

As a specific example, I want to calculate the yield of a bond. There a several pieces of static data required to do so (maturity, coupon, etc), plus some volatile data (namely price). I would like to use my first function to create a "bond object", then have my second function reference that and the price.

My first thought was to have a hashtable into which I put the objects as they were created, then returned the key to the spreadsheet. The second function would then use that key to find the object it needed. However, I can't figure out any way of knowing when to remove my objects from the hashtable.

Does anyone know of an elegant solution to my problem, using C#, Visual Studio 2005 and Excel 2003 I have seen this done in C++, but I'm not aware how it was implemented.





Re: Visual Studio Tools for Office Referencing Other Objects in C#

ChrisSpicer

I am looking to create a two stage process to my Excel calculations: I want to create one UDF that will create an object, which another UDF will use for calculations.

For example, to calculate bond yields you need several pieces of static data (maturity, coupon, etc) and some more changeable data (namely price). I would like to create a 'bond object' for the static data with my first function, then reference that bond object in the second function.

My first thought was to use the first function to store the object in a hashtable, then return the key to the worksheet. The second function would then reference that cell, and use the key to obtain the object it needed.

This works to some degree, but I am unable to figure out how to know when to remove entries from the hashtable. Does anyone know of an elegant solution to this problem, using C#, VSTO and Excel 2003





Re: Visual Studio Tools for Office Referencing Other Objects in C#

X4U

Hi Chris,

why not create a hidden worksheet that stores the "persistent" values.

You can reference the values on your other worksheets from there

Remove Items:

When you close your workbook check on the hidden worksheet for unreferenced Items.

Just a proof of concept.

Generally I would prefere to store data in an external repository likean XML file or an SQL Server.

Hope this may give you a start.

Greets, Helmut






Re: Visual Studio Tools for Office Referencing Other Objects in C#

ChrisSpicer

I reposted my question, thinking the first time hadn't worked. Sorry about that.

Helmut - that's a good idea, but I'd really like something self-contained and (hopefully) more performant, which is why I'm looking to keep these objects in memory. I want to work with real-time data, so avoiding any disk-write overhead is key.

Also, what I hope to store may not be readily expressed as spreadsheet values or database entries.























Re: Visual Studio Tools for Office Referencing Other Objects in C#

Dennis Wallentin

Hi,

Why try to use two UDFs in the first place Use one function that makes all the 'preparations' and in the to the UDFs include the 'bond object'.






Re: Visual Studio Tools for Office Referencing Other Objects in C#

Denis BDA

Chris,

I had a similar situation where I had to do queries from a database that were long running to return financial data and the old way would lock up excel till the data returned. Basically, I wanted to achieve asynchronous UDF's like Bloomburg rather then synchronous like typical UDFs.

My solution was to get the initial UDF to spawn a separate thread. That UDF would provide it's row and column info for later use. The initial UDF would return a "loading" value to show that the data is being updated. Then when the thread completed, it would force the cell to reload the value by resetting the formula value

rng = _activeSheet.Cells(_activeRow, _activeColumn)

rng.Formula = rng.Formula

In the separate thread I woul store the value in a hashtable stored in the appdomain cache

System.AppDomain.CurrentDomain.SetData

And use the original parameters of the UDF call to build a unique ID for storing and retrieving from the cache.

Any time new data is recieved in the separate thread, you simply reset the formula value and have the UDF function check to see if the value is already available in the cache.

My means of managing the cache was to create a custom object with a timestamp which when retrieving data could be checked to see if the data was too old and had to be re-loaded.

Not sure if this is similar to what you're trying to achieve but thought it might help.