To date, our community has answered nearly 2 million questions in the Smarterer Excel test, making it the most popular test on the site. Excel is an essential and powerful tool that nearly every professional finds themselves needing at some point. In our previous posts, we helped you out with Excel shortcuts, formulas, and keystroke macros. This week, we will introduce you to another type of macro – one that has a steeper learning curve for beginners, but can save you precious hours once you become proficient at it.
Automate Your Way
Are you tired of tasks that are repetitive and time-consuming? While keystroke macros can automate many tasks, they don’t always work as expected. Macros are essentially a set of instructions written in a programming language called Visual Basic (aka VBA), for Excel to interpret and perform specific actions. As a result, not all sequences of actions are interpreted by the program correctly.
By knowing how to program in VBA, you will have full control of the tool and be able to ensure that your macros work 100% correctly.
Simple Case Study
Suppose you’re a marketer using Excel to clean up a large email list. Every week, your boss needs you to break the list into segments in different worksheets. Instead of looking for the break points for each segment, cutting & pasting into the separate worksheets, you can achieve this with a few lines of simple VBA code! It’s well worth the effort if it is a recurring task.
Enabling VBA Macros
1. To use Macros, you must save your workbook into a new file. Choose “Save as”, and select “Excel Macro-Enabled Workbook (*.xlsm) for the file type.
2. Click the Microsoft Office Button, and then click “Access Options.”
3. Click “Trust Center”, then click “Trust Center Settings” on the right, and then click “Macro Settings.”
4. Select “Enable all macros”.
5. Create two new worksheets, one with name “Sheet1” and the other “Sheet2”.
Programming VBA Macros
1. On your keyboard press “ALT” together with “F11″. The Visual Basic Editor will open. To go back into Excel, press “ALT + F11″ again.
2. Before you can start writing any code, you need to insert a module. Per the screenshot below, click on the Ribbon of the editor and select “Module” from the dropdown menu.
Defining the Subroutine
1. The most common type of program in VBA is called a subroutine. You will start writing a subroutine by typing the word sub at the top of the module you created earlier.
2. The next thing to do is type in a name for your subroutine. We’ll call it “copy_range()” in this case. Remember not to use spaces and try to avoid punctuation characters in the name.
Segmenting the List
Suppose the email list has 10 entries, and you only need to break the list into halves, each on a new and different worksheet. (For the sake of this tutorial, we’ll keep the example extremely simple. Obviously macros are meant for very large data sets).
Inside the subroutine, write the following code:
Explanation: Range() is a method for you to specify which cell you want Excel to pay attention to. In this case, we want to copy the first half of the list into a new sheet. Hence, we define the range to be from cell A1 to C5. To select the range, the .Select part of this line of code performs the action, equivalent to below:
Once the range is selected by the first line of code, the .Copy part performs the “copy” action, putting the range on the clipboard to allow you to paste it into other cells/worksheets.
To paste the first half of the mailing list into worksheet called “Sheet1”, we need to select the worksheet first. Again, .Select performs this action for you.
Once “Sheet1” is selected, it becomes the active sheet (just like the name indicates). Similar to the .Copy action in step #2, .Paste performs the action of pasting the data into “Sheet1”.
That’s it! The code for the entire subroutine should look exactly like below. If so, you’ve just programmed your first VBA macro!
Now hit ALT+F11 to go back to Excel. Click “Developer” on the ribbon, and select “Macros”. You’ll see the method called “copy_range” in the menu. Select it, hit “Run” and watch the magic.
We hope that these tips will save you hours, so you can come back to Smarterer learn other subjects in the mean time! As usual, Bill Jelen, the renowned “Mr. Excel” and Microsoft MVP, offers videos showing many tricks on programming programming VBA Macros.
Stay tuned for Part 6 in our Excel series, Loops in VBA Macros — and let us know what other Excel skills you’d like to see here!