Category: data analytics

Using custom functions in Excel VBA:

When you work in the SEO and PPC industry it is a giveaway that you will be handing a large amount of data. While there are several ways you can utilize this data and manage it with Excel functions, and several tutorials are available online to talk about them. But what if you do not have the functions on Excel to do what you have to do with the data. You can use the Visual Basic for Applications (VBA) feature in MS Excel and write your own functions to help Excel carry out the functions that you want it to.

So, here in this advanced Excel training blog post, we will discuss about how to write a simple custom Excel function and will also give you readers some general advice on how to get started with Excel VBA.

Getting started with the Excel VBA editor:

First in order to work with the VBA editor in Excel, you must open a new Excel workbook or document and then press the following keys on your keyboard – ALT + F11. This will open a new window on the screen which is the VBE (Visual Basic Editor). This is where you can write your own Excel functions to use with the spreadsheet you have opened in your Excel document. This will be highlighted on the top left corner of the window. The project explorer pane will have the icons for each sheet of the document and another one for the whole of the workbook itself.   Then for the next step, right-click on the ‘ThisWorkbook’, and then go to ‘Insert’ and then ‘Module’ options. That will further add a code module along with a container for the code which we will learn to write here.

Now you are ready to write your first Excel function:

Data analysis will help you analyze the keywords:

Each element of the data gathered through the SEO and PPC will often have keywords and phrases and this can give birth to a large amount of data for people to work with. For a recent piece of analysis, our faculty member was asked to find a method for counting the number of words in a search term. In this way single keywords can be dealt with differently in comparison to phrases. Like for e.g. ‘dresses’ can be treated with a stark difference to the term ‘red party dresses’. But there are often 100s or even 1000s of keywords to work with and it will be too time consuming to manually count the number of words in each phrase. Also there are no in-built functions in MS Excel to do so for us. Hence, we must use VBA to write new functions for us.

Adding the code:

  Function countWords(phrase as string) as Integer  

This will be the first line of the function you are about to write, start by copying it into the module we just created. Copy it under the words ‘Option Explicit’ which should be anyway entered (if it is not then do not worry, just copy it at the top and we can come to this later). This sentence however, has a lot of important things to tell us about.

Function: this first word itself tells us about which code is going to follow. A function is simply a piece of code that takes one or more values, performs something with them and then returns a different value. For instance, there is a built-in function with Excel called SUM. This function may take some input values and add them together to return a different value which is the sum total of the inputs. Similarly the function we create will take the keywords or phrases as an input and then count the number of words in them, then return a value for that number.

CountWords: we have put this as the name of our function. The moment we wish to use it, we can simply input into the spreadsheet cell the words as ‘countWords’. Just like we would add ‘SUM’ to use the sum function.

Phrases as string: this is the input will be the one to be entered when we will need to search a keyword or phrase.

As integer: this is the type of information which will be returned by the function. We are only interested in the number of the whole words in the phrases and hence are aiming to return an integer value.

How to prepare the function:

The next thing to do is to prepare the function by declaring the variables. Here we will declare the variables in ‘countWords’ as integers because it is built to only take integers. This will allow Excel to warn us if anything unexpected happens. For example, if we want to use a function to count the words in ‘red party dresses’ and it only returns with party. This will mean that something has gone wrong for sure. So, with declaration of the variable we will be able to let Excel know that it is not an integer and hence it will return with an error warning.

The variables we will use in this function are going to be called as ‘I’ and ‘counter’, however, there is no hard and fast rule to name your variables this way, you can name it the way you like. But ‘I’ is usually used as an abbreviation for index and counter will just be used as counter. The next step will be to add this line into your code.

‘Counts number of occurrences of space character in a phraseDim i as integerDim counter as integer: counter = 1

Note that ‘dim’ here is short for dimension. This describes the data type of a variable. We have told Excel through our codes that the variable ‘counter’ will always be an integer. We have also given the initial value as 1. But currently ‘I’ has no value assigned to it. The first line should appear green in the code window, this is mostly because of the apostrophe that precedes it. This line in our code is merely a comment and does not do anything within the code. It only exists as a label to let us know what the use of the code is for. It is a good practice to comment your code as otherwise it often becomes very hard to understand it otherwise. Also feel free to add in your own comments throughout to help understand and all you have to do for it is to add an apostrophe before it.

How can you count the words?

You must understand that Excel has no preconceived notions about what a word is. So to count them the concept has to be broken down for it to understand in a few short steps. One of the key features of a word is that it has a space either after or before or even at times in between it, and often both.

So we can start by simply telling Excel to count these spaces:

For i = 1 To Len(phrase)

If Mid(phrase, i, 1) = ” ” Thencounter =

counter + 1

End If

Next i

This is one of the key areas of this function, you must paste it or type it out in the code module. You can do so line-by-line as well. But here is an explanation of what is happening with each step:

For i = 1 To Len(phrase)

Here we have given ‘i’ a value, in fact not just one value but a range of values from 1 to Len (phrase). This is a built-in function with Excel that may return the number of characters (letters + spaces) in the phrase we pass it in.
 
f Mid(phrase, i, 1) = ” ” Then

With this line of the code we are using the ‘Mid’ function in excel. This will ask excel to look into each character in the phrases in turn. This function takes 3 inputs which is – the phrases to be looked at, the character to begin comparison on, and the number of characters to compare with. We aim to compare every letter with one at a time approach. So, we would pass on ‘I’ and 1. And then finally the ‘If’ statement which says that if a character uses spaces, then excel should proceed to the next line of code. Or pass it over to the ‘End If’ statement.

counter = counter + 1

This line is only activated when a space is discovered. So, we increase our counter variables by 1 every time to count the number of spaces in the phrase.

End If
Next i

With the above two lines, we are able to let Excel know where the If statement ends and to go back to the top and the start again for the next value of ‘i’. This is called as a ‘For Loop’ as we letting Excel know that it must repeat this task for a certain number of iterations.

There is also one last piece of code which we will make use of in order to handle a particular situation. When the phrase is passed in is blank. Then copy the following with what you already have:

If phrase = “” Then

countWords = 0

Else

countWords = counter

End If
 

Here is another statement that we have. If the phrase we input is blank, countWords takes the value 0, or else it will take the value of the ‘counter’ variable. After setting the ‘counter’ to 1 initially, we ensure the code will work for single words. However, it may also return 1 for blank phrases, and this prevents errors from occurring.

End Function

Finally with that we tell excel that we have finished defining our function. Here is the full code as mentioned below, check if yours looks the same or not:

Image Source: us.searchlaboratory.com

After you are done, you can close the VBE by clicking on the ‘X’ in the corner and then going back to the spreadsheet. Once done type in some words in a few cells and then type ‘countWords’ in another cell. And then click one of your cells containing the texts and then close the parenthesis. This cell can now contain the number of words in the cell that we have input. If it doesn’t, then we can set it to ‘automatic’ (Formulas > Calculations Options > Automatic in Excel 2010.

This simple function works best to save time as it can be dragged down over as many cells as you’d like, with hundreds of keywords and phrases. However, you must keep its limitations in mind. We are counting the spaces and not just words.

Interested in a career in Data Analyst?

To learn more about Data Analyst with Advanced excel course – Enrol Now.
To learn more about Data Analyst with R Course – Enrol Now.
To learn more about Big Data Course – Enrol Now.

To learn more about Machine Learning Using Python and Spark – Enrol Now.
To learn more about Data Analyst with SAS Course – Enrol Now.
To learn more about Data Analyst with Apache Spark Course – Enrol Now.
To learn more about Data Analyst with Market Risk Analytics and Modelling Course – Enrol Now.