Page 1 of 1
Excel VBA crashing
Posted: Tue Nov 25, 2008 10:08 pm
by newbie
Hi everybody.
I have a Excel workbook that loads and runs the Vensim model perfectly fine. When I am trying to call the variables the vensim doesn't seem to like something and it crashes Excel without throwing any error. Here's piece of code that I'm running in Excel. The first vensim_get_varnames call runs fine without any problem and returns the amount of buffer needed. It's the second vensim_get_varnames call that crashes Excel completely. Any ideas or insights would be helpful. Thanks in advance.
Dim nResult As Long, nMaxStringLen As Long
Dim sFilter As String
Dim buf() As Byte
sFilter = "*"
nMaxStringLen = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, vbNullString, 0)
ReDim buf(nMaxStringLen)
nResult = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, buf, nMaxStringLen)
*** this is where problem occurs with the second vensim_get_varnames call. Not sure what I am doing wrong here.
Posted: Thu Nov 27, 2008 4:19 pm
by bob@vensim.com
This is odd. If I use
ReDim buf(nMaxStringLen + 10)
I don't get the crash. I will have to dig in a little bit to see if Vensim is calculating the buffer size incorrectly or VBA is not allocating all that it is asked for. But it seems like there is a mismatch coming from somewhere.
Posted: Mon Dec 01, 2008 3:32 pm
by bob@vensim.com
I took another look at this and the call
nResult = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, buf, nMaxStringLen)
does not pass the correct address. Apparently Byte() and String are different animals in VBA. If you change the prototype for vensim_get_varnames to
Private Declare Function vensim_get_varnames Lib "vendll32.dll" (ByVal filter$, ByVal vartype As Long, Buf As Byte, ByVal maxbuflen As Long) As Long
your code works fine. If you do this, you will need to check for any other vensim_get_varnames calls to be sure they are being passed Byte arrays and not String s.
Posted: Tue Dec 02, 2008 11:02 pm
by newbie
Thanks Bob. I'll try it out.
Posted: Thu Dec 04, 2008 5:24 pm
by newbie
Bob,
Still running into the same issue with changed code.
Private Declare Function vensim_get_varnames Lib "vendll32.dll" (ByVal filter$, ByVal vartype As Long, Buf() As Byte, ByVal maxbuflen As Long) As Long
Dim Buf() As Byte
sFilter = "*"
nMaxStringLen = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, Buf, 0)
ReDim Buf(nMaxStringLen)
nResult = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, Buf, nMaxStringLen)
Posted: Fri Dec 05, 2008 12:36 pm
by bob@vensim.com
The declaration should be "Buf as Byte" not "Buf() as Byte". If you replace that do you still have a problem?
Posted: Tue Dec 16, 2008 5:19 pm
by newbie
Hi Bob,
Sorry got side tracked on another project work. Anyways, I tried that and it doesn't seem to work either. It is expecting an array due to this declaration
Private Declare Function vensim_get_varnames Lib "vendll32.dll" (ByVal filter$, ByVal vartype As Long, Buf() As Byte, ByVal maxbuflen As Long) As Long
Posted: Wed Dec 17, 2008 11:41 am
by bob@vensim.com
The declaration should be
Private Declare Function vensim_get_varnames Lib "vendll32.dll" (ByVal filter$, ByVal vartype As Long, Buf As Byte, ByVal maxbuflen As Long) As Long
Dim Buf() as Byte is correct.
Posted: Wed Dec 17, 2008 10:06 pm
by newbie
Well, I get "ByRef argument type mismatch" compile error.
Private Declare Function vensim_get_varnames Lib "vendll32.dll" (ByVal filter$, ByVal vartype As Long, Buf As Byte, ByVal maxbuflen As Long) As Long
Dim Buf() As Byte
sFilter = "*"
nMaxStringLen = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, Buf, 0)
ReDim Buf(nMaxStringLen)
nResult = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, Buf, nMaxStringLen)
Posted: Thu Dec 18, 2008 12:35 pm
by bob@vensim.com
You need to pass Buf(1) when you use it as in
nResult = vensim_get_varnames(sFilter, VensimVarNames.VARTYPE_ALL, Buf(1), nMaxStringLen)
This passes a pointer to Buf(1) which is actually the beginning of the Buf array so it works.
Posted: Wed Dec 31, 2008 8:20 pm
by newbie
Yep, that worked. Thanks Bob.
Posted: Wed Jan 07, 2009 9:12 pm
by newbie
I am having similar issues with vensim_get_substring function call.
So, I changed the definition similar to vensim_get_varnames to
Public Declare Function vensim_get_substring Lib "vendll32.dll" (ByVal fullstring$, ByVal frompos As Long, Buf As Byte, ByVal maxbuflen As Long) As Long
Do While (bExitLoop = False)
Dim strVensimVarName As String
nResult = vensim_get_substring(strVensimVarName, nFromPosition, Buf(1), 100)
If (nResult <> 0) Then
nFromPosition = nFromPosition + nResult
comboBox_Parameter.Items.Add (strVensimVarName)
Else
bExitLoop = True
End If
Loop
Crashes Excel when calling the vensim_get_substring function. Any idea what I could be donig wrong here?
Thanks.
Posted: Thu Jan 08, 2009 12:05 pm
by bob@vensim.com
your code snippet does not make sense to me. strVensimVarName has to be a double null terminated utf-8 buffer filled from some other Vensim call so it certainly can't be declared within the while loop.
Posted: Fri Jan 09, 2009 5:42 pm
by newbie
Okay I figured that one out. Works just fine.
Dim strVensimVarName As String * 128
Do While (bExitLoop = False)
strVensimVarName = ""
nResult = vensim_get_substring(Buf(1), nFromPosition, strVensimVarName, 100)
If (nResult <> 0) Then
nFromPosition = nFromPosition + nResult
ComboBox1.AddItem (Trim(strVensimVarName))
Else
bExitLoop = True
End If
Loop
Now, I am running into another issue with displaying graphs. Excel does not provide handle to the picture box (image control).
Public Declare Function vensim_tool_command Lib "vendll32.dll" (ByVal Vcommand$, ByVal Vwindow As Long, ByVal iswip As Long) As Long
strVensimCommand = "SPECIAL>SETWBITEM|" & ComboBox1.Text
vensim_command (strVensimCommand)
lngResult = vensim_tool_command("WORKBENCH>GRAPH", Image1, 0)
I get an error saying object does not support this property or method. How do I access the graph command if I cannot get a handle to the window? I know it's more of an Excel issue but just wondering if there is a solution.
Thanks.
Posted: Sat Jan 10, 2009 11:59 am
by bob@vensim.com
I did a search on this and found
http://msdn.microsoft.com/en-us/library ... e.11).aspx
you can probably get something to work experimenting with that.
Re: Excel VBA crashing
Posted: Thu Dec 16, 2010 12:42 pm
by jaimonides
Hello,
I wrote this morning to Tony from Vensim UK I think, Then he placed my question in this forum.
At the same time I asked some colleagues about the proble. They are far from Vensim or SD issues but they know a lot about VB.
They find one solution, is this:
First, change the declaration of the function:
Private Declare Auto Function vensim_get_varnames Lib "vendll32.dll" (ByVal filter$, ByVal vartype As Int32, ByVal buf As Byte(), ByVal maxbuflen As Int32) As Int32
and then
Dim bytes(4096) As Byte
Dim caracteres As Char()
result = vensim_command("special>loadmodel|" & MisVariablesGlobales.ruta & "\datos\Dehesa.vmf")
result = vensim_get_varnames("*", 0, bytes, 4096)
caracteres = System.Text.Encoding.ASCII.GetChars(bytes)
ReDim Preserve caracteres(result - 3)
Dim cadenas As String() = String.Concat(caracteres).Split(Chr(0))
In cadenas is the list of variables of the model. Then they can placed in a listboz for example
Thank you for your fast response. It is good that I am not alone.