Window APIs for VBA

In this blog, I'll give you brief idea about Window APIs and their application in VBA.

API stands for Application Programming Interface.
There are several reasons to use Window APIs instead of built-in VBA functions:

Speed - although there might be only a fraction of a millisecond's difference between a VBA function and using an API call, if you are using it repeatedly, then this difference mounts up. A good example of this is recursively searching for a file through the directories and sub-directories.

Extensibility - you wish to perform something that cannot be achieved using VBA functions.


In general, an API is declared as below:

[Private|Public] Declare [Function|Sub] APIName Lib [DLLName] (Alias APIName) (Arguments) (Return Type)

For example:

Private Declare Function apiGetDC Lib "user32" Alias "GetDC" (ByVal hwnd As Long) As Long

[Private|Public]:
This determines the scope of the function of subprocedure. This is mostly a matter of preference. I prefer to declare my API calls private within a module, and then use a function to call them. This allows me to have a module that is stand-alone and can be copied to another database without reliance on other modules.

[Function|Sub]:

Whether it is a subprocedure or a function. Nearly all APIs are functions, and they nearly all return a value directly.

[DLLName]:
The name of the DLL that the procedure is in. For the standard DLLs, user32.dll, kernel32.dll or gdi32.dll you can omit the file extension, but for all other DLLs you must include the file extension.


(Alias APIName):
If you have declared the API as being different from the name that it is known within the DLL you must specify the correct name here. There are several reasons why you may wish to do this:

• The name of the API is not a valid VBA function name, such as '_lwrite';
• You are declaring it twice, for example to accept different argument types to get around the 'As Any' variable type;
• You wish to have a common naming policy for API calls, such as prefixing them all with 'api'

Note that the API name must be in the correct case - 'findfile' is not equal to 'FINDFILE'



(Arguments):
As with VBA procedures, APIs may accept various arguments. However, this is one area where care needs to be taken to ensure that you pass ByRef or ByValue as needed. You will often also need to predeclare string arguments to be a certain length. You may also find that you pass a Type Structure as an argument, and the values that you want are in that Type Structure.
(Return Value):
The datatype that the API returns. Normally this will be a Long Integer, with 0 often indicating an error.

To find more about API please go through:

http://www.applecore99.com/api/api001.asp



The following table lists the common operating environment library files.


Dynamic Link Library Description
Advapi32.dll Advanced API services library supporting
numerous APIs  including many security and Registry calls
Comdlg32.dll Common dialog API library
Gdi32.dll Graphics Device Interface API library
Kernel32.dll Core Windows 32-bit base API support
Lz32.dll 32-bit compression routines
Mpr.dll Multiple Provider Router library
Netapi32.dll 32-bit Network API library
Shell32.dll 32-bit Shell API library
User32.dll Library for user interface routines
Version.dll Version library
Winmm.dll Windows multimedia library
Winspool.drv     Print spooler interface that contains the print