Using DLLs from Visual Basic

Microsoft Office Technical Documentation Group

Created: November, 1995

Click to open or copy the ADVDLL project files

Using DLL functions from Visual Basic, Applications Edition, in Microsoft Excel is very similar to using DLL functions from Visual Basic. Chapter 26 of the Microsoft Visual Basic Programmer's Guide for version 4.0 of Visual Basic provides detailed guidelines and examples for calling DLL functions from Visual Basic. This article provides more specific information about using DLL functions from Visual Basic in Microsoft Excel.

The examples in this article were prepared and tested with Microsoft Visual C++ version 2.2, and run on Microsoft Windows 95. Visual C++ version 2.2 includes the OLE 2 header files and libraries required to create DLLs using variants, strings, objects, and arrays as described in this article.

You can use another C compiler and linker if they are capable of creating DLLs. In this case, you must also have the Microsoft OLE 2 Software Developer's Kit (SDK) to create DLLs using variants, strings, objects, or arrays.

In addition to the Help provided with Visual C++ and the OLE 2 SDK, Chapter 6 in Volume 2 of the OLE 2 Programmer's Reference provides valuable information about the functions used in the examples in this article.

Rewriting 16-bit DLL Code for Windows 95

Most 16-bit C-language DLL code will not work in Windows 95 without some modifications. The following sections discuss the changes required to run 16-bit DLL and Visual Basic code with Microsoft Excel for Windows 95. For more information about modifying 16-bit Visual Basic code and C-language DLLs, see the article "Porting 16-Bit Office-Based Solutions to 32-Bit Office" and Chapter 28 of the Microsoft Visual Basic Programmer's Guide for version 4.0 of Visual Basic.

Variable Size

In C-language DLLs written for 16-bit Windows, variables declared as integers (with the int data type) occupy 2 bytes (16 bits). In 32-bit Windows, the int data type occupies 4 bytes (32 bits). In both 16- and 32-bit Microsoft Excel VBA code, the Integer data type occupies only 16 bits, so in Microsoft Excel for Windows 95, the Integer and int data types are not equivalent.

The following table shows the data-type equivalencies for both 16- and 32-bit Windows.

If your DLL code uses the int data type with arguments either passed in or returned to Visual Basic code, you must change those arguments to use the short data type.

String Variables and Unicode

One significant difference between 16-bit and 32-bit environments is in how string data is stored and manipulated. Visual Basic in Microsoft Excel uses ANSI (American National Standards Institute) characters (a single byte represents each character) to store and manipulate strings in both 16- and 32-bit versions. Conversely, 32-bit OLE Automation and the 32-bit OLE 2 API use Unicode (two bytes represent each character) to store and manipulate strings.

When you pass string data from Visual Basic in Microsoft Excel, the data uses ANSI characters. You can leave the strings in ANSI format if you are simply passing them back to Microsoft Excel or calling a Windows 95 system function, but you may need Unicode characters if you use OLE Automation or you call an OLE 2 API function. You can use the MultiByteToWideChar function to convert an ANSI string to Unicode, and the WideCharToMultiByte function to convert a Unicode string to ANSI.

In addition, because Microsoft Excel uses ANSI characters in BSTR variables, you cannot use the standard OLE string allocation and reallocation functions (these functions now create Unicode strings). Two new functions, SysAllocStringByteLen and SysStringByteLen, have been added to operate on single-byte character strings.

Windows API Functions

If your Visual Basic code calls a Windows API function, you will need to change the declaration in the Visual Basic code so that the correct system DLL is linked and the correct function is called. The easiest way to change the Visual Basic declaration is to look up the Windows API function in the Win32api.txt file and copy the declaration from that file into your Visual Basic code. For more information about calling 32-bit Windows system functions, see the article "Porting 16-Bit Office-Based Solutions to 32-Bit Office."

Using the Declare Statement

Before you can call a DLL function from Visual Basic, you must use the Declare statement to identify the function, the name of the DLL where it is located, and its argument types. Once the function is declared in a Visual Basic module, you can call it just as if it were part of your code.

For example, the following C-language function calculates the circumference of a circle given the circle's radius:

double __declspec(dllexport) DoubleArg(double dRadius)

{

return dRadius * 2 * 3.14159;

}

In Microsoft Windows 95 and Windows NT, DLL functions called from Microsoft Excel must be declared using the __declspec(dllexport) keywords. In 16-bit Windows, DLL functions use the FAR PASCAL calling convention, and on the Apple Macintosh, code-resource functions use the pascal calling convention. The examples in this article were written to run on Microsoft Windows 95. If you are using 16-bit Windows or the Apple Macintosh, modify the examples accordingly.

This Visual Basic code uses the DoubleArg C function to display a table of circumference values:

Declare Function DoubleArg Lib "advdll32" _

(ByVal radius As Double) As Double

Sub CircumferenceTable()

Worksheets(1).Activate

Cells(1, 1).Value = "Radius"

Cells(1, 2).Value = "Circumference"

For i = 1 To 10

Cells(i + 1, 1).Value = i

Cells(i + 1, 2).Value = DoubleArg(i)

Next

Columns("a:b").AutoFit

End Sub

The Declare statement uses the ByVal keyword because the argument is passed by value. Visual Basic coerces the variable i to type Double even though it is not explicitly declared in the code (with a Dim statement).

The REGISTER Macro Function

The REGISTER macro function and the Declare Visual Basic statement are not equivalent. Using the REGISTER function registers the DLL function only for the macro sheet or the worksheet. You cannot call the function from a Visual Basic module without using the Declare statement, even if you have already used the REGISTER function and called the same DLL function from a macro sheet. You can, however, call the function on a macro sheet by using the ExecuteExcel4Macro method to call the registered DLL function just as you would call any other Microsoft Excel macro function.

Similarly, DLL functions declared as public functions in a Visual Basic module can be called from any Visual Basic module in the workbook, but cannot be called from a macro sheet or worksheet. Public functions are those declared without either the Public or Private keywords (or explicitly declared with the Public keyword). If you declare the DLL function as Private, you can call the function only from the Visual Basic module where it is declared. You must use the REGISTER function before you can call the DLL function from a macro sheet or a worksheet, even if you have already used the Declare statement and called the same DLL function from a Visual Basic module.

To use the same DLL function in Visual Basic, on a macro sheet, and on a worksheet, you must both declare the function in a Visual Basic module and use the REGISTER function on the macro sheet and worksheet.

In addition, the REGISTER and UNREGISTER functions allow you to dynamically load and unload DLL functions. This is not possible in Visual Basic. Once a DLL function is loaded by a Visual Basic module (the first time a declared function in the DLL runs), the DLL remains loaded until you close the workbook that contains the Visual Basic module.

You can use the dynamic loading behavior of the REGISTER and UNREGISTER functions by creating a stub macro on a macro sheet. The stub macro should accept appropriate arguments for the DLL function, register the DLL function, call it, unregister it, and return the return value from the DLL function. You can then use the ExecuteExcel4Macro function to call the stub macro from Visual Basic. The stub macro in turn calls the DLL and returns to Visual Basic.

Passing Arguments by Reference

By default, Visual Basic passes arguments by reference, rather than by value. This means that the DLL function can modify the argument. When an argument is passed by reference, you must declare it as a pointer in the DLL function C-language prototype.

For example, this C-language function modifies its argument by multiplying it by two. The function returns False if the argument is less than zero.

short __declspec(dllexport) PointerArg(short *pn)

{

if (*pn < 0)

return 0; // False in Visual Basic

*pn *= 2;

return -1; // True in Visual Basic

}

The Visual Basic declaration for this function does not include the ByVal keyword. You may include the ByRef keyword, but it isn't necessary.

Declare Function PointerArg Lib "advdll32" _

(d As Integer) As Boolean

Sub TestPointerArg()

Dim n As Integer

n = CInt(InputBox("Number?"))

r = PointerArg(n)

MsgBox n & ":" & r

End Sub

Using Variants

Passing an argument of Variant data type is very similar to passing any other argument type. In the DLL, you can use the VARIANT data structure to access the data contained in the argument. See Chapter 5 in Volume 2 of the OLE 2 Programmer's Reference for descriptions of the VARIANT data type.

The VARIANT type is a C-language structure containing a single member for the variable type, three reserved members, and a large named union that is used to access the variable data depending on the type.

For example, this C-language function determines the data type contained in the VARIANT argument passed by Visual Basic:

short __declspec(dllexport) VariantArg(VARIANT vt)

{

int i;

char *pcBuf;

if (vt.vt == VT_DISPATCH) // variant is an object

return -1;

else if (vt.vt == VT_BSTR) { // variant is a string

i = SysStringLen(vt.bstrVal);

pcBuf = (char *)malloc(i + 1);

WideCharToMultiByte(CP_ACP, 0, vt.bstrVal, i, pcBuf, i, NULL, NULL);

i = atoi(pcBuf);

free(pcBuf);

return (short)i;

}

else if (vt.vt == VT_I2) // variant is an integer

return vt.iVal;

else // variant is something else

return -3;

}

Note When Microsoft Excel passes a Variant variable containing a string to a DLL, the string is in Unicode format. To use this string in functions requiring ANSI characters, you must convert the string using the WideCharToMultiByte function.

This Visual Basic code declares and calls the VariantArg function:

Declare Function VariantArg Lib "advdll32" _

(ByVal v As Variant) As Integer

Sub VariantArgTest()

MsgBox VariantArg(Worksheets(1)) ' -1

MsgBox VariantArg("25") ' 25

MsgBox VariantArg(5) ' 5

MsgBox VariantArg(3.2) ' -3

End Sub

You could use this information to implement a function that accepts either a Range object or a text description of a range. If the argument contains an object, you can use IDispatch to access properties and methods of the object directly. If the argument contains a string, you can use IDispatch to create an object and then access its properties and methods.

Visual Basic in Microsoft Excel does not support all the data types supported by the VARIANT structure. The following table shows the allowed data types and their value constants. Microsoft Excel never returns a Variant of a data type not shown on this list.

Variants and Objects

The VariantArg function shows how you declare and call a DLL function with a Variant passed by value. A Variant passed by reference would be declared in C as a pointer to a VARIANT structure.

void VariantByRef(LPVARIANT *pvar)

While it is possible to pass an object as a Variant, it is also possible to specifically declare a function that accepts only an object. An object is passed as a dispatch pointer, either by value:

void ObjectByVal(LPDISPATCH pdisp)

or by reference:

void ObjectByRef(LPDISPATCH *ppdisp)

When you pass a variant or object by reference, remember that your DLL code must free any allocated object, string, or array before replacing it. Failure to free allocations results in memory leaks. More information about freeing allocated strings and arrays appears in the following sections.

Return Values

When your function returns an object, it is declared as returning a dispatch pointer:

LPDISPATCH ReturnsObject(void)

Returning a Variant is simple:

VARIANT ReturnsVariant(void)

Using Strings

When Visual Basic passes a string by reference to a C-language DLL, it uses a special OLE 2 data type called a BSTR. OLE Automation allows BSTR strings to be allocated and freed by any component that supports this data type.

In most cases, a BSTR can be treated like a pointer to a null-terminated string. In general, it's best if your C-language code does not directly manipulate the string data. You can dereference the pointer to copy data from the BSTR, however.

Instead of directly manipulating BSTR data, OLE Automation provides several functions that should be used to allocate, free, and reallocate BSTR values. These functions are listed in Chapter 6 in Volume 2 of the OLE 2 Programmer's Reference.

Note Because Microsoft Excel uses ANSI characters in BSTR variables, you cannot use the standard OLE string allocation and reallocation functions (these functions expect Unicode strings). Two new functions, SysAllocStringByteLen and SysStringByteLen, have been added to operate on single-byte character strings.

When you pass a string by reference, your C-language function should declare the argument as a pointer to a BSTR. The pointer will never be NULL, but if the Visual Basic string is unassigned (that is, created with the Dim statement but not assigned a value), the BSTR pointed to will be NULL. If the string is assigned but empty, the first character will be a null character, and the string length will be zero.

The pointer may also reference a NULL pointer if the original variable was created as a Variant type, but not assigned a value. Visual Basic would coerce the variant to a string when it called the DLL, but because the variant is empty, it behaves like a declared but unassigned string.

The following code example tests for these conditions:

short __declspec(dllexport) SType(BSTR *pbstr)

{

if (pbstr == NULL) // pointer is null; will never happen

return 1;

if (*pbstr == NULL) // string (or variant) is allocated by VB

// with Dim statement,

// but not yet assigned

return 2;

if (*pbstr[0] == 0) // string is allocated

// and assigned to empty string ("")

return 3;

// string has a value; ANSI characters can be accessed at *pbstr

return 4;

}

This Visual Basic code declares and calls the SType function:

Declare Function SType Lib "advdll32" _

(s As String) As Integer

Sub STypeTest()

Dim s As String

MsgBox SType(s) 'displays 2

s = ""

MsgBox SType(s) 'displays 3

s = "test"

MsgBox SType(s) 'displays 4

End Sub

Allocating BSTR Values

You should always use OLE functions to operate on BSTR values. If you need to change a BSTR value, first test the BSTR to see if it is already assigned. If it isn't, you may use the SysAllocStringByteLen function to assign a value to the BSTR. If the BSTR is already assigned, you must free the current assignment (with the SysFreeString function) before you can use SysAllocStringByteLen.

The following C-language code copies some number of characters from one BSTR into another. Notice that this example tests the second BSTR to see if it is already assigned. If it is, the example uses the SysFreeString function to free the existing string before replacing it.

short __declspec(dllexport) StringArgs(BSTR *pbstrArg1,

BSTR *pbstrArg2, int cch)

{

// return error code if requested characters

// less than zero, or input string is unassigned

// or has too few characters

if (cch < 0 || *pbstrArg1 == NULL ||

(int)SysStringByteLen(*pbstrArg1) < cch)

return -1;

if (*pbstrArg2 != NULL) { // argument string is already assigned;

// we must free before allocating

SysFreeString(*pbstrArg2);

}

if ((*pbstrArg2 = SysAllocStringByteLen((char *)*pbstrArg1, cch))

== NULL)

return -2;

return 0;

}

This Visual Basic code declares and calls the StringArgs function:

Declare Function StringArgs Lib "advdll32" _

(inpStr As String, outStr As String, _

ByVal n As Integer) As Integer

Sub StringArgsTest()

Dim newStr As String

x = StringArgs("abracadabra", newStr, 5)

MsgBox x & ":" & newStr

End Sub

Using User-Defined Data Structures

The Type statement in Visual Basic can be used to create user-defined data structures. For example, the following Visual Basic data type and C-language structure are equivalent.

In Visual Basic:

Type ARG

i as Integer

str as String

End Type

In C:

typedef struct {

int i;

BSTR str;

} ARG;

User-defined data types cannot be passed by value; they must be passed by reference. Your C function should declare the argument as a pointer to the structure. If the structure contains BSTR values (as this example does) the rules discussed above apply to those values; you must test the BSTR before you reassign it (and free it if it is already allocated).

For example, this C-language function fills a structure with a string and the length of the string:

short __declspec(dllexport) StructArg(ARG *parg, char *szArg)

{

unsigned int cch;

BSTR bstr;

if (parg == NULL)

return -1;

// allocate a local string first; if this fails,

// we have not touched the passed-in string

cch = strlen(szArg);

if ((bstr = SysAllocStringByteLen(szArg, cch)) == NULL)

return -1;

if (parg->bstr != NULL) // string is already assigned

SysFreeString(parg->bstr);

parg->s = (short)cch;

parg->bstr = bstr;

return parg->s;

}

Declared and called from Visual Basic:

Declare Function StructArg Lib "advdll32" _

(a As ARG, ByVal s As String) As Integer

Sub StructArgTest()

Dim x As ARG

MsgBox StructArg(x, "abracadabra")

MsgBox x.str & ":" & x.i 'displays string and length

End Sub

Using Arrays

OLE 2 provides a special data type for arrays passed from Visual Basic to a DLL. This data type, called a SAFEARRAY, allows both Visual Basic and the DLL to allocate, free, and access array data in a controlled way.

Your DLL should always use OLE Automation functions to allocate and access safe arrays. These functions are described in Chapter 6 in Volume 2 of the OLE 2 Programmer's Reference. When OLE Automation passes a safe array to your DLL, you receive a pointer to a pointer to the array itself. Like BSTR pointers, a SAFEARRAY pointer may point to a NULL array if the array has been declared but not yet dimensioned:

Dim a() as Integer

The pointer itself will never be NULL, however.

The following example determines the upper and lower bounds of an array and then loops through the array producing the sum of the elements in the array:

short __declspec(dllexport) SumArray(

FPSAFEARRAY *ppsa, long *plResult)

{

short sElem;

long lLb, lUb, l, lResult;

if (*ppsa == NULL) // array has not been initialized

return -4;

if ((*ppsa)->cDims != 1) // check number of dimensions

return -5;

// get the upper and lower bounds of the array

if (FAILED(SafeArrayGetLBound(*ppsa, 1, &lLb)) ||

FAILED(SafeArrayGetUBound(*ppsa, 1, &lUb)))

return -1;

// loop through the array and add the elements

for (l = lLb, lResult = 0; l <= lUb; l++) {

if (FAILED(SafeArrayGetElement(*ppsa, &l, &sElem)))

return -2;

lResult += sElem;

}

*plResult = lResult;

return 0;

}

Declared and called from Visual Basic:

Declare Function SumArray Lib "advdll32" _

(a() As Integer, r As Long) As Integer

Sub SumArrayTest()

Dim n(5) As Integer

Dim result As Long

For i = 0 To 5

n(i) = 2

Next

x = SumArray(n, result)

MsgBox x & ":" & result

End Sub

Visual Basic does minimal type checking and enforcement on array element size. Because this function was declared as accepting only an array of integers, it is safe to use an integer element in the call to the SafeArrayGetElement function in the DLL. If the function was declared as accepting an array of any type, however, the Visual Basic code might pass an array of long values; in this case, the C-language function would produce incorrect results. If your DLL function must accept an array of any type, you should use an array of variants and check the variant type in the DLL.

Static Arrays

If you declare and dimension an array:

Dim x(5) as String

The pointer you receive in your C function points to a static array (the fFeatures element of the SAFEARRAY structure has the FADF_STATIC flag set). You can read and redimension the array, but you cannot modify the pointer, and you cannot copy over the array. If you need to modify the array in the DLL (to create a new one, for example), you must not dimension it in Visual Basic.

Allocating Arrays

Your DLL can create new safe arrays or change the size of existing arrays. When you create a new array, you should use a local variable rather than modify a passed-in array pointer. Once the allocation and any subsequent operations on the array are successful, you can assign the passed-in pointer to the local pointer and return from the function.

The following example accepts an array pointer and creates an array containing 10 integer elements. If the passed-in array has already been initialized, the function redimensions the existing array.

short __declspec(dllexport) NewArray(FPSAFEARRAY *ppsa)

{

SAFEARRAY *psa;

SAFEARRAYBOUND sa;

sa.lLbound = 1;

sa.cElements = 10;

if (*ppsa == NULL) { //array not yet initialized

if ((psa = SafeArrayCreate(VT_I2, 1, &sa)) == NULL)

return -2;

*ppsa = psa;

}

else {

if ((*ppsa)->cDims != 1) // check array dimensions

return -1; // fail if multi-dimension

if (FAILED(SafeArrayRedim(*ppsa, &sa)))

return -3;

}

return 0;

}

Declared and called from Visual Basic:

Declare Function NewArray Lib "advdll32" _

(a() As Integer) As Integer

Sub NewArrayTest()

Dim a(12) As Integer

MsgBox NewArray(a) & ":" & LBound(a) & ":" & UBound(a)

End Sub

Destroying Arrays

Like BSTRs, SAFEARRAYs must be freed before they can be destroyed. When Visual Basic passes an existing array to your DLL, it is passed as a static array that cannot be destroyed. Your DLL can create its own arrays, however. When you no longer need an array, it should be destroyed.

You must be careful when you destroy an existing array. Freeing the array frees only the array memory; if the array contains any pointers (such as BSTR values) these will not be freed. In this case, you must free each BSTR element individually and then free the array. If you do not know what the array contains, you should probably not free or redimension the array.

Using Arrays of Strings

Safe arrays can contain elements of any allowable type, including BSTRs and user-defined data structures. BSTRs inside arrays should be manipulated with the same OLE Automation functions as ordinary BSTRs. Always remember to free any existing BSTR before allocating a new one, or use the reallocation function.

The following example creates or redimensions an array of strings. Any existing BSTR data in the array is freed before new data is copied into the array.

short __declspec(dllexport) StringArray(FPSAFEARRAY *ppsa)

{

LONG l;

BSTR bstr;

SAFEARRAY *psa;

SAFEARRAYBOUND sa;

sa.lLbound = 0;

sa.cElements = 3;

if ((*ppsa)->cDims != 1) // check array dimensions

return -1;

if (*ppsa == NULL) { // array not yet initialized

if ((psa = SafeArrayCreate(VT_BSTR, 1, &sa)) == NULL)

return -2;

*ppsa = psa;

}

else if (FAILED(SafeArrayRedim(*ppsa, &sa)))

return -3;

// loop through the array; get each element and free

// any existing string, then allocate the new string

// and put it in the array

for (l = sa.lLbound; l < (long)sa.cElements; l++) {

if (FAILED(SafeArrayGetElement(*ppsa, &l, &bstr)))

return -4;

SysFreeString(bstr);

if ((bstr = SysAllocStringByteLen("test string", 11)) == NULL)

return -5;

if (FAILED(SafeArrayPutElement(*ppsa, &l, bstr)))

return -6;

}

return 0;

}

Declared and called from Visual Basic:

Declare Function StringArray Lib "advdll32" _

(s() As String) As Integer

Sub StringArrayTest()

Dim s(5) As String

MsgBox StringArray(s) & ":" & s(1)

End Sub

'start NewArray

Sub NewArrayTest()

Dim a(12) As Integer

MsgBox NewArray(a) & ":" & LBound(a) & ":" & UBound(a)

End Sub

Using Arrays of Data Structures

You cannot use the SafeArrayCreate function to create a new array of user-defined structures. Instead, you must use the SafeArrayAllocDescriptor function to create the array descriptor, then use the SafeArrayAllocData function to allocate space for the array data, and finally use the SafeArrayAccessData function to return a pointer to the data. The SafeArrayAccessData function locks the array data; when you are done with the array, you should call the SafeArrayUnaccessData function to unlock it.

You cannot replace an existing array, so if your Visual Basic code passes a dimensioned array you must redimension it. Remember to free any existing BSTR pointers in the array before overwriting them.

The following example creates or redimensions an array of data structures then copies an array of strings into the structures, adding the string length to each structure. Any existing BSTR data in the array is freed before new data is copied into the array.

short __declspec(dllexport) StructArray(FPSAFEARRAY *ppsaArg,

FPSAFEARRAY *ppsaStr)

{

ARG *parg;

SAFEARRAY *psa;

BSTR *pbstr;

ULONG i, cElements;

if (*ppsaStr == NULL)

return -1;

cElements = (*ppsaStr)->rgsabound[0].cElements;

if (*ppsaArg == NULL) { // create a new array

if (FAILED(SafeArrayAllocDescriptor(1, &psa)))

return -3;

// set up the SAFEARRAY structure

// and allocate data space

psa->fFeatures = 0;

psa->cbElements = sizeof(ARG);

psa->rgsabound[0].cElements = cElements;

psa->rgsabound[0].lLbound = (*ppsaStr)->rgsabound[0].lLbound;

if (FAILED(SafeArrayAllocData(psa))) {

SafeArrayDestroyDescriptor(psa);

return -4;

}

// get a pointer to the new data

if (FAILED(SafeArrayAccessData(psa,

(void **)&parg))) {

SafeArrayDestroy(psa);

return -5;

}

}

else { // redimension the old array

if (FAILED(SafeArrayRedim(*ppsaArg,

(*ppsaStr)->rgsabound)))

return -6;

// get a pointer to the old data

if (FAILED(SafeArrayAccessData(*ppsaArg,

(void **)&parg)))

return -7;

}

// get a pointer to the string array data

if (FAILED(SafeArrayAccessData(*ppsaStr,

(void **)&pbstr)))

return -8;

// allocate strings in the structure array and

// fill them with strings from the string array.

// free any old BSTRs in the structure

for (i = 0; i < cElements; i++) {

SysFreeString(parg[i].bstr);

parg[i].s = strlen((char *)pbstr[i]);

parg[i].bstr =

SysAllocStringByteLen((char *)pbstr[i], parg[i].s);

}

// release pointers and move the structure

// array pointer to the new array if we created one

SafeArrayUnaccessData(*ppsaStr);

if (*ppsaArg == NULL) {

SafeArrayUnaccessData(psa);

*ppsaArg = psa;

}

else

SafeArrayUnaccessData(*ppsaArg);

return 0;

}

Declared and called from Visual Basic:

Declare Function StructArray Lib "advdll32" _

(x() As ARG, s() As String) As Integer

Sub StructArrayTest()

Dim x() As ARG

Dim s(1 To 4) As String

s(1) = "yellow"

s(2) = "orange"

s(3) = "blue"

s(4) = "green"

n = StructArray(x, s)

If n = 0 Then

Worksheets(1).Activate

Range("a1:c25").Clear

For i = LBound(x) To UBound(x)

Cells(i + 1, 1) = i

Cells(i + 1, 2) = x(i).str

Cells(i + 1, 3) = x(i).i

Next

Else

MsgBox "StructArray failed, returned" & n

End If

End Sub

Using the Microsoft Excel C API and Visual Basic

When you call a DLL function from Visual Basic in Microsoft Excel, the DLL function can use the Microsoft Excel C Applications Programming Interface (C API) functions to call back into Microsoft Excel. You cannot use the C API across processes, however. The DLL using the C API must be called from Visual Basic running in Microsoft Excel (a DLL called from Microsoft Project or Microsoft Visual Basic Professional cannot call the C API). In addition, the C API can only be called after Microsoft Excel has called the DLL. This qualification is met when Visual Basic in Microsoft Excel calls the DLL, and the DLL in turn calls the C API. When the DLL function is called by an external event (such as a DDE command from another application or a Windows timer), the DLL function cannot call the C API.

Type Libraries

You can create a type library for your DLL. The type library can contain constant and function declarations and other information about the DLL. Once you have created a type library, you can use the References command on the Tools menu in Microsoft Excel to load the type library. Once loaded, the constant and function declarations are available in Visual Basic without using Declare statements.

Visual Basic in Microsoft Excel does not support user-defined data-structure declarations in type libraries (user-defined data structures are those declared with the Visual Basic Type statement or the C/C++ struct statement). To use the structures in your Visual Basic code, you will have to add their declarations to the module.

For more information about creating type libraries, see Chapter 7 in Volume 2 of the OLE 2 Programmer's Reference.