Using an API Call

Here are some examples of API calls and how to use them within your VBA code.

Getting Disk Space

For this example, you are going to use an API call that gets the spare disk space from a disk device. For the record, this particular API call gets around the 2GB problem. The original Microsoft API call only read disk drives up to 2GB ‚ after that it gave up and every result was capped at 2GB. As hard disk drive sizes, increased, Microsoft had to come up with a new API call.

First of all, you must make the declaration. You do this in the declarations section of a module. The syntax for this particular declaration is as follows :

 Private Declare Function GetDiskFreeSpaceEx Lib "kernel32"  _ 
 Alias "GetDiskFreeSpaceExA" (ByVal lpDirectoryName As  _ 
 String, lpFreeBytesAvailableToCaller As Currency,  _ 
 lpTotalNumberOfBytes As Currency, lpTotalNumberOfFreeBytes  _ 
 As Currency) As Long 

This is quite a long statement, and it has to be completely accurate to work. If you have access to Visual Basic, all the API declarations are contained in a file called API32.TXT and can easily be copied and pasted into your declarations. Further information can be obtained from the Microsoft Developer Network (MSDN) at microsoft.msdn.com, which is the perfect place to find more advanced information on Excel VBA and API calls.

Basically, this statement sets up a reference to the kernel32.dll that resides in the Windows system directory. It specifies the way parameters must be used and what they are called. Its purpose is to put a function into your code that you can use to call this library function from kernel32.dll.

The next step is to put in code to call this function:

 Sub Test_Api() 
 Dim FreeBytesAvailableToCaller As Currency, TotalNumberOfBytes As _ 
 Currency,  TotalNumberOfFreeBytes As Currency 
 x = GetDiskFreeSpaceEx("c:\", FreeBytesAvailableToCaller, _ 
 TotalNumberOfBytes, TotalNumberOfFreeBytes) 
 MsgBox "Total Space " & Format(TotalNumberOfBytes * 10000, "#,##0") 
 MsgBox "Free Space " & Format(TotalNumberOfFreeBytes * 10000, "#,##0") 
 End Sub 

This code sets up variables to hold the values returned by the API call. The actual value returned through the variable calling it (in this case, x ) is related to errors. It has a value of 1 for success.

The call is then made passing the root directory "C:\" and the variables already defined. It is then a simple case of multiplying the value by 10,000 and formatting to comma format for easy display. You can check the values by going into Windows Explorer or NT Explorer and selecting File Properties from the menu, as shown in Figure 17-1.

Figure 17-1: Getting the total space from a disk drive

Reading and Writing to INI Files

API calls can also be used for reading and writing to files. One of their useful functions is creating INI files for your program. INI files are a means of storing settings for your program, such as for control settings that you want to be sticky or details of user settings for individual applications. Variables and properties in VBA only hold their values while the program is present. Once you close down, all is lost, and everything returns to default.

An example might be a text box that holds a directory pathname that is set by the user. The user might put in a different pathname, but when the program is closed, it reverts to the default on reloading. This is irritating for the user, as they have to keep putting in their pathname.

The INI file holds this information locally so that it can be retrieved when the program is loaded next time. Of course, you could use a portion of the spreadsheet to store these values, but this could easily be overwritten by other data.

There are two declarations that can be used for your INI file. (There are other declarations for reading and writing to INI files, but you are going to use these in the example.)

 Private Declare Function GetPrivateProfileString Lib "kernel32" Alias _ 
 "GetPrivateProfileStringA" (ByVal lpApplicationName As String, ByVal _ 
 lpKeyName As Any, ByVal lpDefault As String, ByVal lpReturnedString As _ 
 String, ByVal nSize As Long, ByVal lpFileName As String) As Long 
 
 Private Declare Function WritePrivateProfileString Lib "kernel32" Alias _ 
 "WritePrivateProfileStringA" (ByVal lpApplicationName As String, ByVal _ 
 lpKeyName As Any, ByVal lpString As Any, ByVal lpFileName As String) As _ 
 Long 

These need to be added into the declarations section of a module. They set up references to the kernel32.dll file and describe how the parameters will be passed. You can then use code to pass the parameters and write to the file:

 Sub Test_INI() 
 x = WritePrivateProfileString("Parameters", "Path", "C:\temp\", "myini.ini") 
 s$ = Space$(256) 
 x = GetPrivateProfileString("Parameters", "Path", "", s$, 256, "myini.ini") 
 MsgBox s$ 
 MsgBox x 
 End Sub 

The first line writes the INI file. If the file is not already there, it is automatically created. The default location is in the Windows directory, although you can change this by placing a pathname onto the filename.

The first parameter (Parameters) is the section of the INI file to write the new string. The second parameter is the key name or the entry to set; if set to Null, it will delete all keys within this section. The third parameter is the value to write for the key. This is "C:\temp\"; if set to Null, it will delete the existing string for that key. The fourth parameter is the name of the INI file. You can use any suffix; you do not have to use an INI suffix. This is quite useful if you want to hide some settings and wish to disguise your INI file ‚ you can give it a DLL or EXE suffix so that it looks like a program file on casual inspection.

This now creates MYINI.INI. If you look in your Windows directory, you will see that the file has been created. If you double-click it, it will be loaded into Notepad because it is a text file, and should look like this:

 [Parameters] 
 Path=C:\temp\ 

The next API call reads the path back in. However, before this can happen, you must create a variable for it to be placed in.

The Space$ command has been used to create a string of spaces 256 long. Also, this variable must be specified as a string, otherwise errors will occur. The return value comes back as a string so it must be placed into a string variable.

GetPrivateProfileString works in a similar way to WritePrivateProfileString , but there are more parameters to pass. The first and second parameters are the same as before and give details of the section and key to be read. The third parameter has a default value to return if the entry is not found. The fourth parameter contains the variable name for the result to be passed to. The fifth parameter contains the maximum number of characters to load into the variable. The sixth parameter is the filename to search for.

The variable calling the API ( x ) will give the number of characters returned. S$ will contain the key, terminated by a Null character. If the keyname is left as Null, then all entries in that section will be returned, each terminated with a Null character.

Both these API calls are very forgiving in their operation. If the file is not there, it is created. If the key is not there, it is created. If the file does not exist when you use the read command, then a Null value will be returned.

This method is often used in programs to keep track of user settings so that when the user enters the program subsequently, it is set to that user's personal settings.

Microsoft no longer uses INI files; they use keys in the Registry to record this information.

Read Keyboard Activity

Another useful purpose of API calls is to read the keyboard and find out if a certain key has been pressed. There are events on user forms to manage keyboard events, but these only apply to a particular form or a particular control on the form that has the focus at that time.

For example, suppose you write a macro to do a time-consuming task that involves it looping around thousands of times. You may want to give the user a ‚“get out ‚½ command to bring it to a halt if it takes too long. You can only do this by checking the keyboard because the only way that you can see a keyboard event is on a UserForm, and this may not have the focus at the time when the user presses the ‚“get out ‚½ key. You use the API call GetKeyState to do this. You must put the following code in the declarations part of a module:

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

This will allow you to examine any key while the program is running. You can even differentiate between left and right SHIFT keys or CTRL keys:

 Sub Test_Key() 
 x = 0 
 Do Until x = 1 
 
 If GetKeyState(&H9) < 0 Then x = 1 
 
 DoEvents 
 
 Loop 
 MsgBox "You pressed the TAB key" 
 End Sub 

This program sets up a simple Do Until..Loop that keeps running until x=1. This will never happen until the GetKeyState line turns up a value for the specified key that is less than 0 ‚ that is, it has been pressed. For the purposes of the example, the TAB key is used, which has a value of 09 in hexadecimal. When the TAB key is pressed, x changes its value to 1 and exits from the loop. The message box is then displayed.

The DoEvents command is very important here. It literally allows the operating system to catch its breath and finish processing all messages before moving onto the next instruction. If you do not use this, then the message from the keyboard will not be processed before the next loop is started and the keypress will be missed.

Run this code, and it goes into an infinite loop because x will never equal 1. Press any key on the keyboard and nothing happens. But press the TAB key, and the program will end with a message box. Of course, it helps if you know the values of the Virtual Key Codes if you want to use other key combinations. They are listed here:

Symbolic Constant Name	Value (Hexadecimal)	Mouse or Keyboard Equivalent
VK_LBUTTON	01	Left mouse button
VK_RBUTTON	02	Right mouse button
VK_CANCEL	03	CTRL-BREAK processing
VK_MBUTTON	04	Middle mouse button (three-button mouse)
‚	05 ‚ 07	Undefined
VK_BACK	08	BACKSPACE
VK_TAB	09	TAB
‚	0A ‚ 0B	Undefined
VK_CLEAR	0C	CLEAR
VK_RETURN	0D	ENTER
‚	0E ‚ 0F	Undefined
VK_SHIFT	10	SHIFT
VK_CONTROL	11	CTRL
VK_MENU	12	ALT
VK_PAUSE	13	PAUSE
VK_CAPITAL	14	CAPS LOCK
‚	15 ‚ 19	Reserved for Kanji systems
‚	1A	Undefined
VK_ESCAPE	1B	ESC
‚	1C ‚ 1F	Reserved for Kanji systems
VK_SPACE	20	SPACEBAR
VK_PRIOR	21	PAGE UP
VK_NEXT	22	PAGE DOWN
VK_END	23	END
VK_HOME	24	HOME
VK_LEFT	25	LEFT ARROW
VK_UP	26	UP ARROW
VK_RIGHT	27	RIGHT ARROW
VK_DOWN	28	DOWN ARROW
VK_SELECT	29	SELECT
‚	2A	Original equipment manufacturer (OEM) specific
VK_EXECUTE	2B	EXECUTE
VK_SNAPSHOT	2C	PRINT SCREEN
VK_INSERT	2D	INS
VK_DELETE	2E	DEL
VK_HELP	2F	HELP
VK_0	30
VK_1	31	1
VK_2	32	2
VK_3	33	3
VK_4	34	4
VK_5	35	5
VK_6	36	6
VK_7	37	7
VK_8	38	8
VK_9	39	9
‚	3A ‚ 40	Undefined
VK_A	41	A
VK_B	42	B
VK_C	43	C
VK_D	44	D
VK_E	45	E
VK_F	46	F
VK_G	47	G
VK_H	48	H
VK_I	49	I
VK_J	4A	J
VK_K	4B	K
VK_L	4C	L
VK_M	4D	M
VK_N	4E	N
VK_O	4F	O
VK_P	50	P
VK_Q	51	Q
VK_R	52	R
VK_S	53	S
VK_T	54	T
VK_U	55	U
VK_V	56	V
VK_W	57	W
VK_X	58	X
VK_Y	59	Y
VK_Z	5A	Z
VK_LWIN	5B	LEFT WINDOWS (Microsoft Natural Keyboard)
VK_RWIN	5C	RIGHT WINDOWS (Microsoft Natural Keyboard)
VK_APPS	5D	APPLICATIONS (Microsoft Natural Keyboard)
‚	5E ‚ 5F	Undefined
VK_NUMPAD0	60	Numeric keypad
VK_NUMPAD1	61	Numeric keypad 1
VK_NUMPAD2	62	Numeric keypad 2
VK_NUMPAD3	63	Numeric keypad 3
VK_NUMPAD4	64	Numeric keypad 4
VK_NUMPAD5	65	Numeric keypad 5
VK_NUMPAD6	66	Numeric keypad 6
VK_NUMPAD7	67	Numeric keypad 7
VK_NUMPAD8	68	Numeric keypad 8
VK_NUMPAD9	69	Numeric keypad 9
VK_MULTIPLY	6A	MULTIPLY
VK_ADD	6B	ADD
VK_SEPARATOR	6C	SEPARATOR
VK_SUBTRACT	6D	SUBTRACT
VK_DECIMAL	6E	DECIMAL
VK_DIVIDE	6F	DIVIDE
VK_F1	70	F1
VK_F2	71	F2
VK_F3	72	F3
VK_F4	73	F4
VK_F5	74	F5
VK_F6	75	F6
VK_F7	76	F7
VK_F8	77	F8
VK_F9	78	F9
VK_F10	79	F10
VK_F11	7A	F11
VK_F12	7B	F12
VK_F13	7C	F13
VK_F14	7D	F14
VK_F15	7E	F15
VK_F16	7F	F16
VK_F17	80H	F17
VK_F18	81H	F18
VK_F19	82H	F19
VK_F20	83H	F20
VK_F21	84H	F21
VK_F22	85H	F22
VK_F23	86H	F23
VK_F24	87H	F24
‚	88 ‚ 8F	Unassigned
VK_NUMLOCK	90	NUM LOCK
VK_SCROLL	91	SCROLL LOCK
VK_LSHIFT	A0	LEFT SHIFT
VK_RSHIFT	A1	RIGHT SHIFT
VK_LCONTROL	A2	LEFT CTRL
VK_RCONTROL	A3	RIGHT CTRL
VK_LMENU	A4	LEFT MENU
VK_RMENU	A5	RIGHT MENU
‚	E7 ‚ E8	Unassigned
‚	E9 ‚ F5	OEM specific
VK_ATTN	F6	ATTN
VK_CRSEL	F7	CRSEL
VK_EXSEL	F8	EXSEL
VK_EREOF	F9	ERASE EOF
VK_PLAY	FA	PLAY
VK_ZOOM	FB	ZOOM
VK_NONAME	FC	Reserved for future use
VK_PA1	FD	PA1
VK_OEM_CLEAR	FE	CLEAR

Play Multimedia Sounds

You can also use an API call to play a sound within your code from a WAV file. The old macro programming language had a command to do this, but it has not been included in VBA, or for that matter in Visual Basic itself, which makes it a bit restrictive if you want to play sound files. If you want to use Mulitmedia functions within your code to play sounds or voice files, then the means to play sound files is very crucial. The declaration goes in the declarations section of a module:

 Public Declare Function PlaySound Lib "winmm.dll" Alias "PlaySoundA" _ 
 (ByVal lpszName As String, ByVal hModule As Long, ByVal dwFlags As _ 
 Long) As Long 

You can then play the WAV files within your code:

 Sub test_sound() 
 x = PlaySound("c:\windows\media\chord.wav", 0, 2) 
 x = PlaySound("c:\windows\media\ding.wav", 0, 2) 
 End Sub 

This example plays two standard Windows sounds. If you have a microphone on your computer, you can record your own sound effects or speech onto a WAV file and play them back in this way.

These examples give some idea of the power of API calls within a program. There are many books written on this subject if you wish to examine this topic further.