Getting an array from Vensim to Excel

[olive]

Hi,
I'm trying to retrieve an array from Vensim to Excel. I can retrieve single values in Excel using:

Code: Select all

'result = vensim_get_data("game.vdf", "VensimVariable", "Time", rval(1), tval(1), TIMEVensimVensim)
'For i = 1 To result
    'Sheet5.Range("A3").Offset(i, 0) = tval(i)  'Time
   'Sheet5.Range("B3").Offset(i, i) = rval(i)  
'Next i

But obviously, this doesn't work when the variable is an array... The array is defined in Vensim as VensimVariable[ID]
Any help on this would be greatly appreciated!
Thank you very much for your time and consideration,
Olivia

[tomfid]

During a game, you can use vensim_get_vecvals to retrieve an array, after first using vensim_get_varoff to retrieve offsets of the variable names.

Otherwise, you need to iterate, repeating calls to vensim_get_data for each subscript element for which you want data, i.e. x[id1], x[id2], ...

You can use vensim_get_varattrib with flag value 9 to retrieve an array of all of the subscript combinations if needed.

[olive]

Hi,
Thank you for your response.
I have tried the following:

Code: Select all

resultVenVar = vensim_get_varoff("VensimVariable[ID]")
MsgBox (resultVenVar)

The message box shows "779". I'm trying to understand the meaning of that number. Does it mean that I have 780 terms in this array?
Also is there a way to know how many rows and columns this array actually have?
Thank you very much for your help,
Olivia

[olive]

Hi,
Thank you for your response.
I have tried the following:

Code: Select all

    resultVenVar = vensim_get_varoff("VensimVariable[ID]")
    MsgBox (resultVenVar)

The message box shows "779". I'm trying to understand the meaning of that number. Does it mean that I have 780 terms in this array?
Also is there a way to know how many rows and columns this array actually have?
Also, I'm trying to write all the values contained in the array into Excel. To do so I use the following:

Code: Select all

result = vensim_get_vecvals(resultVenVar, rval8(1), nval)
For i = 1 To result
   Sheet5.Range("W3").Offset(i, 0) = rval8(i)  
Next i

The problem is that I don't know what nval is supposed to be... If I replace nval by the number of steps in my simulation (221) I end up with 221 values (some of them not making any sense at all, i.e. -1.3E+33) and I don't get the entire array...
Any help would be greatly appreciated!
Thank you!
Olivia

[tomfid]

779 is the offset of the element of VensimVariable[ID] that you pass in the storage matrix for variables. Typically, time has offset 1, the first level has offset 2, etc.

So, if you have

Code: Select all

ID : (ID1-ID10)

You should do something like the following:

Code: Select all

venVarOffset[0] = vensim_get_varoff("VensimVariable[ID1]")
for ( id=1; id<10; id++ )
     venVarOffset[id]=venVarOffset[id]+id;

Now venVarOffset contains a vector of offsets, one corresponding to each element of [id]. Note that if [id] is noncontiguous, you'd want to populate the offsets by individual calls to vensim_get_varoff using the specific variable and element name.

Then to retrieve data, call

Code: Select all

result = vensim_get_vecvals(venVarOffset,vals,nvals)  ;

where vals is an array of floats that you've allocated to contain the results, and nvals is the size of both arrays, which should equal ELMCOUNT(ID).

The result will be in vals[]. Note that this retrieves a bunch of variables at once, but NOT time series. If you want time series, use vensim_get_data.

I'm going from memory here, so a bit of fine tuning to my suggested code may be needed (plus if you're in C you need to allocate memory).

[olive]

Hi Tom,
Thank you very much for your email. I still have a few questions though :/
1)

Code: Select all

venVarOffset[0] = vensim_get_varoff("VensimVariable[ID1]")

or

Code: Select all

venVarOffset[0] = vensim_get_varoff("VensimVariable[ID2]")

etc.
always give me venVarOffset[0] = 779.
I guess I still don't understand what the offset is, what it means, and how it is used... Also what do you mean by "the first level has offset 2". What is "level" here.

2) Also, this comes back to me not understanding what an offset is: what is the purpose of the loop:

Code: Select all

for ( id=1; id<10; id++ )
     venVarOffset[id]=venVarOffset[id]+id;

For id = 1, venVarOffset[1]=venVarOffset[1]+1; which is a problem because venVarOffset[1] was never defined before. So should the loop be:

Code: Select all

for ( id=1; id<=10; id++ )
     venVarOffset[id]=venVarOffset[id-1]+id;

instead?

3) I'm working in Excel VBA, how can I call ELMCOUNT(ID)?

4) Would you happen to have an example (vensim model for which an array is exported into excel) that I could look at to see why I get these random values?

Thank you very much for your help,
Olivia

[tomfid]

Sorry - hasty code writing without actually compiling never works. The loop should be:

Code: Select all

venVarOffset[0] = vensim_get_varoff("VensimVariable[ID1]")
for ( id=1; id<10; id++ )
     venVarOffset[id]=venVarOffset[0]+id;

I think it should be the case that vensim_get_varoff("VensimVariable[ID2]") = vensim_get_varoff("VensimVariable[ID1]") + 1

If the two calls return the same value, get_varoff must be returning the position of the first element of the array, regardless of which element is actually passed, which seems like a bug. I'll take a look at the code. Tony may have some further insight.

Fortunately, as long as your ID range is contiguous, my loop should still work.

The offset represents the position of your variable in the memory block for all vars in your sim - so 779 means that your var is the 779th in the model (in some order - starting with time, then levels, then other stuff).