Excel VBA crashing

[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.

[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.

[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.

[newbie]

Thanks Bob. I'll try it out.

[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)

[bob@vensim.com]

The declaration should be "Buf as Byte" not "Buf() as Byte". If you replace that do you still have a problem?

[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

[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.

[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)

[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.

[newbie]

Yep, that worked. Thanks Bob.

[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.

[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.

[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.

[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.

[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.