Moving vba codes to a DLL

[amir.eng]

Hello everyone,
I have written a macro in Excel with vba codes and may you know vba codes in excel is not secure so that anyone can unlock password protection of a Excel project with free softwares. Recently I found a commericial program (DoneEx VBA Compiler) which converts all vba codes to a Standard DLL and it's very secure, unfortunately I'm not able to but it.

I want to know if it's possible to do it via Lazarus ? I mean moving and converting all vba codes to a DLL and then call it in Excel.

[loaded]

Hi,
I struggled a lot with this issue at the time, but I couldn't do it because Lazarus doesn't have COM+ support.
But as a solution, I learned Activex DLL or Activex EXE methods in VB6.
Exe is more logical in my opinion because it handles the registration and similar processes by itself.

[amir.eng]

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.

[loaded]

With the above method, you can use the codes you wrote in Excel almost exactly.
Connecting it is even easier than any other method. You can easily connect even with a vbs you wrote in the notepad.
Also, Activex Dll is pretty safe, I haven't heard of a known vulnerability.
Still, the choice is yours, take it easy.

[marcov]

I struggled a lot with this issue at the time, but I couldn't do it because Lazarus doesn't have COM+ support.

What do you exactly miss?

[loaded]

What do you exactly miss?

No, there is nothing I miss, and I don't want to go back to those days. It's a bit complicated for me to explain this.

With the code below, we can connect to Excel and control it.

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

1. Excel:=CreateOleObject('Excel.Application');

Likewise, to control the applications we write, we must create an expression in the window operating system.

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

1. Project:=CreateOleObject('Lazarus.Project1');

This is exactly what I want to tell you;
The ability to create Com+ supported projects is available in Delphi, but unfortunately not in Lazarus.

Also, Import Type Library Support in Lazarus is available in VB6 as Reference. Then all the methods in Excel can be accessed very quickly with the Object Browser.

The code you write in this way can run smoothly both in Excel "Microsoft Visual Basic for Applications Editor" and in your own application.

[korba812]

You can create COM objects in FPC. The only thing missing is the TLB/IDL editor/compiler. It seems you can use MSIDL compiler for this, but I'm not sure.

[amir.eng]

Do you mean it's not possible to produce Standard DLL (Native DLL) in Lazarus ?

[PascalDragon]

Do you mean it's not possible to produce Standard DLL (Native DLL) in Lazarus ?

Of course it's possible to create a normal, native DLL in Lazarus. You just need to make sure that you use the correct types that VBA expects (e.g. Widestring instead of String or PChar) and the correct calling convention (stdcall). There is however no wizard that will help you convert your VBA code to FPC code.

[amir.eng]

I know that there is not a quick way to convert whole of vba code to FPC, then how do work the commercial softwares which create a DLL for a big vba code only in one second ?
I believe that the vba code are not converted to another language line by line, and they put all vba code in a DLL as it is. Since there are some libraries like "Autocad Library" in a macro file that  which it's not easy to convert it to another language, but the software can convert to a C++ DLL very easilly.

[BobDog]

If it is of any help, here I create a .dll using vbscript.
Compile this:
file: vbcode.pas

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

1.  
2.  
3. library vbcode;
4.  
5. {$mode delphi}
6.  uses
7.   process;
8.  
9.  var
10.  g:ansistring='';
11.  kill:integer=1;
12.  
13.  procedure savefile(fname:string ;text:ansistring;killflag:integer);
14.  label
15.  kill;
16. Var
17.  T:TextFile;
18. begin
19. if killflag<>0 then goto kill;
20.    AssignFile(T,fname);
21.    {$I-}
22.    try
23.    Rewrite(T);
24.    Writeln(T,text);
25.    finally
26.    CloseFile(T);
27.    {$I+}
28.    end;
29.    kill:
30.   if killflag<>0 then erase(T);
31. end;
32.  
33. procedure runscript(filename:ansiString);
34. var s:ansistring='';
35. begin
36. runcommand('cscript.exe /Nologo '+filename,s);
37. writeln(s);
38. End;
39.  
40. procedure createscript;
41. begin
42. g:=g+ 'Const TriStateTrue = -1 '+chr(10);
43. g:=g+ 'URL = InputBox("Enter (or paste) the URL to extract the Code "&vbcr&vbcr&_'+chr(10);
44. g:=g+ '"Exemple ""https://www.freebasic.net""","Extraction of Source text and html  ","https://forum.lazarus.freepascal.org/index.php?action=forum")'+chr(10);
45. g:=g+ 'If URL = "" Then WScript.Quit'+chr(10);
46. g:=g+ 'Titre = "Extraction du Code Source de " & URL'+chr(10);
47. g:=g+ 'Set ie = CreateObject("InternetExplorer.Application")'+chr(10);
48. g:=g+ 'Set objFSO = CreateObject("Scripting.FileSys]"]>Blockedbject")'+chr(10);
49. g:=g+ 'ie.Navigate(URL)'+chr(10);
50. g:=g+ 'ie.Visible=false'+chr(10);
51. g:=g+ 'DO WHILE ie.busy'+chr(10);
52. g:=g+ 'LOOP'+chr(10);
53. g:=g+ 'DataHTML = ie.document.documentElement.innerHTML'+chr(10);
54. g:=g+ 'DataTxt = ie.document.documentElement.innerText'+chr(10);
55. g:=g+ 'strFileHTML = "CodeSourceHTML.txt"'+chr(10);
56. g:=g+ 'strFileTxt = "CodeSourceTxt.txt"'+chr(10);
57. g:=g+ 'Set objHTMLFile = objFSO.OpenTextFile(strFileHTML,2,True, TriStateTrue)'+chr(10);
58. g:=g+ 'objHTMLFile.WriteLine(DataHTML)'+chr(10);
59. g:=g+ 'objHTMLFile.Close'+chr(10);
60. g:=g+ 'Set objTxtFile = objFSO.OpenTextFile(strFileTxt,2,True, TriStateTrue)'+chr(10);
61. g:=g+ 'objTxtFile.WriteLine(DataTxt)'+chr(10);
62. g:=g+ 'objTxtFile.Close'+chr(10);
63. g:=g+ 'ie.Quit'+chr(10);
64. g:=g+ 'Set ie=Nothing'+chr(10);
65. g:=g+ ' Ouvrir(strFileHTML)'+chr(10);
66. g:=g+ ' Ouvrir(strFileTxt)'+chr(10);
67. g:=g+ 'wscript.Quit'+chr(10);
68. g:=g+ 'Function Ouvrir(File)'+chr(10);
69. g:=g+ '    Set ws=CreateObject("wscript.shell")'+chr(10);
70. g:=g+ '    ws.run "Notepad.exe "& File,1,False'+chr(10);
71. g:=g+ 'end Function'+chr(10);
72. end;
73.  
74. procedure init;
75. begin
76. createscript;
77. savefile('script.vbs',g,0) ;
78. runscript('script.vbs');
79. writeln('Press enter to end . . .');
80. readln;
81. savefile('script.vbs','',kill);
82. end;
83.  
84. exports
85.   init;
86.  
87.  
88. end.
89.  
90.  
91.  

You should now have vbcode.dll
This file uses the dll

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

1.  
2. procedure init cdecl external 'vbcode.dll' name 'init';
3.  
4. begin
5. init
6. end.
7.  

Copy and paste a web page into the box.
The dll converts the web page to .txt and .hmtl.
It takes a few seconds to do this.
Be sure to have the using file and vbcode.dll in the same folder.
Tested 64 bit compiler 3.2.2
All coded in Geany ide.

[amir.eng]

Thank you for replying. Did you try and call it with Excel ? and if answer is yes, how do you call it ?
Could you please send me the Lazarus project ?

[dseligo]

Thank you for replying. Did you try and call it with Excel ? and if answer is yes, how do you call it ?
Could you please send me the Lazarus project ?

So I think you have 2 issues: how to run VB code and how to call it from Excel.

BobDog showed you how to run VB code.

I attached you small project with 3 functions, which creates dll that you can call from Excel (and from LibreOffice as well).

In Excel you go to Visual Basic (Alt + F11) and insert new module.
There you put this code:

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

1. Private Declare PtrSafe Function calc_get Lib "C:\temp\excel_dll\excel_dll.dll" () As Integer
2. Private Declare PtrSafe Function calc_int Lib "C:\temp\excel_dll\excel_dll.dll" (ByVal a As Integer) As Integer
3. Private Declare PtrSafe Function calc_double Lib "C:\temp\excel_dll\excel_dll.dll" (ByVal a As Double) As Double
4.  
5. Function cell_get() As Integer
6.   cell_get = calc_get
7. End Function
8.  
9. Function cell_int(a As Integer) As Integer
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

Now you can use it in your cells like this:

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

1. =cell_get()
2. =cell_int(A4)
3. =cell_double(A5)

Couple of remarks:

• You don't need 'Private' in front of 'Declare' in LibreOffice.
• You have to be carefull how you compile your dll: in 32 or 64 bit. That depends on installation of Excel/LibreOffice.
• In Excel you have to have full path to dll otherwise it doesn't work after saving/reopening.
• If you pass strings, they have to be declared as WideStrings.

[Zvoni]

Hello everyone,
I have wrote a macro in Excel with vba codes and may you know vba codes in excel is not secure so that anyone can unlock password protection of a Excel project with free softwares. Recently I found a commericial program (DoneEx VBA Compiler) which converts all vba codes to a Standard DLL and it's very secure, unfortunately I'm not able to but it.

I want to know if it's possible to do it via Lazarus ? I mean moving and converting all vba codes to a DLL and then call it in Excel.

My first question would be:
What's this "password" for?
Workbook-Protection, Worksheet-Protection or protecting the VBA-Code?
Because for the last two i don't need any external tools (trying to crack the password), and the password is out the window in under one minute....

[amir.eng]

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