Moving vba codes to a DLL

[dseligo]

Thank you dseligo for spending time on my project. I did your guidance, but this error appears after calling the function in Excel :
"File not found ..."
I'm puzzeld that the functions are visible in function list of excel, but it returns "#VALUE" error.
Window OS is 64, Compiler is x86_64-win64

Is your Excel 64-bit or 32-bit (you can go to Task Manager details page, turn on column Architecture and see it there)?
Check target in Lazarus (Project, Project Options, Config and Target): if your Excel is 32-bit then Target OS should be Win32 and Target CPU family i386, and if your Excel is 64-bit then Target OS should be Win64 and Target CPU family x86_64.

Also, double check name and path.

[amir.eng]

I checked it again, still returns the same error.

Excel is 2013-64bit
Windows 10- 64bit

[dseligo]

I just tried with Excel 2013 64-bit and it's working. We are missing something.

I attached screenshots so you can see that it is working at my side.

I also attached compiled dll (same code I posted here) and Excel file, if you want to try that. You need only to change drive to F (I tested with E drive).

If I can think of something else, I will post it here.

[Jurassic Pork]

Hello,
amir.eng are you sure that you have an Excel 64 bits ? With a 64 bits Lazarus Dll and Excel 32 bits there is your error.
In Attachment a 32 bits Lazarus Dll : Works with an Excel 2016 32 bits.
Friendly, J.P

[Zvoni]

Not sure if it's your problem, but i noticed something!
in your FPC-Lib you use "Integer" for calc_get and calc_int in ObjFpc-Mode
and you use "Integer" in your API-Declares in your VBA-Code.

That's wrong!

ObjFpc-Mode Integer: signed 32-Bit
VBA Integer: signed 16 Bit

You have to use Long in your VBA-Code.
Change the 3 "Integers" in your Declares to Long

[amir.eng]

I just tried with Excel 2013 64-bit and it's working. We are missing something.

I attached screenshots so you can see that it is working at my side.

I also attached compiled dll (same code I posted here) and Excel file, if you want to try that. You need only to change drive to F (I tested with E drive).

If I can think of something else, I will post it here.

I replaced my DLL with your DLL then it works fine !!!
I think something is wrong with my DLL, I attahed it, please check it with your Excel.

[dseligo]

I just tried with Excel 2013 64-bit and it's working. We are missing something.

I attached screenshots so you can see that it is working at my side.

I also attached compiled dll (same code I posted here) and Excel file, if you want to try that. You need only to change drive to F (I tested with E drive).

If I can think of something else, I will post it here.

I replaced my DLL with your DLL then it works fine !!!
I think something is wrong with my DLL, I attahed it, please check it with your Excel.

I didn't check it yet, but judging by the size of it, your dll is 32-bit and not 64-bit (when I was testing 32-bit was around 300kB and 64-bit around 600kB).
Can you post screenshot of Project, Project Options, Config and Target screen in Lazarus?

[dseligo]

Hello,
amir.eng are you sure that you have an Excel 64 bits ? With a 64 bits Lazarus Dll and Excel 32 bits there is your error.
In Attachment a 32 bits Lazarus Dll : Works with an Excel 2016 32 bits.
Friendly, J.P

I think it's the other way around: he has 64 bit Excel, but he compiles dll to 32 bits.

[dseligo]

Not sure if it's your problem, but i noticed something!
in your FPC-Lib you use "Integer" for calc_get and calc_int in ObjFpc-Mode
and you use "Integer" in your API-Declares in your VBA-Code.

That's wrong!

ObjFpc-Mode Integer: signed 32-Bit
VBA Integer: signed 16 Bit

You have to use Long in your VBA-Code.
Change the 3 "Integers" in your Declares to Long

You are right here, but it works even if there are declared as integers in 32 and 64 bit Excel and in 64 bit Libreoffice.

This is reference from Microsoft about types:
https://docs.microsoft.com/en-us/office/client-developer/excel/how-to-access-dlls-in-excel

That is what VBA code in Excel should look like:

Code: Text  [Select][+][-]

1. Private Declare PtrSafe Function calc_get Lib "E:\excel_dll\excel_dll.dll" () As Long
2. Private Declare PtrSafe Function calc_int Lib "E:\excel_dll\excel_dll.dll" (ByVal a As Long) As Long
3. Private Declare PtrSafe Function calc_double Lib "E:\excel_dll\excel_dll.dll" (ByVal a As Double) As Double
4.  
5. Function cell_get() As Long
6.   cell_get = calc_get
7. End Function
8.  
9. Function cell_int(a As Long) As Long
10.   cell_int = calc_int(a)
11. End Function
12.  
13. Function cell_double(a As Double) As Double
14.   cell_double = calc_double(a)
15. End Function
16.  
17. Sub test()
18.   MsgBox calc_int(10)
19. End Sub

[dseligo]

I replaced my DLL with your DLL then it works fine !!!
I think something is wrong with my DLL, I attahed it, please check it with your Excel.

I tested your DLL and it is working with 32 bit Excel, so you are compiling for 32 bit and not 64 bit.
Check that you 'Config and Target' in Lazarus are as in screenshot I attached, and then recompile your dll.

[Zvoni]

You are right here, but it works even if there are declared as integers in 32 and 64 bit Excel and in 64 bit Libreoffice.

I might be right, and it might work, but IMO it's VERY GOOD practice to use the correct Types.
This time the OP is also the author of the dll/library.
The next time it's someone else, and you don't see the source-code for the dll.
And now let's return a fully used 32-Bit integer, and cram it into a 16-Bit integer...... BOOM

[amir.eng]

Thank you all, it was my fault, it works by changing compiler target to 64. I'm sorry to bother you.

At the final, I wanted to combine "BobDog" and  "dseligo" methods to run vba command from DLL, I was trying it with simple command : Range("A1").value = "Test"

But Excel hangs and stops working after calling funciton and also Lazarus returns this warning while compiling:

Warning: Symbol "RunCommand" is deprecated

Could you please take a look at the attached project ? 

[amir.eng]

Thank you all, it was my fault, it works by changing compiler target to 64. I'm sorry to bother you.

At the final, I wanted to combine "BobDog" and  "dseligo" methods to run vba command from DLL, I was trying it with simple command : Range("A1").value = "Test"

But Excel hangs and stops working after calling funciton and also Lazarus returns this warning while compiling:

Warning: Symbol "RunCommand" is deprecated

Could you please take a look at the attached project ?

Any help?

[marcov]

Runcommand with a single commandline is deprecated. Runcommand with exe name and each parameter separately not.

[BobDog]

you can use
runcommand('cscript.exe',['/Nologo',filename],s);
in ~~line 38 in my dll. This will give no warning and I think is is not deprecated yet in that form.