Template:IncompleteNotification

This page has been marked as requiring review Notes: No comments have been left concerning this page's review status. If this page has been reviewed please remove the {{REVIEW}} template from the page's source markup.

This page has been marked as incomplete Notes: A large portion of this content is not yet entered - I've put headings in place for some things I can think of that still need to be written. If this page has been completed please remove the {{INCOMPLETE}} template from the page's source markup.

APIs (Application Programming Interfaces) are used to access functions in external libraries such as DLLs, OCXs and LIBs. They are an extremely powerful tool that allows us to tap into programming power that VBA in itself cannot reach. The most widely used APIs are the Win32 APIs, allowing us to do things such as use the windows common controls and dialogs (Open/Save Files, Browse Folders, get Temp Paths and Temp File Names), interact with the Spooling (printing) system, and intercept and act upon messages that the operating system sends and receives (hooking).

There is a nearly endless list of things that we can do with the correct APIs. However, it is well to note that because APIs can give us pogrammatic access to system functions, we must be careful with what we do, as it is not impossible for us to make system changes that may render the system unusable.

The API Delcaration

To use an API Function, we must declare them in the declarations section of a module. The Declare statement tells VBA that the function we refer to is contained within an external file, and the other information tells what, where and how it is to access and interact with the external procedure.

The basic syntax of an API Declaration is as follows:

The above shows a bare-minimum declaration for an external function. The complete syntax is:

Here are some examples:

Functions vs. Subs

We should always consult the documentation for an API before declaring them. One of the reasons to do so is to determine what the return type (if any) of the API is. If there is no return type, declare the procedure as a Sub, otherwise declare it as a Function. In the case of the Sleep API, we can look at the Windows Documentation for the Sleep API and make note of two pieces of information: 1) the VOID return under the syntax heading, and under the Return Value heading where it states that this function does not return a value.

In many languages, there are no such things as Subs (as we know them in VB/VBA). However, when calling from VBA, we must make the decision to differentiate between the two based on the presence of a return type in the API documentation.

Lib

The Lib portion of the declaration is required and tells VBA what file the function resides in. In most cases, the external library will be registered, so we need only put the file name itself, without a valid path. In some cases, we can omit the extension as well. If the library is not registered, we must provide the full path and filename (including extension) for the Lib statement.

Alias

The Alias is the actual name of the function as stored in the library we are accessing. When we declare the function, we can name the function whatever we like, but if we name the function anything other than what the library "exports" it as, we are required to give the alias (you will notice that if we name the function itself and the alias the same, the alias will be automatically removed).

Example:

Then, from within our code, we would call the Sleep API by calling our defined MakeMyCodeSleep function name.

Many Win32 APIs that deal with strings will have two different functions to do the same thing, one for unicode handling, and one for ANSI handling. In these cases the function name is suffixed with an "A" or "W" to denote the actual function we will call (see the GetFile example above). In the GetOpenFileName Documentation, we can see under the Requirements table the function names for Unicode and ANSI which we must refer to.

In all cases, we should consult the API documentation to obtain the actual function name that we must refer to. With VBA, in most cases we would refer to the ANSI version of functions that give us a choice (such as the case with GetOpenFileNameA).

Mapping Datatypes between VB and C (Windows)

One of the more difficult aspects of API programming is knowing what datatypes to use. When we consult the documention, we see that there are many datatypes used by the Win32 API. This can be quite daunting, but luckily we can sort this out well enough with a little bit of research.

Windows vs. C datatypes

Ultimately, what we need to find out to map the datatypes correct is two things: a) the number of bytes of memory that a given datatype requires, and b) the valid range that the datatype can hold.

We will do this by first examining the Windows datatypes required per the API documentation, tracing them to the native C datatype, and then comparing the characteristics of that C datatype to those of VB/VBA to find the one that will work.

Windows Data Types

Windows Datatypes

When we look at an Win32 API Documentation, we see Windows datatypes. Windows has a great many datatypes, but luckily all of them are derived from the small handful of C datatypes. Interestingly enough, Windows has many datatypes that are named different but are actually no different from each other aside from the name. As we (VB Developers) are accustom to naming variables to represent certain things, so has Microsoft done with their Datatypes.

Consider the following datatypes numeric Windows datatypes and their descriptions:
LONG - 32 Bit Signed Integer
LONG32 - 32 Bit Signed Integer
BOOL - 32 Bit Signed Integer
INT - 32 Bit Signed Integer
INT32 - 32 Bit Signed Integer

Or the following datatypes for Handles:
HHOOK - Handle to a Hook Procedure
HICON - Handle to an Icon
HFONT - Handle to a Font
HFILE - Handle to a File

All of the "Handle" datatypes list above have a datatype that is derived from the Windows type "HANDLE". As it turns out, the Windows type HANDLE is just a 32 bit signed integer (or, in VBA terms, a Long Integer).

Understanding what Windows has done here will go a long way towards showing us what will be required to map these to VBA datatypes. First, we consult the reference, we find the Datatype in question, we look at the Typedef, we trace it back to either a) another windows type and repeat, or b) the native C type. Then we take the native C type and map it to our VBA type.

TypeDefs

In the C world, we have what's called a typedef. As it's name implies, this is used to define a custom datatype. At the base of every Windows datatype ultimately lies a typedef, from which we can see what the actual native C type is.

Here is the syntax for a typedef:

The second line above takes the C type of int and says that we can now declare variables or functions as BOOL. The Third line gives example of deriving one typedef from another.

C Data Types

If the information in the Windows Data Types link does not give us the size and range information required to make a final judgement on the correct VB mapping, we must look to the native C type for details.

Datatype Ranges for C++

This link gives details on all fundemental C++ datatypes from which the Windows datatypes will be derived.

Note that the int and unsigned int datatypes say "system dependant" for the range and do not give a size in bytes. This will vary based on the number of bits that the operating system runs on. For 32 bit operating systems, the follow applies:

int: 4bytes, range -2147483648 to 2147483647
unsigned int: 4bytes, range 0 to 4294967295

Numeric Values

Signed Numbers

Unsigned Numbers

Boolean Values

Pointers

Strings

Passing Arguments to an API

ByVal vs. ByRef

Buffering Strings

Types and Structures

Reading Returns from an API

Function Return Values

ByRef and Pointer Argument Returns

Resources

Windows Data Types
Datatype Ranges for C++
Fundamental Types, C++

This template has been removed. Please use the {{Article}} instead. For the original version, the {{ArticleOld}} may still be intact.