Excel VBA Macro Programming

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:

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.