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
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)) )
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.
© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext