DLL does not work from VBA

[neuwirthe]

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]

Have you seen this thread:



http://www.alh.net/newlisp/phpbb/viewtopic.php?t=353">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]

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]

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]

I do not have BCC, and i had the same problem when I tried your

VB.NET example.

[HPW]

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]

I have put a BCC compiled DLL in http://newlisp.org/downloads/development/BCC-dll/">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]

http://support.microsoft.com/default.aspx?scid=kb;en-us;187912">http://support.microsoft.com/default.as ... -us;187912">http://support.microsoft.com/default.aspx?scid=kb;en-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]

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]

>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]

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]

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]

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]

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 Expand


Public Declare Function dllEvalNewLISP Lib "c:newlispnewlisp.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]

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



;-)