Excel Macros For Dummies
November 30, 2011 2:23 PM   Subscribe

Explain to me Excel macros and how I can learn to program them (assume that I know absolutely nothing, because that would be correct).

A few years ago, I had a boss that was an Excel macro genius. I did photo keyword tagging for him, which was very data entry heavy. He was able to program information that had been inputted into one part of a spreadsheet to automatically repeat in other parts of the spreadsheet; it was also color-coded.

This was the first time that I'd ever seen Excel macros used, and I thought it was amazing. However, I haven't been in an environment since where I've had the opportunity to see this in action again, or even known other people who were able to program Excel macros.

Still, it's dawned on me that this is a very useful skill. However, it looks quite intimidating, and I'm not even sure about what you would use them for beyond data entry. Hive mind, could you please explain to me what Excel macros might be used for, and perhaps good tutorials so that I can try my hand at it?
posted by so much modern time to Computers & Internet (8 answers total) 21 users marked this as a favorite
Excel macros are written in a programming language called VBA (Visual Basic for Applications). If you "record" a macro in Excel, it will create a VBA program that performs the steps you take in Excel while the macro is recording. If all you want is for the macro to repeat the exact steps that you took while recording, this is all you really need to do, but chances are you will need to edit the program to make the macro more general.

In order to use Excel macros to their full extent, you'll need to learn to program in VBA. VBA isn't a particularly difficult language but programming is a skill that takes time and effort to learn. In addition to learning VBA you'll need to be familiar with the Excel object model which is what you use to interface between your VBA code and the Excel application.

It's worth noting that VBA is a very old language and is likely to be replaced soon in a future version of Office, probably with some variant of VB.NET.
posted by zixyer at 2:40 PM on November 30, 2011 [1 favorite]

The macro recorder is Your Friend. Combined with a boring weekend of learning just a smattering of VBA (Visual Basic for Applications), it'll make you a macro-writing maven.

You know those repetitive tasks that you have to do in Excel? Well, before you start one, fire up the macro recorder. Then go through your task as you normally would. Then stop the macro recorder. Now take a look -- what the recorder has captured is every action you just made, and it's translated those actions into a language Excel understands: VBA. It may all look like a bunch of gobbledygook to you right now, but that's OK! That's why you bought that big honkin' doorstop of a reference book on VBA! Go through that macro you just recorded, line by line, referencing your VBA book. Soon you'll see what that previously abstruse code is telling Excel to do. It's a really specific thing, right? It says "Take the data in A2 on this spreadsheet and copy it to D3 and then make that cell turn red", or whatever. Which is all well and good IF you're just using that same spreadsheet. But what if you want to generalize? What if you want that macro to work on ANY spreadsheet, or across MULTIPLE spreadsheets? No prob, Jim Bob. VBA has got your back. You can use variables! A variable is when instead of looking in a specific cell every time, Excel waits for you to tell it where to look. There's tons of ways to set variables. You can make an input box to type it in. You can tell Excel to designate the highlighted cell as the one to start the macro on. Heck, you can even tell Excel to run all the macros you want as soon as you fire up the program, so you can fetch your coffee while it crunches the morning data. You can tell Excel to look in a specific file on a specific computer and to use that data (which may need massaging, but hey, that's just a matter of recording another macro to reformat the data!).

So do this. Make a copy of your Important Spreadsheet. Name it TestBed or Oops or whatever. Now go to town on that sucker with the macro recorder. Iterate, iterate, iterate. Keep watching as the code changes, and keep referencing your VBA manual. I guarantee that within an hour or so of your first attempt you will have irretrievably fucked up that spreadsheet. S'ok, it's a copy! Delete it and try again.

Eventually the light will come on, and you'll have Excel jumping through flaming hoops.

It's really not that hard!
posted by BitterOldPunk at 3:03 PM on November 30, 2011 [6 favorites]

zixyer expressed it perfectly. start by recording macros and reading/editing them. you need a book - doesn't matter which one, just buy one from amazon that matches your version. it's easy and fun. as a beginner you're basically just going to be stealing other people's ideas and adapting them to your own purposes, you can get away with not really knowing how to program. there are google/usenet groups devoted to the subject, and you can get expert advice in minutes, usually.
posted by facetious at 3:05 PM on November 30, 2011

I taught myself to program Excel macros and started out learning the basics with this book that doesn't appear to have been updated since Excel 2000. I have no programming background but you're correct- it is a very useful skill if you have a job that requires a lot of Excel usage and learning VBA made a huge difference in advancing my career.

My advice would be to check out all of the Excel VBA programming books you can at the library and dig around to see if there's one or two that you like. Walking through the exercises in the books is a great way to get a feel for how things work. I've taken some classes but nothing has advanced my progress more than personal exploration. Once you learn the basics you'll have a better idea of how to progress. And don't underestimate the power of Google to help you with specific problems- I've never had a programming problem that someone else hasn't already had and solved. The macro recorders are great but once you start learning programming it's like a whole new world opens up. Also, once you see what macros can do you'll be able to find your own applications for macros.
posted by shornco at 3:07 PM on November 30, 2011

I work at a university, and have found that there are really terrific tutorials on .edu sites. A google search for excel macro tutorial site:*.edu will return lots of free tutorials. When the macros get more complex and interesting, I'd look for some good discussion boards, or search Usenet, as well as the web. Microsoft's support pages are often very good, too, just use Google to search them.
posted by theora55 at 3:59 PM on November 30, 2011

Aside from recording macros, if you really, really, want to learn to create them, then just grab any random 'introduction to programming' book off the shelves. Because it's programming like any other kind of programming, even though it's a very specialized use case.
posted by empath at 5:30 PM on November 30, 2011

If you want to do some of the things this boss was doing, you might also look at "conditional formatting," which is much less powerful than macros but also easier to do.
posted by whatzit at 3:13 AM on December 1, 2011

When you say "He was able to program information that had been inputted into one part of a spreadsheet to automatically repeat in other parts of the spreadsheet" what's an example of that? I have a feeling you'll be able to do what you want with formulas.

The most basic - if you want the exact contents of a cell to show up in a nother cell you can simply type =Cell (e.g. =A6)
If you want to search for specific cell contents, start by playing with the vlookup function.
posted by jander03 at 2:20 PM on December 1, 2011

« Older Looking for wireless computer speakers   |   Help my get all my stuff to sync properly. Newer »
This thread is closed to new comments.