(Excel and Vensim)link

[ahmadi2010]

I'm using Excel to run the vensim software.When I use this command:

varstr$ = Cells(5, 12).Text + "@" + Str(i)

after getting the values, I can see the value in sheet1.How can I change above command for other sheets?

[Edited on 11-5-2010 by ahmadi2010]

[Administrator]

Try
worsheets("Sheet2").cells(5,12)

This is VBA though, searching Google will provide much more help than asking here.

[ahmadi2010]

Thank you Tony.

now it works correctly:)

[ahmadi2010]

hi,

What does this sign mean in vensim?(<>) (if pick<>1000 then . . . . )

does it mean not equal?

[Edited on 11-13-2010 by ahmadi2010]

[tomfid]

Correct - not equal. You can also use the expression :NOT:

I find negations somewhat confusing, so I tend to use positive expressions, e.g.

IF THEN ELSE( A = B, x, y )

rather than

IF THEN ELSE( A <> B, y, x ) or IF THEN ELSE(:NOT: A=B, y, x)

Tom

[ahmadi2010]

Thank you Tom.
I could solve my problem.

[Edited on 11-22-2010 by ahmadi2010]

[ahmadi2010]

Hi,
I have a question.
after running a micro, when excel wants to get values,for 10^6 it writes (1.M) and my matlab program can't accept this value and shows NAN, How can I solve this problem?

[Administrator]

You'll have to search Google for Excel number formatting.

[ahmadi2010]

I'm using this command in macro for set values to vansim but only for constant values in vensim:

For i = 1 To 12
For j = 1 To 6

varstr$ = "[Simulate>SETVAL|" + Cells(14 + j, .Text + "=" + Cells(14 + j, 8 + i).Text + "]"
Application.DDEExecute DDE_channel, varstr$
Next
Next

If I have lookup variable in vensim what should I do?Can any one help me please?

[tomfid]

One possibility would be to write your lookup changes to a .cin file, then load that.

Another option would be to use an array to represent your lookup, and use the VECTOR LOOKUP function. Then you can change the constants of the array with an ordinary SETVAL.

If your lookup represents a functional relationship, a good option is to parameterize your lookup so that you can vary its shape around a base case. For example:

Y = normalY * effectOfXonYlookup( X/normalX )^sensitivity

normalY, normalX, and sensitivity are parameters (constants) that let you change the operating point and slope of the lookup.

[ahmadi2010]

Thank you Tom for your quick responce.

I have to run a macro.For i=1 TO 7 :
First for i=1 Vensim must receive (time=0 To 9) the values from cell C4 to cell C13 and then run the vensim and after that excel get the results and again for i=2 Vensim must receive (time=0 To 9) the values from cell D4 to cell D13 . . . . . . .

I have to write a command in VBA.The variable name is inflow. How can I define it in vensim? lookup?
In Vensim I define it as a lookup and I define it's initial values like this:
(0,12),
(1,23),
.
.
.
(9,65)

[Edited on 12-2-2010 by ahmadi2010]

[tomfid]

Could you pass the values using GET XLS functions in your Vensim model rather than SETVAL commands?

Rather than using a lookup, use VECTOR LOOKUP. See http://www.vensim.com/documentation/ind ... ?22779.htm

[ahmadi2010]

YesTom, But if I pass the values using:get xls data('C:\test\b.xlsx','sheet1','A','C4'), it works only for cells C4 to C13,
for i=2 , . . .how can Vensim get the values (D4 to D13) and i=3 . . . . ., it should work automatically.

.
I don't have "VECTOR LOOKUP" option to select it in this version of vensim!

[Edited on 12-2-2010 by ahmadi2010]

[tomfid]

Ahh ... I see your problem. Do you have subscripts?

[ahmadi2010]

Yes I have subscript option in section type.

[Edited on 12-3-2010 by ahmadi2010]

[tomfid]

OK ... here's a trick that might work:

Create a subscript:
iteration : (i1-i7)

Use it to array your constant, then pick the active element of the subscript from your arrays:
myconstant[iteration] = GET XLS CONSTANTS( 'file', 'sheet', 'c4*' ) {note the star, which tells Vensim to read a column rather than a row of values - I assume that's how your data is laid out}
activeconstant = 1 {varies from 1 to 7, corresponding with iteration }
myselectedconstant = VECTOR ELM MAP( myconstant[i1], activeconstant-1 ) {note that the first argument references [i7] rather than [iteration], and the second subtracts 1, because VECTOR ELM MAP uses zero-based pointers}
copy/paste the same structure for your other variables (but use the same instance of activeconstant throughout).

With this setup, you can then SETVAL|activeconstant, and all your constant values will adjust appropriately.

An alternative to VECTOR ELM MAP would be VECTOR SELECT or just SUM - they just take one more equation to set up.

Tom

[ahmadi2010]

Thank you Tom,
I am confused a little . Could you see the attached file please.I don't have your email to send it for you.

[Edited on 12-3-2010 by ahmadi2010]

[Edited on 12-3-2010 by ahmadi2010]

[tomfid]

Here's a working version of your model.

I'm guessing that you actually want c to be a lookup (with values changing over time, running down the columns in your spreadsheet). I'll do another version implementing that.

[ahmadi2010]

Thank you Tom,
But I can't download this attachment.When I click on it,I see a web page with these content:

UTF-8}
c[iteration]=
GET XLS CONSTANTS( '?inputs', 'sheet1', 'H6' )
~
~ |

active c=
VECTOR ELM MAP(c[i1],active iteration-1)
~
~ |

active iteration=
1
~ [1,4,1]
~ |

one hour==
1
~ Hour
~ |

.
.
.
.

[Edited on 12-3-2010 by ahmadi2010]

[Edited on 12-3-2010 by ahmadi2010]

[tomfid]

Here's an example that treats c as a lookup over time. I used VECTOR SELECT this time, because VECTOR ELM MAP is harder to understand with more than one subscript dimension.

I just remembered that you don't have VECTOR LOOKUP - it's possible to implement without that, so I'll redo.

If you have trouble downloading .mdl files (which are text), right-click the download link and choose "save link as..." - be sure to add the .mdl extension if it's not already there.

[ahmadi2010]

Thank you very much Tom.I'm trying to understand it and if I still have question I'll ask you

[Edited on 12-3-2010 by ahmadi2010]

[tomfid]

Here's another version that doesn't rely on VECTOR LOOKUP

[ahmadi2010]

Dear Tom,

In Model4.mdl,I need only to change the 'c' address in the model?

GET XLS CONSTANTS( C:\desktop\Tomfid Code.Book2.xlsm', 'sheet1', 'H6' )

I can't run it.

And How can I change VBA code(Book2)?I should run this macro in Book2 :

Sub SetConstantaAndSimulateForFirstTime()
.
.
.Endsub

[Administrator]

In Model4.mdl,I need only to change the 'c' address in the model?
GET XLS CONSTANTS( C:\desktop\Tomfid Code.Book2.xlsm', 'sheet1', 'H6' )

You are missing a quote around the path to the file.
GET XLS CONSTANTS( 'C:\desktop\Tomfid Code.Book2.xlsm', 'sheet1', 'H6' )

Does it work if you try that?

[tomfid]

You don't need the full path to your Excel file. You're better off to keep the file in the same folder as your model, and use just the filename. Or, enter the filename using the ?myfile syntax, and Vensim will query for its location; then you can manage files via Model>Settings...>XLS files (see Help for GET XLS CONSTANTS if this doesn't make sense).