"GET_XLS_SUBSCRIPT" query

[SionC]

Hi,

I am currently running a model from Excel using the dll, and I have the model published in .vpm format.

I want to be able to define the subscripts in the Excel, and the 'GET_XLS_SUBSCRIPT' function allows the subscript elements to defined in a spreadsheet. How can I trigger the subscripts to be updated in the .vpm file from Excel?

Many thanks

Sion

[tomfid]

Unfortunately this can't be done dynamically. Published models are essentially precompiled, because the .dll can't do that itself, so the array dimensions have to be fixed. GET XLS only updates the structure when run from the application.

A possible workaround, if you need to add dimensions on the fly, is to provide a couple of extra, inactive ones in the data from the start, then switch them on if needed.

This is one area where Ventity has an architectural advantage; it can add entities on the fly, even in the middle of a simulation.

[SionC]

Thanks for the info Tom - perhaps one to add to the Vensim Updates list

My model requires the same number of active subscripts throughout the simulation and I want to define this number in the interface, so I can perhaps create a set of .vpms with differing numbers of subscript elements and pick the one that is most appropriate for my particular situation. It would have been nice to be able to change the subscript names too for the sake of transparency, but I can handle that in the spreadsheet.

Best wishes

Sion

[SionC]

Hi,

I was just thinking a bit further about this...Is it possible from Excel to do the following:

- open up Vensim DSS
- run a command file/script that includes the commands to publish the model using a predefined .frm file (thus generating a VPM file with the correct subscript names)
- close Vensim DSS

That would create the VPM file that I then need. It does not matter if this republishing is visible to the user.

Thanks

Sion

[Administrator]

Not possible. Publishing the model essentially means you could generate a model via a text editor and the use it with the DLL. So you wouldn't need Vensim any more.

[tomfid]

Again, I think your best bet would be to have some spare dimensions with a switch to activate/deactivate them. The switch could reside in a corresponding Excel column header, so it would be easy to access.

Ventity, OTOH, can do this easily.

[SionC]

Thanks Tom,

I am going to have to do something like this; but instead have multiple models with varying numbers of subscript elements to make the simulation more efficient as it will not then simulate the unused array elements. I will need to chose the appropriate model file for simulation from Excel as required. Its a shame to do it this way as I cannot rename the subscript elements to something more appropriate. The "GET_XLS_SUBSCRIPT" function is working perfectly for this for another model I am using which I am running from Vensim itself. It's a shame that you cannot use this function from Vensim via the dll, but I will add this to the future Vensim improvements topic and keep my fingers crossed

Best wishes

Sion

[Administrator]

You could try GET_DIRECT_SUBSCRIPT.

GET_XLS_SUBSCRIPT communicates with Excel via DDE/OLE. When the Vensim DLL is loaded into Excel, it's in the same process space so cannot communicate with itself (if that makes sense). But if you use GET_DIRECT, it reads from disk so might well work.

[SionC]

Thanks - I'll give it a go...

[SionC]

Hi,

Sadly this does not seem to work. I have got the "GET DIRECT SUBSCRIPT" linking to the excel file to read in the subscript elements OK in the MDL version. However the VPM version of the model does not appear to look at the spreadsheet when run from Excel, or I cannot trigger it to look at the spreadsheet to repopulate the arrays. Eg, I got rid of the excel file with the subscript names in it and the model ran OK still. I did try sending the "Model>Check Model" command from Excel but this didn't seem to do anything.

I did try to use GET DIRECT SUBSCRIPT linking to a text file, but I couldn't get it to work. I couldn't work out the parameters for the function.

Thanks for your help thus far,

Sion

[tomfid]

The GET_SUBSCRIPT functions can't dynamically recompile the model with different dimensions, unfortunately.