Moving vba codes to a DLL

[amir.eng]

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.

BobDog, Did you test and call it with Excel ?
unfortunately I couldn't run vb script by Calling DLL in Excel. Please share files or DLL if you have done it.

[BobDog]

Hi amir.eng
I don't have Excel, It was only a dll which can run vbscript files.

[Jurassic Pork]

Hello,

We dont need COM or Activex DLL, I guess producing Standard DLL is possible in Lazarus and this type of DLLs can be called easilly with Excel.
Honestly, I did it in VB6 before but VB6 is not secure these days and Also ActiveX DLL is not my goal.

It is not true if you want to execute typical Excel  instruction like Range("A1").value = "Test". In Your DLL you must use EXCEL COM object to execute this kind of instruction.
Donex seems the best solution to put and execute VBA code from a DLL.
Friendly, J.P

[dseligo]

Donex seems the best solution to put and execute VBA code from a DLL.

What is 'Donex'?

Do you refer to https://doneex.com/?

[Jurassic Pork]

What is 'Donex'?
Do you refer to https://doneex.com/?

Yes I made a mistake it is DoneEx   

And Example code to connect Lazarus to an Excel Instance and write a string in the cell A1 of the ActiveSheet :

Code: Pascal  [Select][+][-]

1. uses comobj;
2. // ....
3. procedure TForm1.Button1Click(Sender: TObject);
4.   var XL: OLEVariant;
5. begin
6.   try   // try to get Excel Instance opened
7.     XL := GetActiveOleObject('Excel.Application');
8.   except
9.     try
10.       // ShowMessage('Excel not already open create XL Object');
11.       // If no instance of Excel is running, try to Create a new Excel Object
12.       XL := CreateOleObject('Excel.Application');
13.     except
14.       // ShowMessage('Cannot start Excel/Excel not installed ?');
15.       Exit;
16.     end;
17.   end;
18.    XL.ActiveSheet.Range['A1'].Value := 'coucou';
19. end;    

   

[SymbolicFrank]

The last time I needed to do something like this, it was enough to put "VB6." in front of the function names. External access to Excel is messy and requires constant updates.

[dseligo]

External access to Excel is messy and requires constant updates.

My experience too.
I have much better experience using FPSpreadSheet (https://wiki.freepascal.org/FPSpreadsheet).

If OP still insist using VBA code from Lazarus the way he described, this might be the way:
1. Call dll as shown in post #12 (with regards of using correct types as explained in #23)
2. Running VBA code from dll as shown in post #11
3. Changing Excel's cells as shown in post #34

As we don't know the details of OP's intentions (is this one time convert, does he want to make universal tool to do this, what does VBA code do) this could be very complex task. Depending of what VBA code do, might mean that VBA code can't be run at all as shown in post #11. If it is one time convert, it might be easier to convert VBA code to Pascal and skip step 2.

[dseligo]

Donex seems the best solution to put and execute VBA code from a DLL.

I just noticed that OP mentions DoneEx in his first post and that he's unable to buy it.

[Thaddy]

Donex seems the best solution to put and execute VBA code from a DLL.

I just noticed that OP mentions DoneEx in his first post and that he's unable to buy it.

That is silly, because VBA - not to be confused with VB - is a scripting language and not a compiled language.
It is very simple to store VBA scripts - or even its pseudo code- in a resource only DLL and store it as RT_RCDATA.
You do not need any commercial software for that. Just a resource only dll written in e.g. FPC.
And indeed you can use createoleobject to subsequenty execute that script from the resource.

[Jurassic Pork]

Hello,
 

That is silly, because VBA - not to be confused with VB - is a scripting language and not a compiled language.
It is very simple to store VBA scripts - or even its pseudo code- in a resource only DLL and store it as RT_RCDATA.
You do not need any commercial software for that. Just a resource only dll written in e.g. FPC.
And indeed you can use createoleobject to subsequenty execute that script from the resource.

DoneEx doesn't put the vba code in a dll : it builds a portable exe :

DoneEx XCell Compiler is the unique compiler for MS Excel XLS files which allows you to turn XLS workbook logic into portable EXE application (which require MS Excel to run) with hidden formulas and attach XLA Add-Ins to EXE. It also allows you to promote your company name and/or trade mark by attaching your own image as splash screen in final EXE package
VBA code protection : VBA copy protection blocks access to the VBA project (as it makes VBA project unviewable), and hides the VBA code, thereby disabling VBA debug mode. By applying the VBA code modification options, such as ‘Obfuscation’ and ‘Literals Removing’,  you will get modified VBA code, which will not work outside of the compiled application.

i don't work for DoneEx 
Friendly, J.P

[Thaddy]

Well, I will build a simple example in the weekend. (Did that already for KOL back in the early 20's).
It is quite easy.

[BobDog]

Are you using vba to call the dll?
for vbcode.dll
In freebasic (which is not a million miles removed from vb), I call the dll thus:
declare sub init lib "vbcode" alias "init"
freebasic won't accept a path under lib, i.e. the dll must be in the same folder as the executable using it.
Also freebasic doesn't want the .dll extension in the name.
try various things, maybe
declare sub init cdecl lib "vbcode" alias "init"
or make the init uppercase.

[amir.eng]

What is 'Donex'?
Do you refer to https://doneex.com/?

Yes I made a mistake it is DoneEx   

And Example code to connect Lazarus to an Excel Instance and write a string in the cell A1 of the ActiveSheet : 

I'm working with FPS for first time and it seems great !!
Your sample code works fine when excel is open, but it gives error during CreateOleObject Excel (when Excel application is not open) :

"Access violation"

[Jurassic Pork]

Hello,

Your sample code works fine when excel is open, but it gives error during CreateOleObject Excel (when Excel application is not open) :
"Access violation"

try this :

Code: Pascal  [Select][+][-]

1. uses comobj;
2. // ...
3. procedure TForm1.Button1Click(Sender: TObject);
4.   var XL: OLEVariant;
5.   begin
6.     try   // try to get Excel Instance opened
7.       XL := GetActiveOleObject('Excel.Application');
8.     except
9.       Exit;
10.     end;
11.     try
12.      XL.ActiveSheet.Range['A1'].Value := 'coucou';
13.     finally
14.     end;
15.   end;  

there is no sense to use createOleObject("Excel.application")    here, because in your case , you want to use this code in a dll  from an Excel workbook opened.

Friendly, J.P

[amir.eng]

Hello,

Your sample code works fine when excel is open, but it gives error during CreateOleObject Excel (when Excel application is not open) :
"Access violation"

try this :

there is no sense to use createOleObject("Excel.application")    here, because in your case , you want to use this code in a dll  from an Excel workbook opened.
Friendly, J.P

You're right, I don't need it right now, but I just wanted to learn how to create Excel application,Worbook, and Worksheet with Lazarus. It may be needed in the future.
Just very simple example as same prior code.

Thanks in advance.