Excel VBA crashing
Excel VBA crashing
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.
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.
-
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
-
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
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.
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.
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)
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)
-
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
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
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
-
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
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)
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)
-
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
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.
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.
-
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
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.
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.
-
- Senior Member
- Posts: 1107
- Joined: Wed Mar 12, 2003 2:46 pm
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.
http://msdn.microsoft.com/en-us/library ... e.11).aspx
you can probably get something to work experimenting with that.
-
- Junior Member
- Posts: 4
- Joined: Mon Dec 12, 2005 5:07 pm
Re: Excel VBA crashing
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.
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.