UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> API    

Image:NotifWarningWeak.gif This page has been marked as incomplete

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.
Image:NotifInfo.gif This page has been marked as requiring review

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.

Related Content:
    API Declarations Index (VBA7)
    Compiler Directives

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 programmatic 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.

Note that we have to take in consideration of bitness of architecture with 64-bit architecture becoming more and more mainstream. Though at the time of writing, it is widely recommended that 32-bit Office continue to be the default installation and should be choosen to maintain compatibility, it behooves the developer to be aware of changes that will be required to eventually migrate into 64-bit architecture.


API Programming Overview

Programming the Win32 or Win64 API takes a different approach than programming standard VBA. The WinAPI is written in the C programming language, thus the techniques and overall mindset when API programming from VBA will tend to integrate with some basic C-Style programming techniques.

Use of Structs (User Defined Types)

Likely the most noticable technique that differs from VBA when working with the WinAPI is the extensive use of Structures (called Structs in C, and User Defined Types (UDTs) in VBA). A Struct/UDT is a set amount of variables of any type contained within a single variable name. See the User Defined Types article for further details on understanding how and why to use UDTs in VBA.

The WinAPI often requires that Structs/UDTs are passed as an argument to functions, thus we will often see requirement to declare UDTs from within our VBA project to pass along to the WinAPI function.

Data Type Mapping

The other major shift in approach when working with the WinAPI is in working with Data Types. In VBA, the Data Types are few and simple. In C, the datatypes are not few, though the MSDN lays them out well for us (see: Windows Data Types (MSDN)).

Details on Data Types between VBA and C are covered in the #Mapping Datatypes between VB and C (Windows) section of this article.

The Overall Approach

A common approach to a WinAPI programming tasks usually consists of three parts:

  1. Defining the task to be completed and the API resources to do the task
  2. Declaring the Function/Struct variables required for the VBA/API interaction
  3. Writing the VBA code that will interact with the API functions to do the task(s) required.

WinAPI Resources

The Microsoft Developers Network (MSDN) contains complete documentation for all of it's published functions and structures and often has articles describing overview information and how-to for specific tasks. At the outset of a WinAPI programming venture, we often spend a lot of time reading through the MSDN to obtain overview information on how to approach the task, as well as detailed declaration and usage information for specific functions and structures, supported versions and much more.

Declaring Functions and Structs

After an overview and map of the required API tasks has been obtained, we then declare the functions and structures as required. The MSDN contains complete information on the required declarations. (typing the function name into google usually returns the official MSDN Documentation for that function as the first search result). This documentation is given in the C format, and will have to be converted to VBA format. See #The API Declaration section of this article for more detailed information, and the API Table of Contents for example reference on some common API functions.

Writing the VBA Code

After the task map has been created and the declarations are provided as required, then we begind work in our actual VBA project, writing more VBA conventional code that interacts with both the VBA project and the API functions.

The API Declaration

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: (for 32-bit only)

Declare Function MyFunction Lib "MyLib.dll" () As Long

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

[Public|Private] Declare [Function|Sub] [FunctionName] Lib "[Library]" Alias "[Alias]" ([arglist]) As [Type]

Here are some examples:

Private Declare Function GetFile _
 Lib "comdlg32.dll" _
 Alias "GetOpenFileNameA" _
 As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private Declare Function MoveWindow _
 Lib "user32" _
 (ByVal hWnd As Long, _
  ByVal x As Long, _
  ByVal Y As Long, _
  ByVal nWidth As Long, _
  ByVal nHeight As Long, _
  ByVal bRepaint As Long _
 ) As Long

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.


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.


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 (32-bit only):

Public Declare Sub MakeMyCodeSleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

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, we need to know two things in order to correctly map the datatypes: 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.

One major concept we need to address is the concept of pointers. In Visual Basic, we work at higher level and thus never need to worry about a pointer but we are in fact using them all times. Every time we open a recordset, reference a form or control, or create a new instance of a class, we are actually creating pointer. Rather, Visual Basic is creating them for us behind the scenes. When we use APIs, we must deal with pointers; Visual Basic will help with some aspects but not all and because Visual Basic is incapable of reading our mind, it doesn't always know what we actually want to do with an API function. We will examine the ramifications more in the Using pointers or actual data type section.

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
LONG_PTR On 32-bit architecture, 32 Bit Signed Integer; On 64-bit architecture, 64-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 a pointer data type. Several of "objects" in C are represented as a pointer of some type. In VBA versions prior to 7 (e.g. Access 2007 and prior), there were no true pointer data type so developers used integer data types which would work fine as long we didn't move the code from one architecture to other. For long time, pointer data types were defined in VBA as a Long data type because we would be on a 32-bit architecture and it happened that the VBA Long data type is also 32-bit. With VBA7 (introduced with Access 2010), we have a new data type, LongPtr which is a true pointer data type. On a 32-bit architecture, LongPtr is same as a 32-bit Long, but on 64-bit architecture, LongPtr is big as LongLong, or 64-bit integer. Thus, LongPtr is more portable and is in fact required when we are declaring APIs for 64-bit Access. At time of writing, Access 2010 is only version to come out in 64-bit. Note that we can run 32-bit Access on 64-bit OS but for practical purposes, 32-bit Access uses 32-bit APIs, irrespective of what bitness of the OS it is running in.

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.


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:

typedef [base type] [new type];
typedef int BOOL;

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



Passing Arguments to an API

If you've looked at different examples using same APIs, you may have noticed that some examples uses different signature from one other, which can be confusing. One reason is that we do have some leeway in how we want to handle the parameters. Though it is generally true that a API expect a certain data types, the differences in how we pass those data can allow different way to use it. The next sections will provide concrete examples.

Using pointers or actual data type

Using [GetTempFileName] as an example, we can declare it thus:

Private Declare Function GetTempFileNameNoPrefix Lib "kernel32" Alias "GetTempFileNameA" ( _
   ByVal lpszPath As String, _
   ByVal lpPrefixString As LongPtr, _ 'Changed from String
   ByVal wUnique As Long, _
   ByVal lpTempFileName As String _
) As Long

and call it as thus:

GetTempFileNameNoPrefix "...", &H0, &H0, strBuffer

This is legal because we're passing in a null pointer, and thus indicating that we do not want a prefix applied to our temporary file name. If we were to decide one day that we wanted a prefix after all, we would need to use the original declaration with String data type in order to pass a valid pointer. We, however, could also do this instead:

GetTempFileNameNoPrefix "...", StrPtr(strPrefix), &H0, strBuffer

and contrary to what we named our API declaration, we get the prefix added because we used StrPtr to obtain a pointer to a string variable, which is actually what API was expecting. String data type also works because Visual Basic is smart enough to convert strings into pointer for you. In fact, any given non-integer parameter could be substituted with a pointer. Whenever we do this, the calling syntax will also change.

ByVal vs. ByRef

It is important to note that C/C++ pass parameters by value as the default, which is opposite of Visual Basic's default of passing those by reference. Hence, for many basic data types, we will want to pass in ByVal. For example, if we were to declare [GetDC] using this erroneous declaration:

Private Declare PtrSafe Function GetDC Lib "user32" ( _
  hWnd As LongPtr _
) As LongPtr

and we call it:


which is a valid calling syntax, which will request the Device Context for entire screen. However, instead of actually passing in a null pointer, the API will instead get a pointer address to variable containing value &H0. This appears to the API to be a valid pointer, but it does not point to a Window handle as it expects and when it can't find the matching Window handle, it errors out. Thus, we want to ensure we use ByVal for most parameters where it is expecting a pointer. This also includes String as will be discussed in the next section.

One common case when we can safely use ByRef (the default in Visual Basic) is when we're passing back a structure. In theory, an object should be also passed by reference but large majority of Windows API does not really work with objects so this is pretty rare. But if you have a custom APIs that exposes and expects a certain object, this may be also passed ByRef.

GetOpenFileName is a good example of when we actually want to use ByRef. Because it expects a certain structure (in Visual Basic parlance, we call them user-defined types), we can use a matching structure and pass it back to the API.

Buffering Strings

Of various data types, String is possibly the data type where Visual Basic does lot of work behind the curtains. For anybody not familiar with String, be aware that there is actually no way to conceptualize a string, as you could with integers. If you go back to C language, you would find that there's no such thing as String data type - strings were represented by an array of chars (which is technically a 8-bit integer) that we decided to map to a certain value and therefore means a certain character. It gets more messy than this - there are many different way to represent an array of char. A common way is to use a null-terminated string but that's not the only way. Some may use just expect a pointer to first character and be told how many bytes it needs to "walk" to build the string. Then there's different encoding; do we use ASCII mapping or do we use Unicode? Obviously, we can't pass an Unicode string into API expecting an ANSI string. Hopefully, this helps you understand why Windows seems to have so many different kind of string data types. But this is what you need to know:

1) Visual Basic will always convert a piece of String which is Unicode by default into ANSI. Thus, for most of your API needs, you want to use ANSI version of the API function even though you're actually using Unicode at all times within Visual Basic.

2) You should pass string by value and not by reference. This seems counter-intuitive since a String would be an object and thus should be passed with a poitner, but again, Visual Basic will automatically handle the passing and thus return a pointer to the first character.

With those in mind, we need to work out what kind of string our API function is expecting. If the documentation say it wants a null-terminated string, we should do this:

strSomeValue = "Hello, world!" & vbNullChar

to ensure we have a null character at end of the string. Other API functions may instead want a length of the string as a separate parameter. Therefore, it may be necessary to do a Len() upon the string being passed in.

When dealing with string that may be then returned, the thing we should never do is pass in a zero-length string. Doing this will cause API function to fail because there's no room for API function to write to it. It's as if API function does not know how to allocate enough memory to add new character. This is something that Visual Basic does for us all times but back in C/C++, we are responsible for allocating enough spaces in our string to allow API function to write it. One way to buffer string is to do something as follow:

Const MAX_PATH As Long = 254


Dim strBuffer As String
strBuffer = Space(MAX_PATH) & vbNullChar

strBuffer now is ready for passing into API and will return with valid data.

Types and Structures

Reading Returns from an API

Function Return Values

ByRef and Pointer Argument Returns


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

Edit Discussion
Custom Search

Thank you for your support!
This page has been accessed 64,638 times.  This page was last modified 11:55, 7 May 2012 by Jack Leach. Contributions by BananaRepublic, Mark Davis and Ace  Disclaimers