Download Microsoft Excel VBA User Manual

Excel Visual Basic for Applications
also possible to achieve similar interactive effects in worksheet cells by using Data,
Validation in the main Excel menu. Less sophisticated but much easier.
Using the Windows API
You have access to the Windows Application Programming Interface through VBA and
you can use the WIN API to control your system: manage the display of windows,
communicate with other devices, return information about the operating system,
available memory etc. There are hundreds of functions that you can call but you will not
find any documentation on these in Excel, you must search elsewhere.
When you have discovered the documentation then you must correctly implement the
function call in your VBA procedure. The VBA compiler does not recognise WIN API
functions so you must include a Declare statement in your module declarations section
(top of the module) directing the compiler where to find the function. Then you call the
function in your procedure taking particular care that you match the required data types.
In the following example we are using the WIN API function, GetUserName to retrieve
the registered user name from the system:
Private Declare Function GetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
Sub MyGetUserName()
Dim Buffer As String * 25
Dim ReturnValue As Long, UserName As String
ReturnValue = GetUserName(Buffer, 25)
UserName = Left(Buffer, InStr(Buffer, Chr(0)) - 1)
MsgBox UserName
End Sub
The user name is retrieved into the variable 'Buffer', which is a 25 character length
string. Any unnecessary characters are then stripped out. All the WIN API functions have
to be used in the function form, so you need to assign the function to a variable, in this
case the variable 'ReturnValue'. The value of the variable has no particular use other
than to test whether the function has failed or not.
There are a number of books available on the WIN API and you can also search in the
Microsoft Knowledge Base. The information that you need to find is the name of the
function required, how to properly declare the function and (hopefully) an example that
you can copy.
Page 79