Data analysis for SEO and PPC

Custom Functions in Excel VBA

Working in the SEO or PPC industry usually means handling large amounts of data. Previous blog posts have covered how this data can be managed and analysed using Excels built in functions – but what do you do when the function you need isn’t there? Using Excels built in VBA (Visual Basic for Applications) editor, we can write our own to do exactly what we want. Below I describe how to write a simple custom function, and some general advice on how to get started with VBA.

The VB Editor

Open a new document in Excel, and press ALT+F11. A new window will open – this is the VBE (Visual Basic Editor), where we can write our own functions to use within the usual spread-sheet view of Excel. Highlighted on the top left of the window, within the Project Explorer pane, will be icons for each sheet of your document, and one for the workbook itself. Right click on “ThisWorkbook”, go to “Insert” and “Module”. This will add a code module, a container for the code we will write. You are now ready to write your first function.

Analysing Keywords

One element of the data gathered in SEO and PPC will often be keywords/ phrases, and this can be a lot of data to work with. For a recent piece of analysis, I was asked to find a way of counting the number of words within a search term, so that single keywords could be dealt with differently to phrases – e.g. “dresses” may be treated very differently to “red party dresses”. With hundreds or thousands of keywords, it would be too time consuming to manually count the number of words in each phrase, and there is not a built-in function in Excel to do it for us. So we turn to VBA to write a new function to do this.

Adding Code

Function countWords(phrase as string) as Integer

The above is the first line of our function – copy it into the module you created, under the words “Option Explicit” which should be already entered (don’t worry if not, just copy it in at the top – I will come back to this later). This line tells us a few important things:

Function – the first word tells us what type of code is going to follow. A function is a piece of code that takes one or more values, does something with them, and returns another value. For example, Excel has a built-in function called “SUM”. This function takes some input values, adds them together, and returns another value, the sum of the inputs. Our function is going to take the keyword/ phrase as an input, count the number of words in it, then return that number.

countWords – this is the name of our function. When we wish to use it, we will enter into a spread sheet cell “countWords” (just as we would enter “SUM” to use that function).

(phrase as string) – this is the input we need to enter, and will be the search keyword/ phrase.

as Integer – this is the type of information returned by the function. We are only interested in the number of whole words in the phrase, so are looking to return an integer value.

Preparing The Function

The next thing to do is prepare the function by declaring our variables. Above, we declared the variable “countWords” as an integer, as it will only take integer values. This allows Excel to warn us if something unexpected occurs e.g. if I want a function to count the words in “red party dress” and it returns “party”, obviously something has gone wrong! With variable declaration, Excel will recognise that this is not an integer and return an error warning.

The variables we will use in this function are going to be called “i”, and “counter”. You may call your variables whatever you want, typically “i” is used as an abbreviation of index, while “counter” will be just that, a counter. Add the following code below the first line:

Counts number of occurrences of space character in a phrase
Dim i as integer
Dim counter as integer: counter = 1

“Dim” is short for dimension, which describes the data type of a variable. We have told Excel that the variable “counter” will always be an integer, and we have also given it an initial value of 1. So far, “i” has no value. The first line should appear green in the code window; this is because of the apostrophe preceding it. This line is a comment – it does not do anything within the code, but just exists as a label to tell us what the code is for. Commenting your code is very good practice as it can be hard to understand otherwise. Feel free to add your own comments throughout to help you understand – just prefix with an apostrophe.

Counting Words

Excel doesn’t know what a word is, so counting them needs to be broken down into steps. The key feature of a word in this context is that it has a space either before, after or both.

So we can tell Excel to count the spaces.

For i = 1 To Len(phrase)
If Mid(phrase, i, 1) = " " Then
counter = counter + 1
End If
Next i

This is the main part of the function – paste or type it into the code module. Line-by-line, the following is happening:

For i = 1 To Len(phrase)

We are now giving “i” a value – in fact, a range of values from 1 to Len(phrase) – this is a built in function within Excel that will return the number of characters (letters + spaces) in the phrase we pass in.

If Mid(phrase, i, 1) = " " Then

This line uses the Excel function “Mid” to look at each character in the phrase in turn. “Mid” takes three inputs – the phrase to be looked at, the character to start the comparison on, and the number of characters to compare. We want to compare every letter in turn, 1 at a time so we pass in “i” and “1”. Finally, the “If” statement says that if a character is a space, then proceed to the following line of code, otherwise pass over it to the “End If” statement.

counter = counter + 1

This line is only activated when a space is found. So, we increment our counter variable by 1 each time to count the number of spaces in the phrase.

End If
Next i

These two lines tell Excel where the If statement ends, and to go back to the top and start again for the next value of i. This is called a “For Loop”, as we are telling Excel to repeat this task for a given number of iterations.

There is one last piece of code we will use to handle a particular situation – when the phrase passed in is blank. Copy this below with what you already have:

If phrase = "" Then
countWords = 0
Else
countWords = counter
End If

Here we have another If statement. If the input phrase is blank, countWords takes the value 0, otherwise it takes the value of the “counter” variable. By setting “counter” to 1 initially, we ensure the code works for single words, however it would also return 1 for blank phrases. This prevents that error occurring.

End Function

We tell Excel that we have finished defining our function. The whole code is as below, check that yours looks the same:

Close the VBE by clicking the X in the corner, and go back into the spread sheet. Type a few words in some cells, then type “countWords” (in another, click one of your cells containing text and add the close bracket). This cell should now contain the number of words in the cell you passed in as an input – if not, ensure calculation is set to “automatic” (Formulas>Calculation Options>Automatic in Excel 2010).

This simple function could save a lot of time as the function can be dragged down over as many cells as necessary – with hundreds of keywords/ phrases, it can be used hundreds of times. Do remember its limitations though – we are counting spaces, not words, so any leading, trailing or double spaces in your phrase may cause an error – use Excels built in “TRIM” function on your search terms to prevent this.

VBA Tips

• “Option Explicit”. This phrase should have appeared at the top of your code module. What it means is that all variables MUST be declared – Excel won’t run any code containing undeclared variables. This is good practice. It makes your code easier for others to read, adds additional error checking and should make debugging easier.
• Debugging tools. Your code won’t always work first time. To see exactly where it is going wrong, put your cursor somewhere inside the function code, and press F8. This will cause the code to fire one line at a time.
• Locals Window. In the VBE, click “View” then “Locals Window”. This adds a pane to the main window which will indicate the current value of any variable in your project. Using F8 as above, you will be able to see at which point a variable picks up an incorrect value.
• The example we have done is contained within the workbook code module – this means it will only be available within this workbook. If you have a function you use a lot, save it as an .xla file, and select Tools>Add-Ins from the VBE menu. It will now be available in all Excel files.
• VBA isnt restricted to Excel – all Office apps (Word, Access, Outlook, PowerPoint, Visio) contain a VBE and VB “Object Library”. This describes the custom functions particular to each application. Other applications such as Adobe Acrobat can also be controlled via VBA – this means that for a routine monthly report, you can store data in Access, run some analysis and create a chart in Excel, grab some narrative from a Word report, write the lot into a PowerPoint presentation and produce some PDF summaries to be emailed to a distribution list, all automated through VBA.
• Use other peoples code! There are masses of VBA code online. For most problems you encounter, its likely someone else has already considered and possibly solved it. So Google is usually the best starting point. Altering existing code is also a good way to become proficient in VBA – for example, can you see how to alter the countWords function to return the number of a particular character in a phrase e.g. to determine if a phrase is an email address?