(Excel and Vensim)link

[tomfid]

Your VB can work almost as before; you only need to SETVAL to set the active iteration number.

[ahmadi2010]

Tony;
No, It dosen't work. error: Unable to get constant values.

[Administrator]

Can you post the model and Excel file? It sounds like you have a simple syntax error, but it's hard for us to advice when we cannot see the equations or spreadsheet.

[ahmadi2010]

Tom and Tony;
Thank you,now it works with this address ('?inputs')

But I need to SETVAL like this:

Code: Select all

For i = 1 To 4
For j = 1 To 9

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

But I've made a new variable (Sum of D),When I want to run the macro an error appears(Attached file)

[ahmadi2010]

I've edited the attached file. See the new one please.

[tomfid]

If it fails with "unable to get constant values" there's probably a problem with access to the Excel file. You either need to provide the .xls in the same directory as the .vpm, or include it in the .vpm when you File>Publish it (use the Add button to add it to the archive).

[Administrator]

This works fine for me.

What language is your computer running?

And in Excel, in English the syntax for row/column is R1C1. Is this the same for you (different languages have different letters for Row/Column).

Tony.

[ahmadi2010]

Tom:

After I corrected the address ('inputs?'),Vensim dosen't fail.But when I want to get value in Excel after running this macro:

Code: Select all

Sub SetConstantaAndSimulateForFirstTime()
.
.
.
End Sub

The error that I send it as attachment appears in Vensim and for each of 4 column vensim uses only the data in column 1 and can't change the column for the naxt iter. For example when I want to get value in Excel, for "Sum of D" this value '75658' appears and for other iterations, the value is the same!!!
Can you please check this macro for me to see if it's correct or not?

Tony:
my computer language is English.
but I don't know from where I can check this:

And in Excel, in English the syntax for row/column is R1C1. Is this the same for you (different languages have different letters for Row/Column).

[Administrator]

Can you try and describe what you are trying to do?

You have some VBA which is trying to set values in Vensim, but then you have the Vensim model linking back to the Excel spreadsheet to try and read values (which it cannot do as Excel is running a macro when you are trying to read values).

From what I do see, you need to change the model so that "C" is a constant (and not reading values from Excel). Then use the following function.

Tony.


Sub SetConstantaAndSimulateForFirstTime()

Dim i As Long
Dim j As Long
Dim varstr As String
Dim returnList

For i = 1 To 4
For j = 1 To 9

varstr = "[Simulate>SETVAL|" & Cells(6, 7) & "[TV" & (j - 1) & ",I" & i & "] = " & Cells(5 + j, 7 + i).Text & "]"
Application.DDEExecute DDE_channel, varstr

Next

Application.DDEExecute DDE_channel, "[SIMULATE>runname|iter" & i & "]"
Application.DDEExecute DDE_channel, "[MENU>RUN1|O]"
Application.Visible = True
Application.DisplayAlerts = True

varstr = Worksheets("Sheet1").Cells(17, 7).Text

returnList = Application.DDERequest(DDE_channel, varstr)

Worksheets("Sheet1").Cells(17, 7 + i).Value = returnList(LBound(returnList))

Next
ActiveWorkbook.Save

End Sub

[ahmadi2010]

Thank you Tony.

The model that I sent for you was a very simple model. I'm working on a very complicated model. I'm using an optimization model "PSO" in Matlab program. this model produces for example 9 value for iter1, 9 value for iter 2 and . . . .In fact Matlab produces some time series.
then send these time series to Excel "Book2" and then Vansim should use iter 1,iter2, . . . to calculate objective function and then send this values to Excel. again Matlab uses this values and produces new valuses for iter1:4(new time series for iter 1,iter 2, . . .) and . . . . .I've written a macro to do this procedures automatically.

I did as you said but give error again after running that macro!!!!I've attached that error.

[tomfid]

I didn't realize that VB locked up Excel. I think that means that my proposed architecture won't work. However, it would work if the GET XLS functions were replaced with explicit numbers in Vensim constant equations.

[tomfid]

... It sounds like you do need to pass those values to the model, so what you'd have to do is iterate over your columns and issue a SETVAL statement for each element you need to pass.

[ahmadi2010]

Your model is wonderful. I could never write like that.

I need to find a way that can change the column automatically (GET XLS CONSTANTS( '?inputs', 'sheet1', 'H6' )

[Administrator]

Do you have Vensim DSS?

[ahmadi2010]

Yes Tony.
I have Vensim DSS version 5.9b.

[Administrator]

Try the attached.

There is a new function in VB called "RunModelMultipleTimes". Run this and it should do what you want.

Tony.

[ahmadi2010]

Thank you Tony.

Dose it work with VensimPLE?

With Vensim DLL this warning appears:

Run time error 53
File not found:vendll32.dll

I think this version of Vensim, can't work whit dll.
I've already had this problem

[Administrator]

No it will not work with PLE.

You need to reinstall Vensim DSS, and make sure you install the DLL as well (it is one of the options when you install Vensim).

Tony.

[ahmadi2010]

I've reinstalled it several times. But I'm working with Windows7.It dosen't work on this windows

Is there any way that I will not have to use DLL?

[Administrator]

I've just installed Vensim 5.9b on Windows 7 (x64) and it runs fine.

It installed vendll32.dll to c:\Windows\SysWOW64, but if you are on Windows 7 x32 the path might be different.

When you installed Vensim, did you tick the box as I've shown in the attached image?

Tony.

[Administrator]

You could also try installing the Vensim Model Reader (which is free). This also installs the Vensim DLL, and will definitely work on Windows 7.

http://www.vensim.com/freedownload.html

[ahmadi2010]

I don't have the original CD of Vensim. A company have installed it on my Laptop.

I'm working with Windows 7 (x64).I've just checked this path "c:\Windows\SysWOW64" but I can't see the vendll32.dll

I find it on this path "C:\Windows\System32 ". I've copied it to "c:\Windows\SysWOW64" but my program dosen't work again.

[Administrator]

Can you try installing Vensim Model Reader? That will give you the DLL installed properly.

[Administrator]

And if the DLL is in \windows\system32, try the attached Excel file (I modified the code so that it tries to load c:\windows\system32\vendll32.dll).

[ahmadi2010]

Thank you Tony.
The new Book2 gives the same error.

I can't select DLL option.What can I do?