About Excel Macros

Macros in Excel are "little programs" that do things to data, retrieve data, generate data, or just about anything else in Excel.  You can automate just about anything in Excel using macros and assign that macro a shortcut key, which is useful if you have repetitive actions that you do frequently.  You'll find some useful macros for wildlife, ecology, and other monitoring or research data that I've already created and saved to my "Colin's Macro Library.xls" file.  Most of the macros in the worksheet have shortcut keys and are ready to run once the file is open on your computer, and the macros can be run in any Excel spreadsheet that's open on your computer as long as the macro library is open at the same time.

Below, I'll walk you through how to write your first macro.  The example will allow you to convert acres to hectares with the push of a shortcut key.

Shortcut Keys

Shortcut keys are a set of keyboard buttons that when you press them at the same time do an action.  For instance, the keyboard shortcut for copy in most applications in windows is Ctrl+c.  If you press a "CTRL" button on your keyboard and then, while still holding that button, click the "c" button, it will copy what ever you selected or your cursor is on.  You can then press "Ctrl+v" to paste that item somewhere else.  In a way, these are "macros" that are already programmed into Word, Excel, and many other programs.

Troubleshooting

If you're having trouble using my macro files, be sure your security settings are set correctly.  Instructions are on this page here.  If you have questions about the safety/security of my downloadable files, see information on my Downloads page.

If you want to learn more about what macros are and how to create them, read on:

Learning to Write Macros

By no means am I an expert in Excel macros or the Visual Basic programming language, nor have I had any formal training.  However, I trained myself first by recording simple macros using the "macro recorder" tool, which I'll explain below, and then I learned a little about the programming language by looking at the macros I created with the macro recorder and by reading about them on online forums (see links below).  I'm still learning new things all the time.

I. Macro Recorder

My first recommendation is to use the macro recorder to record a macro to see what it does:

1. Enter some data into an Excel spreadsheet if you wish.
2. On Excel 2007/2010, click on "View" and them "Macros"
3. Click on Record Macro.
4. Enter a name for that macro (optional), a shortcut key (optional), and other information.
5. Type, click around, highlight, or click formatting or other buttons.
6. Click on the Macro button on the ribbon and select "Stop Recording"
7. Click on Macros, View Macros, and Run (or hit your shortcut key) to run the marco.  Click on Edit to see the macro program text.

Example:  Macro to Convert Acres to Hectares

Let's do an example.  This macro will convert a number from acres into hectares when you press the key combination "Ctrl+a".  There are a lot of steps, but they should be simple.  Try to stick with it and not get intimidated:

1. Enter a number, such as 5.5 in cell A1, and hit enter.
2. Place your cursor back on cell A1.
3. Click on the "View" tab above the Ribbon, click on "Macros"
4. Make sure the symbol to the left of "Use Relative References" is higlighted/selected.
5. Then click "Record Macro"
6. On the screen that comes up, name the macro ConvertToHectares (no spaces allowed) and type a lower case "a" into the shortcut key box.
7. Click OK and you'll be place back on the spreadsheet with your cursor where you left it on A1.
8. Click the right arrow, which puts your cursor on B1.
9. Type: "=", click the left arrow, type "*.404685642", and hit the enter key.  The cell in B1 should have read "=A1*.404685642" before you hit enter.
10. Hit the up arrow to put the cursor back on B1.
11. Press Ctrl+c to copy the number (should read 2.225... if you originally entered 5.5 in A1)
12. Push the left arrow to put cursor on A1.
13. Press the following key strokes (press and release rather than hold them down at the same time): "ALT", "e", "s", "v".  This brings up the Paste Special menu and tells it to paste values only into A1.
14. Press the right arrow key to put cursor back on B1.
15. Hit the delete key.
16. Click on "Macros" and "Stop Recording".
17. Type another number in another cell, place your cursor on it, and then press the shortcut key Ctrl+a and it should convert that number to hectares.

BE CAREFUL:  You can only use this macro if the cell to the right of the number to convert is BLANK, otherwise, the data in that cell will be deleted.  There are ways to get around this limitation, but for keeping the macro simple, this is how I've done it.

Relative References

In the example above using relative references, if you have a number in any cell of a worksheet, say B5, D18, etc, running the macro would replace that number with the converted number.  However, if you had unselected "Use Relative References" before recording the macro, the difference would be that no matter where you ran the macro (from cell B5, D18, etc) it would convert and replace whatever value is in cell A1.  This is because A1 was the cell that the macro was originally referring to when it was written, i.e. the absolute cell reference was retained in the macro, rather than running the macro relative to where ever the cursor is placed.

II. Reading and Editing Macro Programs

My second recommendation in learning to write macros is to read/edit the macros that you created using the macro recorder.  To do this, click on "View", "Macros", "View Macros", select the name of the macro (ConvertToHectares in our case), and click "Edit".  Here is the program text for the macro we just wrote in the example above:


Sub ConvertToHectares()
'
' ConvertToHectares Macro
'
' Keyboard Shortcut: Ctrl+a
'
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*0.404685642"
    ActiveCell.Select
    Selection.Copy
    ActiveCell.Offset(0, -1).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveCell.Offset(0, 1).Range("A1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub

The same macro recorded with Relative References turned off is:


Sub Macro5()
'
' Macro5 Macro
'
' Keyboard Shortcut: Ctrl+b
'
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]*0.404685642"
    Range("B1").Select
    Selection.Copy
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub

Notes about reading the macros:

• The first lines of data after the "Sub..." command, which names the macro, all start with a single apostrophe.  In the Visual Basic (VB) editor window, the text after the apostrophe will be green.  This character is used at the beginning of a line to say "ignore everything on the line after the apostrophe."  You can enter notes and other text after the apostrophe and the macro won't "read" it as programming language.
• The first line of programming for both macros (after the apostrophe lines) selects the cell to the right of where you run the macro from.  In the Relative Reference macro (blue), it's a little deceiving because cell "A1" is referred to, but in an "AciveCell.Offset" command, it's still a relative reference saying that it's a 1 row x 1 column selection (one cell only).  The second macro shows that the absolute range of cell "B1" is selected and no matter where you run the macro from, it will first select cell B1 as the active cell.
• The second line uses the R1C1 format (for Row1Column1 format).  This is a relative cell reference code.  "RC[-1]" tells the macro to select or use data from the cell that is 0 rows and -1 columns from the active cell.  "R2C3" would be selecting or using data from the cell that is 2 rows below and 3 columns to the right of the actively selected cell.
• The rest of the macro is copying the data in the active cell, selecting the cell to the left of the formula, pasting the value only, and then selecting and erasing the cell with the formula in it.

This all might sound and look intimidating, but if you keep at it you'll slowly pick up clues and hints about what various commands are doing and then you can slowly customize your recorded macros or start ones from scratch to do more complex things.

III. Helpful Links

To learn more, peruse the internet!

Here's another website with an example of how to write your first macro:
  http://www.mrexcel.com/articles/record-modify-run-excel-macro.php
  Of course you can google "how to write excel macros" and you'll get many returns.

Some of my favorite Excel help forums where I've learned quite a bit are:

  http://www.mrexcel.com/

  http://www.excelforum.com/

  http://excelhelp.org/