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 Im 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 Its OK to not know as long as you know what you dont know Ill point out a few topics that I will not be discussing yet. Ill 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. (Ill 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.
Youve 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
ActiveCell.FormulaR1C1 = "Hello Excel"
Selection.Font.Bold = True
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:
- Microsoft Excel Objects: one object representing the workbook, one for each sheet in the workbook
- Forms: user-defined forms contained in the spreadsheet (covered later)
- 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.
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
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.
Weve used the variable x to represent the row number of a series of cells and weve 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, weve 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 Ill 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 youre using a loop as we did in example 2, youll 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 havent covered yet.
- Ive 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 havent covered yet. Even if you dont know much about these things, ActiveSheet.UsedRange.Rows.Count can be extremely valuable to you in writing Excel VBA code.
- Im 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. Im 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 havent 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 youve 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.
totalrows = ActiveSheet.UsedRange.Rows.Count
For Row = totalrows To 2 Step -1
If 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, youll 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 havent bored you to tears, or hopelessly confused you, and youre still interested in learning to do more with Excel VBA, heres 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 Shaggys), 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 luckwatch for Lesson 2 next month!