Excel VBA: No Experience Necessary - Lesson 1

We all know that Microsoft Excel is an extremely powerful piece of software, with more capabilities than most of us (including myself) could ever hope to wrap our tiny brains around. However, you may sometimes find yourself spending what seems like too much time doing repetitive or manual tasks in Excel. Shirley, you thought, there must be a better way. Well, unless you know someone named Shirley, you may have to learn how to do something about it yourself.

Excel, Word, and PowerPoint each include a version of the programming language Visual Basic called VBA (Visual Basic for Applications), which can be used to automate such tasks. In this series of articles I will attempt to give you just enough knowledge to make you dangerous, even if you have little or no background in programming.

The approach I’m going to take is to go through a few examples without getting into a lot of technical discussion and definitions yet. My goal is just to get you started. In the spirit of “It’s OK to not know as long as you know what you don’t know” I’ll point out a few topics that I will not be discussing yet. I’ll try to circle back in a future article to cover such things, or at least point you to another resource.

Recording a Macro

A macro is a stored sequence of actions which may be executed as a single action (and which may be executed multiple times). A macro is also known as a subroutine. (I’ll use the terms interchangeably here.) Your VBA code will be in the form of subroutines and functions.

Excel, like Word, allows you to record a series of actions as a macro. When you record a macro, Excel actually produces VBA code to mimic your actions. Try this:

• Open Excel
• Click on menu item Tools, Macro, Record New Macro
• In the Record Macro box, type in the macro name, “Example1”. Click OK
• You should now see a small toolbar labeled “Stop” with two buttons.
• Click on cell B2
• Type “Hello Excel” and push your Enter key
• Highlight cell B2 again and change the font to bold by clicking the “B” button on the toolbar.
• Stop recording your macro by clicking the first button, with a small blue square, on the toolbar which appeared when you started recording.

You’ve now written four lines of VBA code! To see it, go back to the menu and click on Tools, Macro, Visual Basic Editor. (Keyboard shortcut Alt + F11).

In the Visual Basic editor, you will see a tree structure on the left representing your Excel workbook and its associated code. The folder called “Microsoft Excel Objects” includes an item for each worksheet in your workbook, as well as an item for the workbook itself. There is a second folder called “Modules” in which you should find an item called Module1. Double-click on Module1 to display the code you recorded. It should look something like this:

Example 1.

Sub Example1()
   '' Macro1 Macro
    ' Macro recorded 12/19/2003 by Mark “Shirley” Thorpe

Range("B2").Select
    ActiveCell.FormulaR1C1 = "Hello Excel"
    Range("B2").Select
    Selection.Font.Bold = True
End Sub

Running a Macro

Next try running your macro. First clear cell B2, then click on Tools, Macro, Macros. Click on Macro1 and click the Run button, or just double-click on the macro name.

Visual Basic Editor

As I mentioned earlier, the Visual Basic Editor is accessible by selecting menu item Tools, Macro, Visual Basic Editor, or by using Alt + F11 on your keyboard. (Alt + F11 will also switch you back to Excel itself from the Editor.) The Project Window, which appears on the left side of the screen holds a tree structure which shows up to three “folders” for each Excel file currently open:

1. Microsoft Excel Objects: one object representing the workbook, one for each sheet in the workbook
2. Forms: user-defined forms contained in the spreadsheet (covered later)
3. Modules: collections of procedures and functions

Double-click on a module or worksheet to see the code associated with it. The code window will appear on the right.

Important Note!
You can usually figure out how to code any action in Excel by recording it in a macro and viewing the resulting macro code.

Visual Basic Editor

Loopy

This next example will introduce you to the “for loop” which is particulary useful in Excel. A for loop allows you to repeat the same action a specific number of times. Type in, or copy/paste the following lines into your VBA Editor, below your Macro1:

Example 2.

Sub Example2()
    For x = 1 To 5
       Cells(x, 3).Select
       Selection.Vaue = x + 1
    Next x
End Sub

Next run the macro directly from the VB Editor by putting your cursor somewhere within the macro and clicking on the small blue triangle button in the toolbar. (Or push F5 on your keyboard.) Switch back to Excel with Alt + F11. You will see the numbers 2 through 6 in column C.

We’ve used the variable x to represent the row number of a series of cells and we’ve used a “For… Next” statement to loop through the first five rows.

Stepping Through Code

Clear column C, then resize the VB Editor window and the Excel window so that you can see both at the same time. Click to put the cursor within the Example2 macro code, and push F8. This will “step” you into the code. Push F8 repeatedly to see your code executed a line at a time.

Referencing a Cell

In the two examples above, we’ve used two different methods for referencing a cell. In the first example, we used Range, while in the second example we used Cells. There are a variety of ways to use these two, but I’ll cover just the basics. Range can be used to access one or more cells by specifying the cells in the standard Excel notation, where the column is represented by a letter, and the row by a number. Cells is used to access a single cell by specifying row number and column number, in that order. Try adding these lines to a macro and stepping through them with F8:

Example 3.

Sub Example3()
   Range("D5").Interior.ColorIndex = 3
   Range("C2:E4").Value = 100
   Cells(7, 2).Borders.LineStyle = xlDouble
End Sub

Generally, if you’re using a loop as we did in example 2, you’ll use Cells rather than Range. I mentioned above that you can learn how to do almost any Excel action in VBA by recording a macro and looking at the resulting code. However, if you want to do the same action repeatedly, your best bet is to take the recorded code, put it inside a for loop, and replace the Range cell reference, (e.g. Range(“B2”) from Example 1), with Cells, where you can use a variable to specify the row and/or column.

An Actual Useful Example

Example 3 is a subroutine which will sort the active worksheet by first column, then remove any duplicates. You should notice a number of things:

• The first line sorts the contents of the spreadsheet using Cells.Sort. The column to sort by is specified with the Key1 argument. Arguments is a topic I haven’t covered yet.
  
• I’ve used “ActiveSheet.UsedRange.Rows.Count” to determine how many total rows are actually used in the worksheet. This is an example of Excel objects, properties, and collections, topics I haven’t covered yet. Even if you don’t know much about these things, ActiveSheet.UsedRange.Rows.Count can be extremely valuable to you in writing Excel VBA code.
  
• I’m counting backwards in my For loop by using “Step –1”. You can use Step with other values, for example Step 2 if you want to highlight every other row. I’m counting backwards in this case because I am deleting rows. If I count forwards, I will be skipping over rows which have moved up to take the place of rows which have been deleted.
  
• If you haven’t already figured this out from the previous examples, you can get (or change) the contents of a cell by using .Value
  
• I am using an If … Then statement to determine if the contents of two different cells are equal. If you’ve done any programming at all, you already know about If … Then. It allows you to check whether a certain condition is true, and, if so, execute one or more subsequent statements.

Example 4.

Sub RemoveDuplicates()
Cells.Sort Key1:=Range("A1")
totalrows = ActiveSheet.UsedRange.Rows.Count

For Row = totalrows To 2 Step -1
If Cells(Row, 1).Value = Cells(Row - 1, 1).Value Then
Rows(Row).Delete
End If
Next Row
End Sub

This subroutine is included in the spreadsheet linked below. The spreadsheet contains data which will allow you to see the subroutine in action. While this subroutine can be “useful”, you’ll notice that the data in this spreadsheet is definitely not. The data is copied on Sheet2, so if you want to run RemoveDuplicates more than once, simply copy the original data from Sheet2 back to Sheet1 before rerunning.

Download: Excel VBA Article 1 Example.xls [.zip compressed file, 8k]

Something to Try

If I haven’t bored you to tears, or hopelessly confused you, and you’re still interested in learning to do more with Excel VBA, here’s an exercise for you to try on your own…

Starting with the spreadsheet above, modify the RemoveDuplicate function above so that it keeps track of the total number of instances of a given record (e.g. number of Shaggy’s), and puts the total for each record in column 3. When finished, these numbers should sum to 100, the total number of rows in the original data.

Good luck—watch for Lesson 2 next month!

- Mark

Need further help getting your Excel VBA code right? Join our free Excel VBA support group! See this link for details: http://groups.yahoo.com/group/ExcelVBA/ .