What is an API ?
Perhaps it may be better to explain a little about API's and then go into examples of there uses within Excel.
Here is a Basic Q & A I gave @ MrExcel.com perhaps this may help a little on API functions.
Q > Can you explain about the Function API below
> Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
A >
In VBA before you can call a function in a DLL, you must provide VBA with information about where that function is and how to call it. There are two ways to do this:
1) by setting a reference to the DLL's type library (.tlb)
2) by using a Declare statement in a module as above.
The Declare keyword alerts VBA that you want to include the definition for a DLL function in your project. A Declare statement in a standard module can be public or private, depending on whether you want the API function to be available to only a single module or to the entire project. For class modules, a Declare statement must be private so .........................
The Public Function >
Public Declare Function sndPlaySound Lib "winmm.dll" _
Alias "sndPlaySoundA" ( _
ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long
Is a function call [Look up "Function Statement" in the VBA Help files] to a Library file, in this case the Windows Multimedia Dynamic link library. Within this dll (Dynamic link library) there are various other functions (made in C / C++) that work at the lower level of programing, these calls or libraries of calls make up these DLLs, and are most commonly documented using C language syntax. To call these procedures from Visual Basic, you need to translate them into valid Declare statements and call them with the correct arguments.
So basically Dlls are libraries of procedures that applications can link to and use at run time rather than link to statically at compile time.
These procedures are sometimes referred to as the
Windows API, or Application Programming Interface.
You will see alot of these API calls on this site. An API is exactly as its acronym suggests, an interface. Just like the interface between your screen and yourself is the keyboard or mouse, so the underlying code just translates your actions to the screen....everything between these 2 is handled by Library files that call other functions to perform actions. You use API while programing in VBA, you just don't realise it...the VBA just acts like a translator in converting your code to the appropriate Op-codes so that the underlying library files can perform some action/function.
The plus side of using API's means that the libraries can be updated independently of the application, and many applications can share a single DLL. The down side is that if you get it wrong you may not receive an error and/or your system will become unstable or crash causing you to reboot.
Always good practice to constantly save when working with APIs. Also can be difficult to debug your program, in fact you can't debug API calls in VBA.
Have a look @ your System Dir under your Windows Dir and you will see all these files. Windows is comprised of DLLs, and other applications call the procedures within these libraries to display windows and graphics, manage memory, or perform other tasks. There are numerous libraries and associated calls (API's). Now to access these library files or APIs you need to know how to declare a DLL procedure. You add a Declare statement to the Declarations section of the code window. By declaring the function, you tell VBA where it can find the function.
If the procedure returns a value, write the declare as a Function:
e.g. Using the winmm.dll call to SndPlaySoundA does returns a value;
A value of nonzero means the function call succeeded.
Zero indicates the call failed.
Declare Function publicname Lib "libname" [Alias "alias"] [([[ByVal] variable [As type] [,[ByVal]
variable [As type]]...])] As Type
If a procedure does not return a value, write the declare as a Sub:
eg.
Declare Sub publicname Lib "libname" [Alias "alias"] [([[ByVal] variable [As type] [,[ByVal] variable [As type]]...])]
As per Std Sub Procedure set-up, DLL procedures declared in standard modules are public by default and can be called from anywhere in your application. DLL procedures declared in any other type of module are private to that module, and you must identify them as such by preceding the declaration with the Private keyword. Procedure names are case-sensitive in 32-bit versions of Visual Basic. In previous, 16-bit versions, procedure names were not case-sensitive.
Lets look @ the above Function;
[Lib]
Specifying the Library
The Lib clause in the Declare statement tells Visual Basic where to find the .dll file that contains the procedure. When you're referencing one of the core Windows libraries;
User32 [user interface functions],
Kernel32 [operating system kernel functions],
GDI32 [graphics device interface functions], and
shell32.dll [Windows shell functions],
you don't need to include the file name extension:
eg.
Declare Function RegisterClass Lib "user32" Alias "RegisterClass" (Class As WNDCLASS) As Long
For other DLLs, the Lib clause is a file specification that can include a path:
Declare Function lzCopy Lib "c:\windows\lzexpand.dll"
_(ByVal S As
_ Integer, ByVal D As Integer) As Long
If you do not specify a path for libname, Visual Basic will search for the file in the following order:
1. Directory containing the .exe file
2. Current directory
3. Windows 32-bit system directory \Windows\System32
4. Windows directory \Windows
In most cases you will see API calls with No explicit path as you would use the core window library files which are located in your system dir. So in the above example we are Looking @ the winmm.dll
sndPlaySound = The name by which Visual Basic will refer to the function everywhere else in your code. You can make this any name you want (as long as you use the proper Alias clause), but it's safest to make this the Actual name of the function in the Windows API.
[Alias]
The Alias clause in the declare statement is needed to specify the correct character set. Windows API functions that contain strings actually exist in two formats: ANSI and Unicode.
It is the A (ANSI) @ end that signifies this in "sndPlaySoundA"
The Unicode version ends with the letter W (WCHAR type). So if the function has two entry points then you would expect to see the Alias used.
ANSI or Unicode
The 32-bit versions of Windows (Windows 95+ and Windows NT) support two separate character sets: ANSI, in which each character takes 1 byte of storage and there are only 256 different possible characters; and Unicode, in which each character takes up 2 bytes of storage and there are 65,536 different possible characters ie. 16-bit Unicode character. VBA, Access' programming language, supports Unicode only. In general, this works in your favour, allowing support for many languages, including those that use character sets different from the English ones. In the Windows header files, therefore, you'll get both ANSI and Unicode versions of each function that contains a string. Have a look for winmm.dll and right click to select Quickview, you should get something like this;
Export table
Ordinal | Entry Point | Name
0004 00012d0 CloseDriver
0005 0008db3 DefDriverProc
etc....listing all the entry points or LP Long pointers to the various functions with in the library.
Now for the Arguments and Types;
(ByVal lpszSoundName As String, ByVal uFlags As Long) As Long
lpszSoundName = a string that specifies the sound to play. This must be the Fullpathname. If this parameter is NULL, any currently playing waveform sound is stopped. ie To stop a currently playing sound then send the VBA Const VBNullString and NOT "" !!
ByVal = when a value is passed ByVal, the actual value is passed directly to the function, the exception to this is string variables, but that is another story, and when passed ByRef, the address of the value is passed, so you may see something like LPWORD which is actually a Long Pointer to a memory address.
As String = value passed is a string = the full path name to the sound file
ByVal uFlags As Long
The function is expecting some Flags to be declared here as the data type Long (Look up data Type for Long) = Long (long integer) variables are stored as signed 32-bit (4-byte) numbers ranging in value from -2,147,483,648 to 2,147,483,647. The type-declaration character for Long is the ampersand (&).
As for the Public Constants
Public Const SND_SYNC = &H0
Public Const SND_ASYNC = &H1
Public Const SND_NODEFAULT = &H2
Public Const SND_MEMORY = &H4
Public Const SND_LOOP = &H8
Public Const SND_NOSTOP = &H10
What does &H0, &H1, &H2, &H4, &H8, &H10 mean?? They are Hexadecimal values (Base 16)
The function will recognize various Constants or flags that tell it how/what to do eg.
Flags for playing the sound;
The sound is played asynchronously and PlaySound returns immediately after beginning the sound. To terminate an asynchronously played waveform sound, call PlaySound with lpszSoundName set to VBNullString.
The sound plays repeatedly until PlaySound is called again with the lpszSoundName parameter set to VBNullString. You must also specify the SND_ASYNC flag to indicate an asynchronous sound event.
No default sound event is used. If the sound cannot be found, PlaySound returns silently without playing the default sound.
The specified sound event will yield to another sound event that is already playing. If a sound cannot be played because the resource needed to generate that sound is busy playing another sound,the function immediately returns FALSE without playing the requested sound. If this flag is not specified, PlaySound attempts to stop the currently playing sound so that the device can be used to play the new sound.
If the driver is busy, then return immediately without playing the sound.
Synchronous playback of a sound event. PlaySound returns after the sound event completes.
The values; &H0, &H1, &H2, &H4, &H8, &H10 are the Long Hex values of these constants eg.
&[Long] H(Hex) 0 or &H0 = 0 Decimal
&[Long] H(Hex) 10 or &H10 = 16 Decimal
A recent Question was asked @ the Mrexcel board, here is the response I supplied that you may also find helpful.
Q> The Code Line :
wFlags = SND_ASYNC Or SND_NODEFAULT
Assigns 2 Constants to the wFlags Variable !! by means of the OR Operator. I have never seen this
before in VBA.
Can anyone explain this to me ?
A>
What you are looking @ is Boolean Algebra and setting Bit Flags.
Have a look @ the Flags;
Hex =
Public Const SND_SYNC = &H0
Public Const SND_ASYNC = &H1
Public Const SND_NODEFAULT = &H2
Public Const SND_MEMORY = &H4
Public Const SND_LOOP = &H8
Public Const SND_NOSTOP = &H10
Dec =
Public Const SND_SYNC = 0
Public Const SND_ASYNC = 1
Public Const SND_NODEFAULT = 2
Public Const SND_MEMORY = 4
Public Const SND_LOOP = 8
Public Const SND_NOSTOP = 16
Can you see the pattern?
If you are familiar with Binary Bits & Bytes then the above looks familiar.
A Binary Byte representation of the above Flags looks like this (16Bit representation!);
Public Const SND_SYNC = 0000000000000000 or 0
Public Const SND_ASYNC = 0000000000000001 or 1
Public Const SND_NODEFAULT = 0000000000000010 or 10
Public Const SND_MEMORY = 0000000000000100 or 100
Public Const SND_LOOP = 8 0000000000001000 or 1000
Public Const SND_NOSTOP = 16 0000000000010000 or 10000
Do you see now why they are Flags, each Bit is set according to it's function, and every other Bit is CLEARED (set to 0). Don't forget computers are essentially Binary and you are essentially communicating to registers that do certain things when Set / Not Set. For PLC programmers this will be very clear in that they work with 32Bit registers via Ladder programing. When doing Assembly language this understanding is important, but I digress :-)
That is each individual Bit is either ON or OFF and represents a Flag.
eg.
If NO Bit is set sound is played Synchronously > Public Const SND_SYNC = 0
If 1st Bit set it is Asynchronous
If 2nd Bit set then No default sound is played if no file to play is found
If 3rd Bit set etc.......
Why take this approach? because if you used separate Parameters for each and every parameter setting you would end up with dozens of parameter settings, ie more then 16 (32,64 depending on OS) data Bit settings, each one requiring extra memory and CPU operations to store the parameters on the Stack ( = Memory).
This is very inefficient. So by specifying Constants that represent SINGLE Bits (Flags), it is possible to set ANY combination of Bits using the OR Operator.
Now what about if you need to Play sound Asynchronously AND NOT play the Default sound (=Beep) You would set Bits 1 and 2 = decimal 1 & 2
When programing using API's it is useful for the programmer to visualize these so we typically view
them as native Hex = &H1 and &H2 - its easier to visualize the Bits set :-)
So you need to set these 2 Bits which would look like this;
0000000000000011 (or 11 Binary)
So how to we get to set Individual Bits?
We use the OR Operator
&H1 OR &H2 = &H3
So.......to turn Bits 1 & 2 on
&H1 0000000000000001
Or &H2 0000000000000010
= &H3 0000000000000011
These Operators are very important to the API programmer (& Assembly programmers even more so!) Why? because setting Bits is one thing BUT you will also need to test for what Bits are set, Shift Bits left or right, turn Bits On and Off, Masking etc........
Q2> The wFlags Constants are declared using the Hexagonal System .
Is this a requirement or can they be declared using the Decimal System like in standard VBA Codes ?
A> Reply:
See above....why this is done....BUT yes you could just use Decimal in most cases.
API calls are very useful when you require commands that are not provided with VBA.
By calling procedures in DLLs, you can access the thousands of procedures that form the backbone of the Microsoft Windows operating system.
See code below as example
![Goto Guest book sign in page [Home]](files/guestbook.gif)
