The Holy Grail of Excel Macros

Okay, so, you just got handed a job for a 100+ page workbook. Your client wants you to fix some formatting or something of the like on every page (the 100 pages already exist… creating that many is a different story…). What do you do? You use macros and let the computer do all the work, silly! 😛 The best part is you can charge for all the hours it would have taken you to fix it all up without the snippet I’m about to give you! (Okay, this is probably a tad unethical…).

You’re interested? I thought so. We’re going to use the power of foreach loops to let the computer run through and do all this on every single page. The workflow I use looks something like this:

  1. On the first page (or a “test” page), I hit Record Macro and name it something I can remember. Be sure that you remember what the exact name of it is. This is very important. 
  2. Proceed to style/do whatever it is that you’re being paid to do. Ahem. 
  3. Once you’re done, stop recording the macro. At this point, I usually like to test the macro before I use it on my entire workbook, just to make sure it isn’t going to destroy it all. 😛
  4. If you have verified the integrity, now we need to write a bit of code. :O But I have never used the Visual Basic editor! Don’t worry–it’s incredibly simple and you can basically copy and paste this snippet I’m about to give you and just change one little bit for all your repeating macros. 

Let’s get on to the code now, shall we?

Remember to replace the “MyOriginalMacroName” with the exact name of whatever you called your macro. See the highlighted lines. I like to add the suffix “All” to the macro’s original name. This keeps large amounts of macros organized and modular.

Sub MyOriginalMacroName_All()
    Application.ScreenUpdating = False
    Dim sheet As Worksheet

    For Each sheet In ActiveWorkbook.Worksheets
    Next sheet

    Application.ScreenUpdating = True
End Sub

Let’s break this snippet down a bit. If you don’t know anything about Visual Basic, don’t worry. It’s actually really easy to understand if you have any prior programming experience. First, you are declaring a subroutine (function) which Excel sees as your macro. If you look around in the Visual Basic document, you’ll probably see the Excel-generated macro (hopefully you didn’t write that really messy code…). You can run this new function in Excel like any other macro.

I won’t get too much into the code, as this is really more of a tutorial on running your macro on every worksheet in a workbook, not the VB Macro API. 😛 Line 3 and 10 basically tell Excel to stop updating the screen when you run your macros. If you leave this code out, your macro will take forever to run in really large workbooks. For reference, it took my relatively fast machine about 2 minutes to run a simple macro on every page with the screen updating. Without the screen updating, it took 5 seconds.

In the “middle”, you have the foreach loop that makes it all work. Basically, we are iterating over every single sheet in the workbook and running that macro you created. This may not be the most efficient way to do things, but it is by far the easiest way I have found to do this. If you want to learn more about how exactly it works, you can read up on Visual Basic and the VB Macro API.

If you did everything correctly from start to finish, you should be able to run this macro and have the formatting be applied to every single sheet in the workbook. “Yay! Thank you for saving me 8 hours of tedious work!” said everyone that has put this to good use. 😛

Let me know if you have any questions or need help. Keep in mind that I’m not going to do the actual macro for you… unless you have money. 😛 Happy coding everyone!


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s