The magazine of the Melbourne PC User Group
A Very Basic Approach to the VBA in Excel
 Robin Blair

I wonder how many readers may have contemplated, like me, the clever things they could do with Visual Basic for Applications (VBA) in Excel, and have then found the instructions for programming the language totally opaque. I started by trying to wade through the VBA sections of the Excel help, but found it must have been written by someone who occupies a universe parallel to mine. The next bit of good advice was to record a simple macro by the usual means and then examine the code it produces. A warning: don't do this - the incomprehensibility of the result would frighten off a normal person for life.

As always, the final act of desperation is to spend money on a textbook for the subject. I did. It runs to 800 pages and contains the most erudite descriptions of a vast zoo of VBA objects, methods and procedures, ActiveX, something called the Component Object Model, and much else. One wilts in the expectation that one will have to understand a lot of this stuff before writing a single line of useful VBA code.
 
In the face of all this it was very pleasing, and surprising, to discover that many useful applications can be written if one understands only a small handful of VBA statements - in fact only one is wholly necessary. Indeed, it is not really necessary to be familiar with Visual Basic at all, and anyone who has used QBasic could be writing routines and functions for Excel in only a few minutes.

The key to the simple approach is the use of the "Cells(row, column).Value" statement in VBA for Excel. To get straight into this by way of example, Listing 1 is a short program segment that will write "Hello World" in the group of cells C2 to E7 on the active worksheet. Simple, isn't it? Notice that the row and column references are numeric, not alphanumeric as they would be in normal Excel usage, and they are also reversed. Thus "Cells(3,4)" refers to what would be the cell D3 in Excel cell references, and "Cells(2,3)" would correspond to C2. Why this should be so seems to be just a little more Microsoft quirkiness. Note also the use of the plural "Cells(a,b)", not "Cell(a,b)".
Option Explicit 

Sub Hello_World()
Dim Ctr1, Ctr2
For Ctr1 = 3 To 7
For Ctr2 = 2 To 8 Step 2
Cells(Ctr1, Ctr2).Value = "Hello World"
Next Ctr2
Next Ctr1
End Sub

               Listing 1 		Sub SetRand()
Dim aRng As Range
Dim aCell As Range
Set aRng = Selection
For Each aCell In aRng
aCell.Value = Rnd() * 100
Next aCell
Selection.NumberFormat = "0.00"
Set aRng = Nothing
End Sub

               Listing 2

Listing 1 showed how to assign some value to the contents of cells by using the terminology "Cells(a,b).Value = something". In fact Cells(a,b).Value supports both reading and writing operations. Thus a statement like
 
       MyVar = Cells(a,b).Value 

would assign to the variable MyVar the contents of the cell in row a and column b. Cells(a,b).Value can be a String, as in Listing 1, or it can be numeric. Listing 2, which is possibly more useful than Listing 1, assigns a random number to a group of cells previously selected with the mouse. Listing 2 also introduces a few new terms that greatly expand the usefulness of our very basic approach to VBA.

Two of the new terms in Listing 2 are "Range" and "Selection." Technically, these are Objects or Collections (of Objects), but that detail need not detain us. Think of both as merely identifying a block of cells - a block selected by the mouse in the case of "Selection". In Listing 1, the statement Cells(a,b).Value identifies the contents of the cell on the active worksheet, and similarly "Range.Cells(a,b).Value" designates the cell in row a and column b of the Range. Thus Range.Cells(1,1) is at the top left-hand corner of the identified range. The row and column are not constrained to lie within the Range, and this terminology can be used to write to or read from cells lying outside the actual range. To further illustrate how these terms can be used, here are some examples of isolated lines of code.

 Range("D4:F6").Select
Range("A7:E8").Name = "MyRange"
Range("MyRange").Cells(4,7).Value = "Fred"
For Each aCell in Range("MyRange")

With Range, we can use an alphanumeric cell designation like A6:B7, but it must be enclosed in quote marks to signify that it is a String character.

Something else unfamiliar may be the use of the term "For each" in the code. This is an extension of the Basic operation "For x = a to b" to Objects and Collections. In our basic approach to programming it is used simply to identify all cells in a given Range or Selection.

Finally for Listing 2, notice the use of the word "Set" in the line "Set aRng = Selection". Set is necessary to set-up (or "instantiate") an instance of an Object in memory, and is quite straightforward. A word of warning though-be sure to follow such a statement with one like "Set aRng = Nothing", which must execute at some time before you quit your application. This uses the VBA keyword "Nothing" to clear the memory allocated to the Object. Windows will not necessarily reallocate that memory otherwise, and you could conceivably get an out-of-memory fault effecting all your running Windows applications.

In fact, for simple applications you can avoid using Set altogether. Thus in Listing 2, a statement like "For Each aCell in Selection" would also work. However using the Set statement allows you to refer to the Range elsewhere in your project.
 
And that's it for writing code. You need only be aware of these few statements

Cells(a,b).Value
Range ("ab:xy")
Select and Selection
For Each
Set
to tap into some really powerful functionality of VBA.
 
Now for a few tips on the mechanics of the process. To write code, you must obviously access the window in which the code is contained. In Excel 5 this is a sheet labeled "Module 1" etc, and in Excel 97 is called the VB Editor. The simplest way to access it seems to be to first record a simple macro (even just to select one cell) using the normal method for doing that. Then, from the top menu bar select Tools|Macro|Macros, highlight your macro, and click Edit. This will open the appropriate editing sheet. From here, highlight and delete your little macro and then start writing your own routines like those in the listings herein.

After writing your routine go back to your worksheet by clicking its tab in Excel 5 or by selecting it on the taskbar in later versions. To run your routine, again select Tools|Macro|Macros, highlight your routine and click Run. There are other ways to make your program run, but this way is probably safest for our very basic approach, as it ensures that the appropriate worksheet is visible and activated. Other methods can throw up peculiar errors if the code tries to address cells that may be inaccessible for some reason. Later, you might like to do something clever like assigning your routine to a toolbar button.

Astute readers may have noticed that the random number generator in Listing 2 is the Basic RND() function, not the normal Excel RAND() statement. In writing VBA routines remember that you must use function statements applicable to that language, not those applying in Excel itself. The search facility in VBA Help contains comprehensive descriptions of such VBA statements.

You can write functions in VBA as well as routines. The format is essentially the same as in Visual Basic and QBasic. Functions taking a single cell as input can be called up just as are normal Excel functions. Thus, a function to calculate tax on the income in cell G6, say, would appear in the Excel formula bar as =GetTax(G6). A function, say one named AddUp, that takes its input from a Range, however, must appear in the format =AddUp("B6:F7"). Note the need to include the quote marks, which differs from the usual Excel format appearing in statements like =Sum(B6:F7).

The manner in which Excel reuses code is something of a mystery. My advice is to have not more than one workbook open at any time, as the VBA routines in all can be active and cause unexpected problems of identification. Also, if you wish to copy code from one application to another, copy and paste it through a text editor like Notepad. With direct copying and pasting within Excel, the code retains some ghost of its original application, and can do strange things if that application is not also open.

Those coming directly from QBasic may find the statements "Option Explicit" and "Dim Ctr1, Ctr2" in Listing 1 a little mysterious. The first statement tells Visual Basic that all variables must be explicitly declared before they are used. The second is where the two variables Ctr1 and Ctr2 are actually declared. Neither statement is strictly necessary, but explicitly declaring variables is a very valuable feature of the language. If you were to declare a variable as say MyVar, and later type it as MyVer the compiler would throw up an error message when you try to run the program. This has saved the author many hours of frustration in chasing obscure errors in debugging. So be sure to type "Option Explicit" at the top of your coding sheet; it need appear only once. In fact, the debugger in VBA is very good, but it doesn't have to be understood in detail before you can write useful applications.

Reprinted from the July 2002 issue of PC Update, the magazine of Melbourne PC User Group, Australia

[About Melbourne PC User Group]