Power Up Excel Using Macros And UDFs
Published: May 28, 2013
Author: Glenn Walker
Scrooge McDuck said “Work Smarter, not Harder.” Wiser words have never been spoken. As a child of the ’90s (OK, ‘80s), I place great value on after-school cartoon philosophies.
Part of working smarter is finding tools that help you perform your job better, like Excel. Two Excel features that save me time every day are User-Defined Functions (UDF) and Macros.
They save time by:
– Building customized functions in Excel specifically for my needs.
– Automating repetitive work – for example, sorting by category.
– Streamlining workflows on specific tasks – for example, generating and formatting reports.
– Creating reusable tools that can be applied to several tasks.
In this post, I’ll introduce you to UDF and macro concepts, then take you through a mash-up of several of my favorite sorting and formatting related macros, a handy user-defined function, an example of a workbook with macros so you can really dig in, and links to resources you can use to get started building your own macros and UDFs.
First, a demo (so you can see just why you need to learn this stuff):
What is a User-Defined Function?
Excel has many handy built-in functions (SUM, VLOOKUP etc.), but every user’s needs are unique. Excel provides a way (read how) to write your own functions and create customized reusable functions tailored to your own needs. A UDF takes in your specific data in the form of a cell function and returns a value based on a custom calculation performed.
What is a Macro?
An Excel macro is a set of prescribed instructions for Excel tasks. You can prescribe the actions in two ways: by recording a macro; or, as I have done for this article, by creating a macro using Visual Basic for Applications (VBA) Code. As mentioned before, I will be using VBA, but if you want to record actions, here’s a resource for learning how.
Implementing Macros and User Defined Functions
Even if you are not a programmer, you can use a bit of trial and error to figure out what is going on and hack together your own bits of workable actions. As someone who spent several years in software development, I’ve learned the programmer’s adage: Why code when you can copy (and paste)?
To begin, you need to do several things:
– Access the Developers Bar (video below)
– Create a New Module
– Add Macro and UDF code to newly created module.
Enable the Developer Bar in Excel
Example 1 – Create Modified Broad Match User Defined Function
A common problem in Excel for anyone who works with PPC is the problem Google caused by using plus (+) signs as the modifier to indicate modified broad match terms. You get that nice #NAME response because Excel does not know what you are trying to do.
Moreover, when building keyword lists and using broad match, short common keywords like for, a, or is cut the number of impressions generated by modified broad keywords.
My solution was to create a UDF called MakeModifiedBroadMatch. I use it to process keywords and output them as text (a string, for developers out there) keywords with prepended plus signs.
Once implemented, a UDF is easy to use as a built-in Excel function. For example, let’s say you want to process the keyword in Cell B1 and remove the short words mentioned above. In your cell, type =MakeModifiedBroadMatch(B1,True) – oh, and replace True with False If you want to keep those short words.
The same rules apply as with built-in functions; you can fill down to process multiple keywords.
Watch The Demo
Example 2 – Category to Sheets Macro
I will admit the following macro is probably not the best example for beginners because it’s quite long, but it does give you an example of how you could push the envelope and create powerful recipes to perform several repetitive tasks from sorting to styling. The macro called CategorytoSheet does several things: – Searches the first column of an unsorted table of data and creates a collection of unique categories. – Creates a new worksheet for each category and copies table entries from the parent table to a unique table on each worksheet. – Formats and removes any redundant columns from the new tables. – Adds titles to each new worksheet and styles the new worksheets. – Creates a linked table of contents to make it easy to browse the resulting tables.
(Click to Examine The Source Code)
Download Example Workbook
I’m giving you an example of a workbook so you can see just how the macros function. The .xslm extension indicates a macro-enabled spreadsheet. (Note that you may run into permissions issues on some corporate networks that disallow macro-enabled spreadsheets.)
You will need to enable Macros in Excel to run these tools. Use the following tutorial to learn how.
Be Aware: Macros are customized bits of code, and while they are mostly used for good, they can also be used to introduce malicious code to your computer. Be vigilant and ensure you trust source of any files you do open once you enable macros in Microsoft Office.
Two tutorials in particular were instrumental resources I used for creating these tools.
Working With Tables in Excel 2007 – A great tutorial on the ways you can use tables in Excel Macros. The Trick is they are called List Objects in VBA (think Microsoft : Bing!).
VBA Loops – A listing of loop types in VBA. Looping is a fundamental part of automating repetitive tasks, but not all loops are the same. This list gives you some options and quick explanations.
Good luck! Hit me with comments or questions as they come up.