Board index » delphi » Calling a delphi dll from excel

Calling a delphi dll from excel

Hi,

I have written some dlls which are used in the calculation of insurance
premiums.  This is fine when they are used by delphi apps,  however I
want to be able to use them in Excel and Notes applications as well.

A typical function might take the form:

function Premium( age : integer; Term : integer; InterestRate : variant;

                           SA : variant;
                              PremiumFrequency : Str2;
                              PerMille : variant;
                              PercentageLoading : variant;
                              Sex : Char;
                              SmokerStatus : char;
                          var EP : Variant ) : variant; stdcall;

by the way,  I use variants primarilly because they seem round more
consistenly than real or double types

How would I declare this function to be used in an excel spreadsheet?

Any help or examples would be of tremendous help

Best regards
Peter Williamson

 

Re:Calling a delphi dll from excel


From Excel Help File Search Call.

Here it is:
The following describes the argument and return value data types used by the
CALL, REGISTER, and REGISTER.ID functions. Arguments and return values differ
slightly depending on your operating environment, and these differences are
noted in the data type table.
The "Microsoft Excel 97 Developer's Kit" contains detailed information about
dynamic link libraries (DLLs) and code resources, the Microsoft Excel
application programming interface (API), file formats, and many other technical
aspects of Microsoft Excel. It also contains code samples and programming tools
that you can use to develop custom applications. To obtain a copy of the
"Microsoft Excel 97 Developer's Kit," contact your software supplier or
Microsoft Press. In the United States, contact Microsoft Press at (800)
677-7377.

Data Types

In the CALL, REGISTER, and REGISTER.ID functions, the type_text argument
specifies the data type of the return value and the data types of all arguments
to the DLL function or code resource. The first character of type_text specifies
the data type of the return value. The remaining characters indicate the data
types of all the arguments. For example, a DLL function that returns a
floating-point number and takes an integer and a floating-point number as
arguments would require "BIB" for the type_text argument.

The following table contains a complete list of the data type codes that
Microsoft Excel recognizes, a description of each data type, how the argument or
return value is passed, and a typical declaration for the data type in the C
programming language.

Code Description Pass by C Declaration
A Logical
(FALSE = 0), TRUE = 1) Value short int
B IEEE 8-byte floating-point number Value
(Windows)Reference (Macintosh) double
(Windows)double * (Macintosh)
C Null-terminated string (maximum string length = 255) Reference char *
D Byte-counted string (first byte contains length of string, maximum string
length = 255 characters) Reference Unsigned char *
E IEEE 8-byte floating-point number Reference double *
F Null-terminated string (maximum string length = 255 characters) Reference
(modify in place) char *
G Byte-counted string (first byte contains length of string, maximum string
length = 255 characters) Reference (modify in place) unsigned char *
H Unsigned 2-byte integer Value unsigned short int
I Signed 2-byte integer Value short int
J Signed 4-byte integer Value long int
K Array Reference FP *
L Logical
(FALSE = 0, TRUE = 1) Reference short int *
M Signed 2-byte integer Reference short int *
N Signed 4-byte integer Reference long int *
O Array Reference Three arguments are passed:
unsigned short int *
unsigned short int *
double [ ]
P Microsoft Excel OPER data structure Reference OPER *
R Microsoft Excel XLOPER data structure Reference XLOPER *
Remarks

The C-language declarations are based on the assumption that your compiler
defaults to 8-byte doubles, 2-byte short integers, and 4-byte long integers.
In the Microsoft Windows programming environment, all pointers are far
pointers. For example, you must declare the D data type code as unsigned char
far * in Microsoft Windows.
All functions in DLLs and code resources are called using the Pascal calling
convention. Most C compilers allow you to use the Pascal calling convention by
adding the Pascal keyword to the function declaration, as shown in the following
example:

pascal void main (rows,columns,a)

If a function uses a pass-by-reference data type for its return value, you can
pass a null pointer as the return value. Microsoft Excel will interpret the null
pointer as the #NUM! error value.

Additional Data Type Information

This section contains detailed information about the F, G, K, O, P, and R data
types and other information about the type_text argument.

F and G Data Types

With the F and G data types, a function can modify a string buffer that is
allocated by Microsoft Excel. If the return value type code is F or G, then
Microsoft Excel ignores the value returned by the function. Instead, Microsoft
Excel searches the list of function arguments for the first corresponding data
type (F or G) and then takes the current contents of the allocated string buffer
as the return value. Microsoft Excel allocates 256 bytes for the argument, so
the function may return a larger string than it received.

K Data Type

The K data type uses a pointer to a variable-size FP structure. You must define
this structure in the DLL or code resource as follows:

typedef struct _FP
{
 unsigned short int rows;
 unsigned short int columns;
 double array[1];  /* Actually, array[rows][columns] */

Quote
} FP;

The declaration double array[1] allocates storage for only a single-element
array. The number of elements in the actual array equals the number of rows
multiplied by the number of columns.

O Data Type

The O data type can be used only as an argument, not as a return value. It
passes three items: a pointer to the number of rows in an array, a pointer to
the number of columns in an array, and a pointer to a two-dimensional array of
floating-point numbers.

Instead of returning a value, a function can modify an array passed by the O
data type. To do this, you can use ">O" as the type_text argument. For more
information, see "Modifying in Place ? Functions Declared as Void" below.

The O data type was created for direct compatibility with Fortran DLLs, which
pass arguments by reference.

P Data Type

The P data type is a pointer to an OPER structure. The OPER structure contains 8
bytes of data, followed by a 2-byte identifier that specifies the type of data.
With the P data type, a DLL function or code resource can take and return any
Microsoft Excel data type.

The OPER structure is defined as follows:

typedef struct _oper

{
 union
 {
  double num;
  unsigned char *str;
  unsigned short int bool;
  unsigned short int err;
  struct
  {
   struct _oper *lparray;
   unsigned short int rows;
   unsigned short int columns;
  } array;
 } val;
 unsigned short int type;

Quote
} OPER;

The type field contains one of these values.

Type Description Val field to use
1 Numeric num
2 String (first byte contains length of string) str
4 Boolean (logical) bool
16 Error: the error values are:  0 #NULL!  7 #DIV/0!  15 #Value!  23 #REF!  29
#NAME?  36 #NUM!  42 #N/A err
64 Array array
128 Missing argument
256 Empty cell
The last two values can be used only as arguments, not return values. The
missing argument value (128) is passed when the caller omits an argument. The
empty cell value (256) is passed when the caller passes a reference to an empty
cell.

R Data Type ? Calling Microsoft Excel Functions from DLLs

The R data type is a pointer to an XLOPER structure, which is an enhanced
version of the OPER structure. In Microsoft Excel version 4.0 and later, you can
use the R data type to write DLLs and code resources that call Microsoft Excel
functions. With the XLOPER structure, a DLL function can pass sheet references
and implement flow control, in addition to passing data. A complete description
of the R data type and the Microsoft Excel application programming interface
(API) is beyond the scope of this topic. The Microsoft Excel Developer's Kit
contains detailed information about the R data type, the Microsoft Excel API,
and many other technical aspects of Microsoft Excel.

Volatile Functions and Recalculation

Microsoft Excel usually calculates a DLL function (or a code resource) only when
it is entered into a cell, when one of its precedents changes, or when the cell
is calculated during a macro. On a worksheet, you can make a DLL function or
code resource volatile, which means that it recalculates every time the
worksheet recalculates. To make a function volatile, add an exclamation point
(!) as the last character in the type_text argument.
For example, in Microsoft Excel for Windows 95 and Microsoft Excel for Windows
NT, the following worksheet formula recalculates every time the worksheet
recalculates:

CALL("Kernel32","GetTickCount","J!")

Modifying in Place ? Functions Declared as Void

You can use a single digit n for the return type code in type_text, where n is a
number from 1 to 9. This tells Microsoft Excel to modify the variable in the
location pointed to by the nth argument in type_text, instead of returning a
value. This is also known as modifying in place. The nth argument must be a
pass-by-reference data type (C, D, E, F, G, K, L, M, N, O, P, or R). The DLL
function or code resource must also be declared with the void keyword in the C
language (or the procedure keyword in the Pascal language).

For example, a DLL function that takes a null-terminated string and two pointers
to integers as arguments can modify the string in place. Use "1FMM" as the
type_text argument, and declare the function as void.

Versions prior to Microsoft Excel 4.0 used the > character to modify the first
argument in place; there was no way to modify any argument other than the first.
The > character is equivalent to n = 1 in Microsoft Excel version 4.0 and later.

Quote
Peter Williamson wrote:
> Hi,

> I have written some dlls which are used in the calculation of insurance
> premiums.  This is fine when they are used by delphi apps,  however I
> want to be able to use them in Excel and Notes applications as well.

> A typical function might take the form:

> function Premium( age : integer; Term : integer; InterestRate : variant;

>                            SA : variant;
>                               PremiumFrequency : Str2;
>                               PerMille : variant;
>                               PercentageLoading : variant;
>                               Sex : Char;
>                               SmokerStatus : char;
>                           var EP : Variant ) : variant; stdcall;

> by the way,  I use variants primarilly because they seem round more
> consistenly than real or double types

> How would I declare this function to be used

...

read more »

Re:Calling a delphi dll from excel


On Thu, 17 Sep 1998 17:04:52 +0800, Peter Williamson

Quote
<p...@pacific.net.sg> wrote:

>by the way,  I use variants primarilly because they seem round more
>consistenly than real or double types

I've written interest calculating software and used Extended types - I
believe it uses a little less memory.
Quote

>How would I declare this function to be used in an excel spreadsheet?

>Any help or examples would be of tremendous help

>Best regards
>Peter Williamson

Other Threads