Sub Grab_Varnames()
Dim buf As String * 512
Dim buf2 As String * 128
Dim length As Integer
We allocate two buffers for holding the results of Vensim functions that fill in string buffers.
result = vensim_command("
SPECIAL>LOADMODEL|c:\Users\Public\Vensim\dll\worldapp.vmf")
filter$ = Worksheets("Sheet1").Cells(8, 5).Value
vtype = Worksheets("Sheet1").Cells(9, 5).Value
The filter and variable type have been taken from the spreadsheet itself and will then be passed to the Vensim DLL.
result = vensim_get_varnames(filter$, vtype, buf, 500)
The results of the query are now in buf as a series of null terminated strings ending in a double null. We put the required size of the buffer in cell A10. Note that no more than 500 characters will be copied into the buffer, so if this value is larger than 500 it means not all the results were included.
Worksheets("Sheet1").Cells(10, 1).Value = result
The next task is to copy these results into the worksheet.
x = 5
y = 0
Do
length = vensim_get_substring(buf, y, buf2, 128)
If (length = 0) Then Exit Do
Worksheets("Sheet1").Cells(10, x).Value = buf2
y = y + length
x = x + 1
Loop
The vensim_get_substring function is used to get the values of the Null terminated strings. When this returns 0 it is a signal that all the strings have been processed.
Finally, we need to erase the content of later cells to clean up anything that might be left there.
Do
length = Len(Worksheets("Sheet1").Cells(10, x).Value)
If (length = 0) Then Exit Do
Worksheets("Sheet1").Cells(10, x).Value = ""
x = x + 1
Loop
End Sub
Row 10 now contains the size of the buffer required to handle the results of the function call and a variable name in each cell staring in column E.
