Excel VBA Macro Programming

Overview

Although the Excel object model and VBA code is very comprehensive in providing you with methods to do various operations on the spreadsheet, you may have noticed that there are a number of functions having to do with Windows that you still cannot do. Even Visual Basic itself does not provide the direct means to do some of these.

For example, you cannot find the amount of available space on a disk device. You cannot read the keyboard directly ‚ you only can read incoming keys on a user form, provided that the form or control has the focus. You cannot determine where the mouse position is. Obviously, there are ways of doing these things, since Windows Explorer can tell you how much space is on your hard disk and Windows knows which key you pressed. VBA does not have the direct commands to deal with these subjects, but it does allow access to the WIN32 application programming interface (API), which in turn allows you to access a treasure chest of information from Windows directly.

API calls are a very advanced subject and can provide an enormous amount of extra functionality to your programs. It is not the purpose of this book to go too deeply into API calls, however, but I will show you some examples of how they can be used to your advantage.