Excel tutorials for data analysis, productivity and general happiness
Excel tutorials for data analysis, productivity and general happiness
This is a platform for User Generated Content. G/O Media assumes no liability for content posted by Kinja users to this platform.

# Create the Excel Function of Your Dreams

Do you ever find yourself using the same formula, day in and day out, and think, “Wow, I wish Excel just had a function for this.” Turns out, you can create your own functions in Excel to do whatever you want (for the most part)!

For example, in digital advertising, something that gets calculated a lot is CPM, aka cost per mille, aka cost per 1000 ad impressions.

If an advertiser pays \$10,000 for 1,000,000 impressions, we can figure out the CPM by:

Total cost / (Total Impressions / 1000)

or

Total cost * 1000 / Total Impressions

So,

\$10,000 * 1000/ 1,000,000 = \$10 CPM

The CPM calc is simple enough to write in Excel, but given the number of times I need to perform this calculation, it would be nice to have function for it.

So let’s make one!

## Where to start

User defined functions are created with Visual Basic for Applications, Microsoft Office’s own programming language. Visual Basic is useful to know, but fairly annoying to learn and work with. Luckily, you don’t need to know much to create your own function!

To write our function, we need to open the Visual Basic Editor. How you access the editor will depend on your computer and Excel version: in Excel 2007 and 2010 on a PC, you need to go to the a Developer tab (or add this tab to your ribbon through Excel Options) where you’ll find a Visual Basic button. For Excel 2011 on Mac, which is what I have and am showing in the screenshots in this article, go to Tools > Macro > Visual Basic Editor.

In the editor, create a Module to store the function code. Go to Insert > Module to do so.

And now we have a ... blank box! This is where we’ll write our function.

## Function Basics

Your function needs to abide by the following format

Public Function YourFunctionsName(variable1 as <data type>, variable2 as <data type>)

Do something

YourFunctionName = do more stuff

End Function

Here’s a very simple example, of a function that multiplies two numbers together:

Public Function MULTIPLY(x as Double, y as Double)

MULTIPLY = x * y

End Function

Let’s walk through these functions step by step.

• All the code for your function must be between `Public Function` and `End Function`. Technically, you don’t need the word `Public`, but this ensures that there are no restrictions to accessing it, which is fine unless you’re making some top secret function that you’re distributing widely but don’t want people to copy.
• Name your function. Just make sure it’s not the same as any existing Excel functions! (i.e. don’t name it SUM)
• Define the input variables. You need to tell Excel how many and what type of inputs to expect. In the case of MULTIPLY, we need two numbers (defined as `Double`, which means they can have decimals), but there may be cases where your function takes text (`String`), true/false values (`Boolean`), whole numbers (`Integer`) etc. A full list of accepted data types can be found here.
• The inside of the function is where you define what you want it to do. The possibilities are pretty endless, although if you really want to get crazy with these you’ll need to learn some Visual Basic.
• For basic functions though, you’ll generally perform some operation on the input variables and return the result. To return the result, just make sure you assign the value to the function name (ex: `MULTIPLY = x * y`)

Following those steps, we can create the CPM function:

Public Function CPM(cost as Double, impressions as Double)

CPM = cost * 1000 / impressions

End Function

And voila, now we don’t have to type that formula again!

To make sure your function is still there when you close and re-open Excel tomorrow, you need to install it as an Add-In.

1. Open a new workbook (or if the one you used to create your function in is blank, that works too, and you can skip to step #4)
2. Open the Visual Basic Editor and create a module
3. Paste your function code in this module
4. Save the workbook as a .xlam file (the Excel Add-In format) in your Excel Add-In Folder. This folder’s location will vary depending on what computer and version of Excel you use. For my Excel 2011, I can store mine in `/Applications/Microsoft Office 2011/Office/Add-Ins`
5. Enable your add-in by going to Tools > Add-ins. You should see whatever you named your xlam file listed there - check it and press OK