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.




Excel VBA Macro Programming
Excel VBA Macro Programming
ISBN: 0072231440
EAN: 2147483647
Year: 2004
Pages: 141

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net