Windows API

Creates a reference to a procedure or function in an external DLL.
The reason for calling an API function is to perform something that would otherwise be impossible.

These functions are a set of functions that Windows programs use to manipulate Windows.
In traditional applications the functions used / called where 'statically linked' to the program.
The program included all the functions it needed.
The obvious problem is the same functions and procedures are reproduced lots of times.

64 Bit - PtrSafe

In 32 bit office a pointer must be a 32 bit variable In 64 bit office a pointer must be a 64 bit variable.
When declaring an API you must acknowledge this fact by adding the PtrSafe keyword to the API declaration.

Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As LongLong 

When declaring an API you must acknowledge this fact by adding the PtrSafe keyword to the API declaration.
The PtrSafe attribute is optional on the 32-bit version of Office.
When running 32 Bit Office a pointer must be a 32 bit variable
When running 64 Bit Office a pointer must be a 64 bit variable.

Alias Keyword

Whenever you use an API (Application Programming Interface) call function prefix the alias with "A" eg aFindWindow
Windows uses a different approach. Libraries are usually 'dynamically linked' to the program. The common routines reside in a Dynamic Link Library and are stored in the Windows / System directory if more than one program uses them.

[Public | Private] Declare Sub name Lib "libname" [Alias "aliasname"] [ (arglist) ] 

These functions provide facilities to create a window, change its size, read and write Registry entries, manipulate a file and so on. Most of these functions are stored in the three link libraries (User32.dll, Kernel.dll & ????)

A full list of all Windows API functions and subroutines, containing the syntax of their calls can be found in the help file "WIN32API.txt" which is available for download from the unit websites.

If you are going to use Windows API calls in your code it may be worth encapsulating them into a class.

Before you can use a Windows API function you must declare the function at the top of a standard code module above the first procedure.
Declaring a Windows API can be a bit tricky for the following reasons:
1) Deciding which API to actually use
2) Deciding in which library the function is located
3) Knowing the exact arguments

Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer 

Once it is declared you can use it just like a regular function.

Example - GetWindowsDirectory

Declare Function GetWindowsDirectoryA Lib "kernel32" (ByVal lpBuffer As String, ByVal nSize As Long) As Long 

This function returns the name of the directory in which Windows is installed and has two arguments,
After calling the function, the Windows directory is contained in lpBuffer, and the length of the directory string is contained in nSize.
Once you have added the Declare statement to the top of your module, you can access the function by calling the GetWindowsDirectoryA function.

Dim sPathName As String 
sPathName = GetWindowsDirectoryA(Space(255), 255) 

Creating a Wrapper

Most of the time you will probably want to create a wrapper for the API function.
Creating a wrapper can simplify actually using the function.

Function WindowsDire() As String 
   Dim WinPath As String 
   WinPath = Space(255) 
   WindowsDir = Left(WinPath, GetWindowsDirectoryA(WinPath, Len(WinPath)) ) 
End Function 

Dynamic Link Libraries (DLL)

One of the most powerful features of VBA is the ability to call a DDL from a VBA procedure. A dynamic link library is a list of routines loaded into memory and linked to applications at run-time. DLL's are normally written in C or C++ although you could create then using Pascal or Delphi. You declare a reference to an external procedure in a DLL by the following syntax.
Because DLL routines reside in files that are external to your application, you must let VBA know exactly where the files are
Public Declare PtrSafe Sub name Lib "libname" Alias "aliasname" (arglist)
Public Declare PtrSafe Function name Lib "libname" Alias "aliasname" (arglist) as type
The procedure name that immediately follows the Declare keyword indicates the name you are going to use to call the procedure (ie an alias name). The name in quotes, after the Alias keyword specifies the actual name of the procedure as it can be found in the DLL
Libname is the name of the DLL that contains the procedure being declared and alias name is the name of the procedure in the DLL. To use a DLL you must know the procedure in it and the arguments to each of these procedures.
Accompanying the DLL there may be a file called a 'type library' which describes the procedures within the DLL. A type library normally has the extension (.OLB) or (.TLB) and is registered with the OLE component of Windows. The Setup program that installs the DLL usually creates the properties with the Windows Registry. Tools > References shows all the registered type libraries available

If you use a type library, there is no need to use Declare statements as the functionality is included. Type libraries also avoid the difficulties of passing strings to DLLs. The Windows API doesn't have a type library and so you must Declare the functions before they can be used. A Declare statement must be made in the declaration section of your module.
It is important to save your work before running any code that calls a DLL as an incorrect call will result in a General Protection (GP) fault and will cause VBA to crash losing any unsaved changes. Also take care when returning strings from DLLs

You can only pass numeric arrays to DLLs. (not string or user defined) When passing an array you must give some indication of the size of the array.
The name of the procedure given in the Declare statement is case sensitive. If you want the procedure name in your code to use a different capitalisation you must use the Alias Name.
Converting C parameters into VBA declarations. The documentation for DLLs that aren't part of the Windows API are usually in the form of a C header file that provides the type and number of the arguments to the functions in the DLL. Toy will need to convert the C data types into their equivalent VBA data type.
Calling a DLL procedure is a method of performing tasks that standard VBA functions and statements do not allow. For example VBA has no intrinsic ability to retrieve information about the amount of free memory available but you can easily do this with an API call. Windows itself includes a number of DLLs with hundreds of useful functions. These functions are collectively called the Windows API.