Accessing and calling DLLs from VBA: passing parameters byRef and byVal
This is a test that hopefully demonstrates how values can be passed to and from a DLL using byRef and byVal.
The function diff_double takes two doubles, displays them in a message box and calculates their difference and returns it.
In VBA, the doubles that are passed to the function need to be passed with the byVal option.
The function swap_double_ptr takes to pointers to doubles, hence their values must be passsed to the function with the byRef option.
Since the arguments are passed by reference, the function can modify their value. This is what the function does: it swaps their values.
The function to_upper_char_ptr takes a pointer to a char. Thus a string can be passed to this function. Although the function expects a pointer, in the special case of strings, they need to be passed with byVal anyway.
The function bits_in_long shows which bits are set in a long. I needed that to verify that a VBA constant like &h80000051 has the highest most bit set when assigned to a longdatatype.
The functions (also subs) that VBA calls need to be declared in the VBA source code with the declare statement.
This statement identifies the physical location of the DLL, the entry points (functions) and calling conventions (byVal vs byRef).
The source code for the DLL
//
// gcc -c dll.c
// gcc -shared dll.o -o the.dll -Wl,--add-stdcall-alias
//
#include <stdio.h>
#include <windows.h>
__declspec(dllexport) void __stdcall swap_double_ptr(double *a, double *b) {
char buf[200];
double t;
sprintf(buf, "a = %f, b = %f", *a, *b);
MessageBox(0, buf, "swap_double_ptr", 0);
t = *a;
*a = *b;
*b = t;
}
__declspec(dllexport) double __stdcall diff_double(double a, double b) {
char buf[200];
sprintf(buf, "a = %f, b = %f", a, b);
MessageBox(0, buf, "diff_double", 0);
return a - b;
}
__declspec(dllexport) void __stdcall to_upper_char_ptr(char* p) {
while (*p) {
*p = toupper(*p);
p++;
}
}
__declspec(dllexport) void __stdcall bits_in_long(signed long l) {
char buf[200];
int i, p;
if (sizeof(long) != 4) {
MessageBox(0, "sizeof(long) != 4", 0, 0);
return;
}
p = 32 + 6 + 3;
buf[p+1] = 0;
for (i = 0; i<32; i++) {
if (i > 0 && ! (i % 8)) {
buf[p--] = ' ';
buf[p--] = ' ';
}
else if (i > 0 && ! (i % 4) ) {
buf[p--] = ' ';
}
buf[p--] = (l & (1 << i)) ? 'X' : '_';
}
MessageBox(0, buf, "Bits in long", 0);
}
Note that either the DLL needs to be in the PATHenvironment variable or that the path to the DLL needs to be explicitly stated in the declare statement.
option explicit
declare sub swap_double_ptr _
lib "c:\github\VBA-calls-DLL\byRef-byVal\the.dll" ( _
byRef a as double, _
byRef b as double _
)
declare function diff_double _
lib "c:\github\VBA-calls-DLL\byRef-byVal\the.dll" ( _
byVal a as double, _
byVal b as double _
) as double
declare sub to_upper_char_ptr _
lib "c:\github\VBA-calls-DLL\byRef-byVal\the.dll" ( _
byVal p as string _
)
declare sub bits_in_long _
lib "c:\github\VBA-calls-DLL\byRef-byVal\the.dll" ( _
byVal r as long _
)
sub main()
dim a as double
dim b as double
dim d as double
a = 123.45
b = 12.34
d = diff_double(a, b)
msgBox "d = " & d
call swap_double_ptr(a, b)
msgBox "After swap: a = " & a & ", b = " & b
dim str as string
str = "Foo Bar Baz"
to_upper_char_ptr str
msgBox str
dim l as long
l = &h80000051
call bits_in_long(l)
end sub