DLL does not work from VBA

neuwirthe · Post by **neuwirthe** » Thu Sep 16, 2004 7:24 am

I tried do use the DLL in Excel through VBA.
It does not work, it crashes.
I have used similar code for other DLLs, so I assume the
signature of newlispEvalStr is not right.
Loading works, I get a handle on the DLL
(there is code to check that!)

Public Declare Function EvalNewLISP Lib "newlisp" Alias "newlispEvalStr" (ByVal LExpr As String) As String
Private Declare Function LoadLibraryA Lib "kernel32" (ByVal s As String) As Long
Private Declare Sub FreeLibrary Lib "kernel32" (ByVal h As Long)
Dim NewLISPhandle As Long

Sub LoadNewLISP()
On Error Resume Next
Dim mylib As String
mylib = ThisWorkbook.Path & "\newlisp.dll"
NewLISPhandle = LoadLibraryA(mylib)
MsgBox NewLISPhandle
End Sub

Sub UnloadNewLISP()
On Error Resume Next
FreeLibrary (NewLISPhandle)
End Sub

Sub test()
LoadNewLISP
MsgBox EvalNewLISP("(+ 2 3)")
UnloadNewLISP
End Sub

HPW · Post by **HPW** » Thu Sep 16, 2004 10:27 am

Have you seen this thread:

http://www.alh.net/newlisp/phpbb/viewtopic.php?t=353

By the way, what calling convention is used from VBA by default?
I see no definition in your code.

It must be stdcall

Tell us if you get it to work.
Maybe you can upload a XLS with demo-code?

neuwirthe · Post by **neuwirthe** » Thu Sep 16, 2004 11:02 am

This is getting really messy.
The problem is that VB and VBA cannot handle char * as return values,
strings work differently in VB.
I am investigating if it can be done.
I read the mentioned thread.
VB and VBA use stdcall, that is not the issue.
I added a test function returning integers
declared with DLLCALL to win32dll.c and it worked.

In VBA, one cannot declare the calling convention.

I already posted on microsoft.public.vb.general.discussion
to see if somebody knows how to write a C function
returning VB-usable strings.

HPW · Post by **HPW** » Thu Sep 16, 2004 11:05 am

I just rerun my old VB.NET test-code.

I get a strange error message using the latest GCC-compiled DLL.
After the third call I get an error "SystemNullReferenceException: ...."
When I change back to the BCC-DLL it runs stable!

I had no problems with the GCC-DLL in neobook and I have no reports from the neobook community about problems.

Strange effekt with GCC!

neuwirthe · Post by **neuwirthe** » Thu Sep 16, 2004 11:14 am

I do not have BCC, and i had the same problem when I tried your
VB.NET example.

HPW · Post by **HPW** » Thu Sep 16, 2004 11:26 am

To get the BCC-compile it is no problem since Lutz contains the makefile for all flavors in the source distribution.

So when you want to get a BCC-DLL I or Lutz can compile it and upload somewhere.

Lutz · Post by **Lutz** » Thu Sep 16, 2004 12:02 pm

I have put a BCC compiled DLL in http://newlisp.org/downloads/development/BCC-dll/

I wonder if there are any other reports of the new MinGW DLL on other applications than VBA?

Lutz

neuwirthe · Post by **neuwirthe** » Thu Sep 16, 2004 1:21 pm

http://support.microsoft.com/default.as ... -us;187912
has a description of how to pass strings between VB6 (also applies to VBA) and a C dll.
I think this mandates a 2 step process.
Evaluate in the DLL and assign to a temporary buffer.
Let VB call a function giving the size of the string in VB.
Allocates string in VB. Call C DLL to fil VB buffer.
I volunteer for the VBA testing. Can somebody try to implement
this mechanism in the C DLL?
I am not good enough in C to do it.
I think it would be VERY important to be able to call NewLISP
in office apps, especially in Excel.

neuwirthe · Post by **neuwirthe** » Thu Sep 16, 2004 3:13 pm

Thats the way it works in VBA:

Create a module in Excel's VBA editor.
Paste the code below.
Put the cursor inside the sub test
Press F5

Now we have NewLISP in Excel!!!!!!!!!!!!!!!!!!!!!!!!!!!!
More code to follow

Public Declare Function dllEvalNewLISP Lib "newlisp" Alias "newlispEvalStr" ( _
ByVal LExpr As String) As Long
Private Declare Function lstrLen Lib "kernel32" Alias "lstrlenA" ( _
lpString As Any) As Long
Private Declare Function lstrCpy Lib "kernel32" Alias "lstrcpyA" ( _
lpString1 As Any, lpString2 As Any) As Long
Private Declare Function LoadLibraryA Lib "kernel32" (ByVal s As String) As Long
Private Declare Sub FreeLibrary Lib "kernel32" (ByVal h As Long)
Dim NewLISPhandle As Long

Sub LoadNewLISP()
On Error Resume Next
Dim mylib As String
mylib = ThisWorkbook.Path & "\newlisp.dll"
NewLISPhandle = LoadLibraryA(mylib)
Debug.Print NewLISPhandle
End Sub

Sub UnloadNewLISP()
On Error Resume Next
FreeLibrary (NewLISPhandle)
End Sub

Function EvalNewLISP(LispExpression As String) As String
Dim resHandle As Long
Dim result As String
resHandle = dllEvalNewLISP(LispExpression)
result = Space$(lstrLen(ByVal resHandle))
lstrCpy ByVal result, ByVal resHandle
EvalNewLISP = result
End Function

Sub test()
LoadNewLISP
MsgBox EvalNewLISP("(* 56 56)")
UnloadNewLISP
End Sub

HPW · Post by **HPW** » Thu Sep 16, 2004 5:43 pm

>Now we have NewLISP in Excel!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Great news! I tried your example and it works for me in Excel 2002.

>More code to follow

More sample code always welcome to attract more user to newLISP.
Spread the word of newLISP to the VBA/VB world.

HPW · Post by **HPW** » Thu Sep 16, 2004 5:55 pm

And it seems to work with the current GCC-newLISP.dll
And it is found through the path enviroment,
since I had not copied it to the XLS-path.
And did you notice that it hold the symbol data between subsequent call's with F5. That the same nice thing as on other platform we tested. (neobook, powerbasic, purebasic)

Lutz · Post by **Lutz** » Thu Sep 16, 2004 11:41 pm

Thanks 'neuwirthe' for researching this! I tried to do use newlisp.dll in Excel again and again but did not succeed, just don't know enough about VBA. In a previous GUI version of newLISP you could do DDE with Excel.

On first sight I don't fully understand what you are doing, but I will dig into it and perhaps it is possible with some changes in newlisp.dll to make a newlisp.dll import into Excel more streight forward.

Thanks again ... the possibility of using Excel with newLISP is exciting. For me the spreadsheet is still the crown of all computer programs.

Lutz

HPW · Post by **HPW** » Fri Sep 17, 2004 6:15 am

I just take my Excel 2002 file and test in Excel97 with success.

>with some changes in newlisp.dll to make a newlisp.dll import into Excel more streight forward.

I think it is streight forward enough. He only uses WIN API functions to convert the result into the right format.

Lutz · Post by **Lutz** » Fri Sep 17, 2004 2:25 pm

It seems to me that at in least in VBA Excel you don't need the functions 'LoadNewLISP()' and 'UnloadNewLISP()'

I just did:

Code: Select all

Public Declare Function dllEvalNewLISP Lib "c:\newlisp\newlisp.dll" Alias "newlispEvalStr" (ByVal LExpr As String) As Long
Private Declare Function lstrLen Lib "kernel32" Alias "lstrlenA" (lpString As Any) As Long
Private Declare Function lstrCpy Lib "kernel32" Alias "lstrcpyA" (lpString1 As Any, lpString2 As Any) As Long
Private Declare Function LoadLibraryA Lib "kernel32" (ByVal s As String) As Long
Private Declare Sub FreeLibrary Lib "kernel32" (ByVal h As Long)
Dim NewLISPhandle As Long

Function EvalNewLISP(LispExpression As String) As String
Dim resHandle As Long
Dim result As String
resHandle = dllEvalNewLISP(LispExpression)
result = Space$(lstrLen(ByVal resHandle))
lstrCpy ByVal result, ByVal resHandle
EvalNewLISP = result
End Function

Sub TestEvalNewLISP()
MsgBox EvalNewLISP("(* 256 256)")
End Sub

And then put

=EvalNewLISP("(+ 3 4)")

in an Excel cell and see "7"

May be 'LoadNewLISP()' and 'UnloadNewLISP()' are necessary in other VB applications? in Excel the Declare alone seems to be sufficient for newlisp.dll to be loaded.

Lutz

ps: for VBA newbees: Note that some of the Declaration lines wrap in this post, unify them again when pasting into the VB editor in Excel

HPW · Post by **HPW** » Fri Sep 17, 2004 2:45 pm

Real nice, of cource we had to build a better example for newbees to point out the advantage of embeded newLISP.

Here they would say it is so simple in Excel: =3+4

;-)

neuwirthe · Post by **neuwirthe** » Sat Sep 25, 2004 6:37 pm

is there some repository where I can put examples? I now have VB6 and Excel examples as zip files.

About LoadNewLISP:
Your code works with a fixed path to newlisp. LoadNewLISP in the new examples will pompt you for its location when it is not found immediately.

I also think that as long as newlisp.dll is on the path, LoadNewLISP is not necessary. But I think it helps people if you show them how to handle a somehwat more difficult situation.

neuwirthe · Post by **neuwirthe** » Sat Sep 25, 2004 7:01 pm

Some more things:
Strings in VB are rather difficult, there is a special type BSTR which has to be used in C programs to be called by VB, but support for this type seems only to be supplied for Microsoft compilers. Therefore I am using a workaround calling WinAPI functions converting C's char * to VB strings.

Another thing: if you like Excel, try to have a look at my book

The Active Modeler - Mathematical Modeling with Microsoft Excel
Erich Neuwirth - University of Vienna (Austria)
Deane Arganbright - University of Tennessee, Martin
ISBN 0534420850

HPW · Post by **HPW** » Sat Sep 25, 2004 7:05 pm

I think Lutz will be interested in the demo-code.
(Send it to 'lutz at nuevatec dot com' and put newlisp in the subject)
Until Lutz has put it on the server, you could also send it to my mail.
(hpwickern at hpwsoft dot de)
Thanks again for working it out.
This is one more sample for the practical use of newLISP for real world work.

Lutz · Post by **Lutz** » Sat Sep 25, 2004 7:10 pm

Hi Erich,

You can send the zip file to me: lutz at nuevatec dot com . put the word 'newlisp' in the subject line, so it will not be eaten by my spam filters. I can than link to it from the 'Code Contributions' section.

Or if you can host it yourself, I can link to it.

Lutz

ps: oops, looks like HPW's and my brain worked in sync on this

neuwirthe · Post by **neuwirthe** » Sat Sep 25, 2004 7:37 pm

I just tried to adapt the WinAPI technique to the VB.NET example, and it does not work at all. I do not know VB.NET. Perhaps you can try to convert the VB6 technique to VB.NET? Maybe this takes care of the crashes in VB.NET

HPW · Post by **HPW** » Sat Sep 25, 2004 8:45 pm

I have a working VB.NET example posted here and as I posted, found to have problems with the GCC-compiled version of newLISP.dll
It was working with the BCC-compiled flavor. So interested should use this flavor until we found out what the problem could be.

neuwirthe · Post by **neuwirthe** » Sat Sep 25, 2004 9:58 pm

I was using your VB.NET example as a starter.
Have you done extensive testing with the BC compiled version?
I fear that it might have the same problems lik the GCC version
in a longer run.
Therefore, I think it would be worthwhile to adapt the lstrCpy method
to VB.NET also. but I have not found a way of doing this.

HPW · Post by **HPW** » Sun Sep 26, 2004 6:27 am

>Have you done extensive testing with the BC compiled version?

Not realy! Was only simple testing with calling one simple function again an again.
GCC crashed with the third call. BCC run a lot calls without problems, but I did not count. More testing have to be done if their is interest to run under .NET. My interest for the moment is to do testing for the WINCE-port as soon I get some hardware.

neuwirthe · Post by **neuwirthe** » Sun Sep 26, 2004 4:08 pm

Here is VB.NET code working with h GCC compiled version.

Public Class Form1
Inherits System.Windows.Forms.Form

<DllImport("c:\newlisp\newlisp.dll", _
EntryPoint:="dllEvalStr", _
CharSet:=CharSet.Ansi, _
CallingConvention:=CallingConvention.StdCall)> _
Public Shared Function dllNewlispeval(ByVal txt As String) As System.Text.StringBuilder
' Leave function empty - DLLImport attribute forwards calls to newlisp
End Function

Public Shared Function NewLisp(ByVal LispExpression As String) As String
Dim ResultObject As System.Text.StringBuilder
ResultObject = dllNewlispeval(LispExpression)
Return ResultObject.ToString
End Function

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim RetVal As String ' Stores the return value.
Try
TextBox2.Text = NewLisp(TextBox1.Text)
Catch ex As Exception ' Catch the error.
MsgBox(ex.ToString) ' Show friendly error message.
End Try
End Sub

#Region " Windows Form Designer generated code "
#I do not include this code
#End Region

End Class

HPW · Post by **HPW** » Sun Sep 26, 2004 6:33 pm

Erich,

Thanks again for this.
I will give it a try.

newlispfanclub.alh.net

DLL does not work from VBA

DLL does not work from VBA

Further information, no solution yet

Problem solved