Ron,

I have a very simple-minded question. I'm trying to learn to write simple macros for Excel 97. I've read several different books but nothing seems to tell me simple things like how to move the cursor from cell to cell and then stop it for data entry. I have a feeling that if I had a glossary of key words, I might be able to figure out how to do what I want to do. For example, the use of an Input statement would do the job if it exists in VB. Do you have a book available that would supply me with what I need?

Milt Wollman

Milt,

Excel programming seems to be on the mind of many of the forum's readers -- yours is the most recent in what seems like a deluge of Excel questions. But I've also decided to address it in the Forum first, since it provides a good starting point for understanding how to control VBA-enabled or script-enabled applications programmatically.

We do publish something resembling a glossary of keywords that you're looking for; its title is VB & VBA in a Nutshell: The Language, and it provides enhanced documentation for the core language component of VBA-enabled applications (like Microsoft Word or Microsoft Excel) and of Visual Basic. I highly recommend it for anyone doing VBA programming.

But keywords -- or the VBA documentation -- won't really help you with navigating an Excel workbook or entering data into a particular cell. That's because, when used within a hosted environment (as is Excel), VBA really serves as a "glue language." Understanding what this means is particularly important for if you want to figure out how to programmatically control many of the applications developed by Microsoft.

A "glue language" -- as is VBA in this instance -- is responsible for providing some basic services (such as support for variable definition and for program control structures like loops) that are essential to any program. Glue, however, is rarely, if ever, useful as a thing in itself; its utility comes from its ability to bind the pieces of one or more objects together. And this is true of VBA programming as well: the VBA language itself does not provide any means of interacting with Excel directly. In the case of Excel VBA, it is the Excel object model that allows Excel to be controlled programmatically and that benefits from the application of the glue that is the VBA language.

Incidentally, this separation between an object model and the programming language used to access it is typical of Microsoft technologies and accounts for the language independence of many COM (or Component Object Model) objects. (You use a programming language to access the properties and methods of an object that is not part of the programming language, but belongs to some external component.) It also seems to confuse those unaccustomed to this separation, and particularly those who work with other object-oriented development technologies. But it's important to keep this separation in mind, since it's essential to the notion of component-based development.

So to manipulate the cells of an Excel worksheet or chartsheet, you need to access the Excel object model. For instance, to enter data into the active cell, you can simply set the ActiveCell property of the Application object. The code

Application.ActiveCell = 10

enters the number 10 into the current worksheet's current cell. To enter data into a designated cell, you can use the Range property of the Activesheet object. The code

Application.ActiveSheet.Range("C1") = 10

enters the number 10 into cell C1 of the active worksheet. There are numbers other ways to enter data into cells programmatically.

Rather than having all sorts of visible activity going on in your worksheet as the cursor moves from cell to cell (something common with early spreadsheet macros but rare today), it's far better to have the user input data into some interface object, and then to programmatically transfer it to the appropriate cells of the workbook. If the data consists of the fields of records arranged in tabular format (which can be accessed using the Form option from the Data menu), data entry can be handled automatically by a code fragment like the following:

Application.ActiveCell = "A1"
Application.ActiveSheet.ShowDataForm

Excel automatically handles the process of transferring new information to the worksheet. In simple applications, it's also possible to use the VBA InputBox statement, although for more sophisticated cases, it's better to create an Excel UserForm.

Within the next week or so, we'll be publishing a book by Steven Roman, Writing Excel Macros, which provides an introduction to VBA programming for Excel. It contains the basic information that you need to know to use the Excel object model and to create Excel UserForms.

--Ron

Return to: Ron's Archive