Page 1 of 1
Getting an array from Vensim to Excel
Posted: Thu Jul 12, 2012 10:01 pm
by 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
Re: Getting an array from Vensim to Excel
Posted: Thu Jul 12, 2012 10:22 pm
by 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.
Re: Getting an array from Vensim to Excel
Posted: Fri Jul 13, 2012 10:22 pm
by 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
Re: Getting an array from Vensim to Excel
Posted: Fri Jul 13, 2012 11:19 pm
by 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
Re: Getting an array from Vensim to Excel
Posted: Sat Jul 14, 2012 1:00 am
by 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
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).
Re: Getting an array from Vensim to Excel
Posted: Mon Jul 16, 2012 3:09 pm
by 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
Re: Getting an array from Vensim to Excel
Posted: Mon Jul 16, 2012 4:40 pm
by 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).