More musings on making macros.
This week, I thought I’d expand upon my last blog and demonstrate some Excel tips and tricks for modifying your macro. Despite the fact that we’ll be using the Visual Basic editor in Excel, you can still do these tricks without any prior programming experience.
Our goal this week will be to take our Sample Macro from last week and adapt it to a new situation. Let’s say we have the following worksheet:
We’d love to use our new macro, but it won’t quite work in this case – there are two date columns that need to be formatted, and two amount columns. In this case, a simple edit of the macro can accomplish what we need!
First, you’ll need to make sure the “DEVELOPER” tab in your Excel is enabled. If it is, you’ll see it on your ribbon like this:
If it’s not there, don’t panic! Microsoft hides it by default, but it’s easy enough to reveal:
- Click the ‘File’ tab;
- Click ‘Options’ on the left bar;
- Select ‘Customize Ribbon’ on the left;
- For ‘Customize the Ribbon’ (upper right), select ‘Main Tabs’;
- Check the box for ‘Developer’, then click ‘OK’;
Now that the Developer tab is visible, click on it, then click the “Visual Basic” button on the upper far left.
Whoa! That’s a pretty intimidating window that opens up, isn’t it? Don’t worry, we’re not going to be doing anything too difficult here. On the left, you’ll see a list of your VBA Projects (macros you’ve created). Yours will likely look different than my example below; if the SampleMacro was your first macro, then click on “VBAProject (PERSONAL.XLSB)”, then double-click “Module 1.” The main window should now look similar to mine below:
Time to modify! In the main window, where it says “Columns (“C:C”).Select”, change it to say “Columns(“C:D”).Select” – this tells Excel to select both columns C and D, which get formatted with the next line. Next, change the line “Columns(“D:D”).Select” to “Columns(“E:F”).Select”. As you might guess, this tells Excel to select columns E through F. The screenshot below should help illustrate:
Now, the only step left is to save, by clicking the Save icon in the upper left.
And that’s it! You can now close the VBA window, which will return you to your spreadsheet. Press ‘CTRL + u’ (or whichever key press you originally bound the macro to), and it should format your new spreadsheet just as easily as the old! Below is what mine looked like after running the macro:
