Delphi's DLL and its application in Excel VBA
Zhanshan Dong
Introduction
Delphi is a powerful programming environment and language. It not only can
generate Windows application but also Windows Dynamically link library (DLL)
and more. Since Delphi already provide a lot of excellent functions and procedures,
we can easily write a complicated DLL with advanced functionality.
VBA is a powerful programming tool in Microsoft Office. Excel is a convenient
tool to process data. Incorporation of VBA program in Excel VBA provide costomizing
opportunities when you use Excel. However VBA is not a compiled language and
all code is interpreted during execution of the program. It is very slow when
VBA program getting big and dealing with a complicated problem. One solution
is writing program in other compiled language and call the procedures or functions
in VBA or Excel spreadsheets. In the past, C++ and C are used to solve the problem.
A DLL file can be generated in C/C++ and called in VBA. Meanwhile Delphi provide
the functionality to generate DLL, can we just write program in Delphi and called
in VBA? The answer is yes. I will explain how to create a simple Delphi DLL
project and demonstrate how to call the function of the DLL file provided.
DELPHI DLL
Use DELPHI 5.0 and above version to generate a DELPHI Windows Dynamic Link
Library project and then create a new unit.
And add a simple function called AddLong to the unit. Then modeify the project
file as in the example. See the project and unit source code in the source code
files.
library Project1;
{ Important note about DLL memory management: ShareMem must be the
first unit in your library's USES clause AND your project's (select
Project-View Source) USES clause if your DLL exports any procedures or
functions that pass strings as parameters or function results. This
applies to all strings passed to and from your DLL--even those that
are nested in records and classes. ShareMem is the interface unit to
the BORLNDMM.DLL shared memory manager, which must be deployed along
with your DLL. To avoid using BORLNDMM.DLL, pass string information
using PChar or ShortString parameters. }
uses
SysUtils,
Classes,
Unit1 in 'Unit1.pas';
{$R *.res}
exports
AddLong;
begin
end.
|
unit Unit1;
interface
function AddLong (a: longint; b: longint) : longint; stdcall;
implementation
function AddLong(a: longint; b:longint) : longint; stdcall;
begin
AddLong := a + b;
end;
end.
|
EXCEL VBA module
To use the function in DLL in VBA, you have to declare the external function
in VBA module first. Since it is a public funtion, you can use it in the spreadsheets
after you declare it. The code is pretty simple as the following:
Option Explicit
Public Declare Function AddLong Lib "c:\project1.dll" _
(ByVal a As Long, ByVal b As Long) As Long
Public Function al(a As Long, b As Long) As Long
al = AddLong(a, b)
End Function
|
[Download Delphi DLL example project and Excel spreadsheet]
|
