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
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.
- Open Excel
- Click on menu item Tools, Macro, Record
- 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:
'' Macro1 Macro
' Macro recorded 12/19/2003
by Mark “Shirley” Thorpe
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
Excel Objects: one object representing the workbook, one for each sheet in
user-defined forms contained in the spreadsheet (covered later)
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.
You can usually figure out how to code
any action in Excel by recording it in a macro and viewing the resulting
Visual Basic Editor
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:
For x = 1 To 5
Selection.Vaue = x + 1
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:
Range("D5").Interior.ColorIndex = 3
Range("C2:E4").Value = 100
Cells(7, 2).Borders.LineStyle = xlDouble
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:
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
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.
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.
you haven’t already figured this out from the previous examples, you
can get (or change) the contents of a cell by using .Value
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.
Row = totalrows To 2 Step -1
Cells(Row, 1).Value = Cells(Row - 1, 1).Value Then
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
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!
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/ .