Author: Manuel Sarmiento
How do I make delphi functions available to Excel users?
I have seen many articles telling how to control Excel from within Delphi. However,
it is also appealing to give Excel users (which tend to be far less programming
oriented guys) the power of tools built with Dephi, its flexibility and velocity.
Answer:
The idea is very simple and is based upon the variable types that are common to
Excel's VBA and to Delphi. Those include 32 bit integer, double precision floating
point and, mainly, Excel ranges.
I found that Excel sometimes interprets incorrectly simple types when passed by
reference and thus I limmited their usage to value parameters.
On the other hand, ranges can only be passed by reference and can be read from but
not written to. This means that, within Delphi, you must use the reserved word
CONST instead of VAR.
First, I defined within a simple unit a set of functions that convert simple
Variant types to simple types and viceversa. Those are IntToVar,Double and
VarTodouble (the real unit also includes a StrToVar function but not a VarToStr
since this one is already included in the System unit), and are used within the
procedures that do the real work (RangeToMatrix, RangeToVector,VectorToMatrix and
VectortoRange).
All these functions (along with some others that you might find useful) are put
together in a unit called "_Variants" whose source code is copied here (with some
slight modifications).
In the real unit you will find that there fucntions that provide conversion between
Excel ranges and SDL delphi component suite which I have found to be quite useful
(refer to www.lohninger.com).
I shall restrict the examples, however to standard types.
Lets take first a simple function:
This function, called gamma_alfa, takes as input the mean and the variance of a
population and returns the alfa parameter of a gamma distribution.
In Excel's VBA it is declared as
Declare Function gamma_alfa Lib "c:\archivos\del_files\f_auxiliares_delphi" Alias
"gamma_alfa_XL" (ByVal media As Double, ByVal varianza As Double) As Double
note the lib statement that refers to name that the DLL actually has.
note also the ByVal modifiers used for declaring the variables as well as the "as
double" statements.
These mean that both the input and the output will be simple types of type double.
In Delphi, the function is declared as
function gamma_alfa(media, varianza : double) : Double;stdcall;
Note the stdcall at the end of the declaration. This is to ensure that Delphi will
use the Microsoft calling convention
Also note the inconsistency between the delphi function's name and the "alias"
statement in VBA.
This is set in the export clause of the DLL:
1 exports ...,
2 gamma_alfa name 'gamma_alfa_XL',
3 ...;
4
5 //Although irrelevant, the implementation of the function follows:
6
7 implementation
8
9 function gamma_alfa(media, varianza: double): Double; stdcall;
10 begin
11 gamma_alfa := media * media / varianza;
12 end;
Now, let's go to the tough stuff: sending Excel ranges as parameters.
Now, I will make use of a function that gets and returns excel ranges as
parameters:
This function is called gamma_parametros and takes as input an histogram (with
frequencies and class markers) and returns the alfa and beta parameters for a
gamma. Here is its VBA declaration:
Declare Function gamma_parametros Lib "c:\archivos\del_files\f_auxiliares_delphi"
Alias "gamma_parametros_XL" (ByRef marcas_de_clase As Variant, ByRef frecuencias As
Variant) As Variant
Now note hte "Byref" and the as "Variant" types.
In Delphi, the function is declared as follows:
function gamma_parametros_XL(const _marcas_de_clase, _frecuencias: Variant):
Variant;
stdcall;
and is implemented as:
13 function gamma_parametros_XL(const _marcas_de_clase, _frecuencias:
14 Variant): Variant;
15 stdcall;
16 var
17 marcas_de_clase, frecuencias, pars: TVector_;
18 pars_: Variant;
19 begin
20 RangeToVector(_marcas_de_clase, marcas_de_clase);
21 RangeToVector(_frecuencias, frecuencias);
22 pars := gamma_parametros(marcas_de_clase, frecuencias);
23 VectorToRange(pars, pars_);
24 gamma_parametros_XL := pars_;
25 end;
Note that the functions that does the real work is not gamma_parametros_XL but
gamma_parametros. The former only does the job of converting Excel ranges to
TVector_ and viceversa.
the exports clause exports gamma_parametros_XL, since it's the one that is
replicated in the VBA definition, and thus it does not need a 'name' clause.
Here is the implementation of the gamma_parametros function:
26 function gamma_parametros(const marcas_de_clase, frecuencias: TVector_):
27 TVector_;
28 var
29 pars: TVector_;
30 mu, sigmac: double;
31 begin
32 SetLength(pars, 2);
33 mu := media_ponderada(marcas_de_clase, frecuencias);
34 sigmac := varianza_ponderada(marcas_de_clase, frecuencias);
35 pars[0] := gamma_alfa(mu, sigmac);
36 pars[1] := gamma_beta(mu, sigmac);
37 gamma_parametros := pars;
38 end;
39
40 //Here is the listing of the _Variants unit:
41
42 interface
43 uses SysUtils,
44 excel97,
45 vector,
46 matrix,
47 Classes,
48 Dialogs,
49 registry,
50 windows;
51
52 type
53
54 tmatriz = array of array of double;
55 tvector_ = array of double;
56
57 function IntToVar(dato: longint): variant;
58 function DoubleToVar(dato: double): variant;
59
60 function VarToDouble(const dato: variant): double;
61
62 procedure RangeToMatrix(const rango: variant; var matriz: tmatriz);
63 procedure RangeToVector(const rango: variant; var matriz: tvector_);
64 procedure MatrixToRange(const matriz: tmatriz; var rango: variant);
65 procedure VectorToRange(const matriz: tvector_; var rango: variant);
66
67 procedure transpose(var matriz: tmatriz);
68
69 implementation
70
71 function IntToVar(dato: longint): variant;
72 var
73 temp: variant;
74 begin
75 tvardata(temp).vtype := VarInteger;
76 tvardata(temp).Vinteger := dato;
77 IntToVar := temp;
78 end;
79
80 function DoubleToVar(dato: double): variant;
81 var
82 temp: variant;
83 begin
84 tvardata(temp).vtype := VarDouble;
85 tvardata(temp).VDouble := dato;
86 DoubleToVar := temp;
87 end;
88
89 function VarToDouble(const dato: variant): double;
90 var
91 temp: variant;
92 begin
93 try
94 temp := varastype(dato, vardouble);
95 except
96 on EVariantError do
97 begin
98 tvardata(temp).vtype := vardouble;
99 tvardata(temp).vdouble := 0.0;
100 end;
101 end;
102 VarToDouble := tvardata(temp).vdouble;
103 end;
104
105 procedure RangeToMatrix(const rango: variant; var matriz: tmatriz);
106 var
107 Rows, Columns: longint;
108 i, j: longint;
109 begin
110 if ((tvardata(rango).vtype and vararray) = 0) and
111 ((tvardata(rango).vtype and vartypemask) = vardispatch) then
112 begin
113 Rows := Rango.rows.count;
114 Columns := Rango.columns.count;
115 SetLength(matriz, Rows);
116 for i := 0 to Rows - 1 do
117 SetLength(matriz[i], Columns);
118 for i := 0 to Rows - 1 do
119 for J := 0 to Columns - 1 do
120 matriz[i, j] := VarToDouble(Rango.cells[i + 1, j + 1]);
121 end
122 else if ((tvardata(rango).vtype and vararray) <> 0) then
123 begin
124 rows := vararrayhighbound(rango, 1) - vararraylowbound(rango, 1) + 1;
125 if VarArrayDimCount(rango) = 2 then
126 begin
127 columns := vararrayhighbound(rango, 2) - vararraylowbound(rango, 2) + 1;
128 setLength(matriz, rows);
129 for i := 0 to Rows - 1 do
130 SetLength(matriz[i], Columns);
131 for i := 0 to Rows - 1 do
132 for J := 0 to Columns - 1 do
133 matriz[i, j] := vartodouble(rango[i + 1, j + 1]);
134 end
135 else
136 begin
137 setlength(matriz, 1);
138 setlength(matriz[0], rows);
139 for i := 0 to rows - 1 do
140 matriz[0, i] := vartodouble(rango[i + 1]);
141 end;
142 end
143 else
144 begin
145 rows := 1;
146 columns := 1;
147 setLength(matriz, rows);
148 setLength(matriz[0], columns);
149 matriz[0, 0] := vartodouble(rango);
150 end
151 end;
152
153 procedure RangeToVector(const rango: variant; var matriz: tvector_);
154 var
155 Rows, columns: longint;
156 i, j: longint;
157 begin
158 if ((tvardata(rango).vtype and vararray) = 0) and
159 ((tvardata(rango).vtype and vartypemask) = vardispatch) then
160 begin
161 Rows := Rango.count;
162 SetLength(matriz, Rows);
163 for i := 0 to Rows - 1 do
164 matriz[i] := VarToDouble(Rango.cells[i + 1]);
165 end
166 else if ((tvardata(rango).vtype and vararray) <> 0) then
167 begin
168 rows := vararrayhighbound(rango, 1) - vararraylowbound(rango, 1) + 1;
169 if VarArrayDimCount(rango) = 1 then
170 begin
171 setLength(matriz, rows);
172 for i := 0 to rows - 1 do
173 matriz[i] := vartodouble(rango[i + 1]);
174 end
175 else
176 begin
177 columns := vararrayhighbound(rango, 2) - vararraylowbound(rango, 2) + 1;
178 setlength(Matriz, Columns * Rows);
179 for i := 1 to rows do
180 for j := 1 to columns do
181 try
182 matriz[(i - 1) * columns + j] := VarToDouble(rango[i, j]);
183 except
184 on EVariantError do
185 matriz[(i - 1) * columns + j] := 0;
186 end;
187 end
188 end
189 else
190 begin
191 rows := 1;
192 setLength(matriz, rows);
193 matriz[0] := vartodouble(rango);
194 end;
195 end;
196
197 procedure MatrixToRange(const matriz: tmatriz; var rango: variant);
198 var
199 Rows, Columns: longint;
200 i, j: longint;
201 begin
202 Rows := high(matriz) - low(matriz) + 1;
203 Columns := high(matriz[0]) - low(matriz[0]) + 1;
204 rango := VarArrayCreate([1, Rows, 1, Columns], varDouble);
205 for i := 1 to Rows do
206 for j := 1 to Columns do
207 rango[i, j] := matriz[i - 1, j - 1];
208 end;
209
210 procedure VectorToRange(const matriz: tvector_; var rango: variant);
211 var
212 Rows: longint;
213 i: longint;
214 begin
215 Rows := high(matriz) - low(matriz) + 1;
216 rango := VarArrayCreate([1, Rows], varDouble);
217 for i := 1 to Rows do
218 rango[i] := matriz[i - 1];
219 end;
220
221 procedure transpose(var matriz: tmatriz);
222 var
223 Rows, Columns,
224 i, j: longint;
225 temp: double;
226 begin
227 Rows := high(matriz) - low(matriz) + 1;
228 Columns := high(matriz[0]) - low(matriz[0]) + 1;
229 for i := 0 to rows - 1 do
230 for j := i to columns - 1 do
231 begin
232 temp := matriz[i, j];
233 matriz[i, j] := matriz[j, i];
234 matriz[j, i] := temp;
235 end;
236 end;
237
238 end.
One final warning note:
Notice that the types' names in VBA are NOT the same as in Delphi.
The two must obvious are BOOLEAN (which in VBA is a 2 byte type whereas in Delphi
is a one byte type). Thus you MUST use WORDBOOL in Delphi.
The other obvious type is INTEGER (in DElphi is a 4-byte type and in VBA a 2-byte
type). To avoid confussion use LONGINT in Delphi and LONG in VBA
I will be more than glad to send you the full source code of the _Variant unit
|
