Category Archives: Excel

Using Else-If Statements in Excel VBA

Else-If statements allow the developer to have their function or subroutine consider multiple alternatives. This can allow a piece of code to evaluate multiple criteria quickly. This will explain how to use Else-If statements in Excel VBA.

Ad

In this example, we are going to make a function that checks the score that student earned on a English proficiency exam and then assigns a string indicating what level the student is at. Below is a picture of the Excel worksheet before we create the function.

We want to fill the “Level” column automatically. Below is a partial visual of what the code will do.

We want Excel VBA to check the score and assigned a level based on the score. For example, if the student score 9 or higher than Excel VBA will assign a level of “Expert” to the student. If the students score 8 or higher but less than 9 than the student will be assigned the level of “Very good.” This continues until the score of 1 which is not seen in the graphic above.

The code below is the actual code that is placed in the VBA developer.

Function level(score As Double) As String
If score >= 9 Then
    level = "Expert"
ElseIf score >= 8 Then
   level = "Very good"
ElseIf score >= 7 Then
    level = "Good"
ElseIf score >= 6 Then
    level = "Competent"
ElseIf score >= 5 Then
    level = "Modest"
ElseIf score >= 4 Then
    level = "Limited"
ElseIf score >= 3 Then
    level = "Extremely limited"
ElseIf score >= 2 Then
    level = "Intermittent"
ElseIf score >= 1 Then
    level = "Nonuser"
End If
End Function

At the top of the code, we declare a function and give it the name “level”. This function takes one argument called “score” the input into the function is a numerical value but the output is a string. After this, we have are first ElseIf statement where VBA checks if the score is 9 or higher. If the score is then the level is set to expert. This process continues until the number 1. After this the If statement ends and the function ends.

Below is the output of the code found above

It appears everything is working.

Conclusion

This example shared one way to employ else-if statements in Excel VBA. With a basic example such as this it is possible to employ else-if statements in ways that are much more complex.

Errors & Subroutines in Excel VBA

Whenever you create a line of code you have to be aware of the potential of errors frustrating the end user of your product. This post will provide some guidance on how to deal with errors when developing subroutines for Excel VBA.

The Product

Our subroutine is a currency convertor. It specifically converts Thai Baht to US Dollars. Below is the code.

Here is what this code does.

  1. Line 1 declares sub along with its name
  2. Line 2 Declare variable we will need
  3. Line 3 will cause a box to appear that will ask the user to input the amount of Baht to convert
  4. Line 4 indicates the formula for conversion and the output

It is possible to run this code for the developer. However, this is not for a developer but for a user who may not be familiar with VBA coding. Therefore, we need to have a way to run this subroutine from the Excel spreadsheet.

To run this subroutine from the Worksheet we will create a button. This can be done by clicking

developers-> Insert->Button

Once the button is create Excel will ask you which macro to assign to it. Simply click on the macro that has the same name as the subroutine as shown below.

Implementation

If the user inputs numbers and presses okay the subroutine works fine as you can see below.

Here is the answer below

The Problem

However there are problems if we enter text into our input box or if we click cancel. In either case. We get the following.

Excel takes us to the VBA developer and shows an error message. This can be a serious problem if we want other people to use this subroutine. In order to fix this we will add a few lines to our code as shown below.

Here are the additions

  1. Line 3 as some code that says “On Error GoTo Here” this tells VBA that when there is any error to jump down to line 7 where the word “Here:” is
  2. Line 8 is executed whenever there is an error and this brings up the box “There was an error”
  3. Line 6 makes sure that the error box only occurs when there is an error. If you do not put this Exit Sub code in there the error box will appear every time you run the code whether there is an error or not.

Below is what happens now when there is an error.

Now when there is a problem this is what the end user will see. It’s not clear what the error is but this is less confusing then allowing them to see code and the developer application.

Conclusion

This post provided an example of how to deal with errors when working wit VBA. There is naturally more than one way to handle the problems presented here. However, this is one viable way.

Borrowing Functions in EXCEL VIDEO

Borrowing functions is a great shortcut when making your own functions in VBA. Why reinvent something if it is already available? If the function is there this reduces the likelihood of introducing bugs and errors to your code if you try to make the function yourself. Using functions inside your own functions. The video found below explains how to use Excel functions in your own VBA developed functions.

Making If-Then Statements in Excel VBA

If-then statements are used in programming to allow a computer to execute an action based on a criterion. When this is done well, a program can do almost anything the developer wants. This post will provide an example of how to use if-then statements in Excel VBA.

Our goal is to create a subroutine in Excel VBA that determines if a number is divisible by five. In order to do this we have to do the following

  1. Create a variable to store input from the user
  2. Ask the user for a number
  3. Test if this number is divisible by 5
  4. If the answer is yes do one thing
  5. If the answer is no do something else
  6. exit the subroutine.

Below is a visual of what is described above

The Code

Below is the code for completing this task. There is no other way but to share the whole thing and then go through screenshots of each step.

Below is an explanation

The subroutine is called “divideByFive.” After this, we create a variable called x as an integer.

After creating the x variable, Excel will ask the user for a number in the input box as shown below. For this example, we inputted the number 25.

Once you input a number and press Enter and Excel will use the mod function to determine if the number is divisible by five. If the mod is zero this means there is no remainder and this triggers true for the if-then statement. Since our number is divisible by five, you will see the following which is the first message box.

If you put any number that is not divisible by 5 you will see the following, which is the second message box.

For simplicity, we skipped many steps, for example, if you type in text or press the cancel button you will get an error message.

Conclusion

If then statements can be used in your programming to achieve specific actions in your code. The example provided here is one simple way of doing this in Excel VBA

Making Subroutines in Excel VIDEO

Subroutines are another tool that can be developed in VBA. They are similar to functions but generally serve a different purpose. In particular, subroutines allow you to program in a modular manner in which different pieces of code are developed separately. In the video found here is an introduction to Creating a subroutine in Excel.

Naming Cells & Cell Ranges in Excel

Naming cells and ranges is a way to complete task in Excel. This post will explain how to use this tool.

The main benefit of naming cells and ranges in Excel may be that it simplifies completing operations inside Excel. In the example below you are going to see two numbers

If we ever want to use the values 10 and 5 we have to continue typing 10 and 5. By naming these cells we can you the text terms length and width instead. In the screenshot below you can see in the upper left corner how I rename the value 10 with the term “length.” To the left of the formula bar and directly below the clipboard is a box called the “name box.” Right now it says “C2” but we will change that.

In the picture below we have changed the label “C2” to the word “length.”

We also renamed cell “C3” to “width”. In the screens below we will now call the names of the cells rather than their numerical values and still complete calculations.

You can see how the cells are highlighted when their names are called. Below is the output when pressing enter.

Look closely at the formula bar. The values 10 and 5 are not there. Rather the formula bar shows = length * width. In other words, naming a cell or cells is similar to declaring a variable in a programming language.

You don’t have to name each cell individually. By highlighting the column with the names and the columns with the numerical values you can do this all with a few clicks in Excel. In the picture below I have “base” and “height” in one column and in the column right next to it I have the values of 5 and 4. If I highlight the 2 rows and 2 columns I can then name a cell range from the current selection. This can be done two ways

  1. Click formulas->Create from selection
  2. Shift + ctrl + F3

When you do this you will see the following

Excel has already determine where the names for each named cell is. Once you click okay you will have two new named cells, “base” set to a value of 5 and “height set to a value of 4.

Editing the Names

If you ever want to edit or delete the names you have given a cell all you have to do is click on formula->Name manager or you can also press ctrl + F3 and you will see the following

In this box you can create new cell names, delete cell names, or edit.

Creating Named Ranges

A named range is more than one cell that has a shared name. To do this you highlight the multiple rows and columns you want and follow the same steps as naming an individual cell. Below is a picture of this. Notice how in the name box the rage is given the name “data.”

Naming a range will allow you to type its name somewhere else and instantly reproduce all of the data.

Conclusion

Naming cells and ranges is a convenient way to access information in your worksheets inside Excel. When it is necessary to deal with complex operations this can be another tool that can be employed to do things quickly.

Creating a Subroutine in Excel VBA

Subroutine are another tool that can be used in Excel VBA for achieving the goals of the creator. Subroutine come from the idea of modular programming in which code that is reused multiple times is created and store in its own separate space and called whenever it is needed. By creating a subroutine separately it does not have to b recreated every time the functions it does are needed.

Many wonder what the difference is between a subroutine and a function. For generally readers this is actually highly esoteric but the main difference is that subroutines do not return a value while functions do. In addition, functions can be used in formulas but subroutines cannot. Right now this difference between functions and subroutines is not important but when programming becomes more complex this difference can be significant.

In the subroutine in this post we are going to make subroutine that calculates the area of a triangle. IN order to achieve this we need the subroutine to do following.

  1. Ask for the length of the triangle
  2. Ask for the width of the triangle
  3. Calculate the area
  4. Return the answer to the user

The code for this is not as complicated as it seems below is the code.

  1. In the first line we declare a subroutine use the Sub followed by the name of the subroutine.
  2. Line 2-3 is where we declare the two variables we will need. Input boxes appear to get this information
  3. Line 4 we calculate the area
  4. Line 5 we print the results of area in a message box

To run this subroutine you can press F5 while in the VBA developer. Once this is done you will see the following

First, Excel asks for the length of your triangle. BElow you can see I typed the number 10.

Next, Excel will ask you for the width of your triangle. I put 4

Finally, we get an answer of 20 which is the area of a triangle in this situation.

Conclusion

With a few lines of code we are able to have Excel calculate the area of a triangle. This could have been done with a function or directly on the worksheet. However, if there is a reason you do not want the user to interact with the worksheet or you need to repeat this calculation over and over a subroutine may be more appropriate.

Making Dashboards in Excel VIDEO

Understanding how to make dashboards is an in demand skill. Many analyst jobs come with the expectation that the applicant can do this. Dashboards provide a way for a manager to play with different visualizations and or aggregations of some data without have to code or create anything themselves. In the video below, we will learn how to make a dashboard in Excel.

Borrow a Function in Excel

When creating a function of your own in Excel often it is more practical to borrow formulas rather than code all of this behavior yourself. In this post, we will learn how to create functions that borrow other functions already available in Excel. Specifically, we are going to create a function that calculates the range of a dataset through using the difference between the max and min functions of excel.

In the piture below we want to find the range of this data.

To get the answer we need to go into the VBA editor. This is available by clicking on the developer tab and clicking on Visual basic. When you do this you will see the following.

Once inside visual basic click on insert->module to add a new module. Inside this module is where we will place our code.

The Code

The code is rather simply and is shown below

At the top we type the name function to indicate to Excel what we are making. Next to this, we define the name of the function and inside the parentheses we indicate what the arguments are. after this we indicate the data type. In this situation the name of the function is “spread” and it takes the argument “spr” and the data type is “Double.”

In the next link we explain the behavior of the function. We use the command application.WorksheetFunction,Max() to call the max function whic will find the largest value in the spr data object. We repeat this process with the min function after the minus sign. Lastly, we end the function.

The Results

We can now test the function. In the first picture we call the function and in the second we show the results.

Now for the results

The dataset is small enough that you can check this manually. The number 12 is the largest while the number 1 is the smallest. The difference between 12 and 1 is 11.

Conclusion

With a few lines of code we can quickly borrow functions in Excel to create our own functions. Doing this can save a lot of time especially when you begin to create much more complex functions

Pivot Tables in Excel VIDEO

Pivot tables is one way of performing data aggregation in Excel. With a few clicks of the mouse many different insights can be extracted from some data. The video below explains how to make pivot tables in Excel. Knowledge of this is an expectation in many analyst jobs. Please like and comment on this video and let us know how we can continue to improve.

Data Validation in Excel VIDEO

Data-validation is a tool in Excel that is a great way to avoid making mistakes when inputting data. Anybody who has ever done any data collection knows the headache of have to fix data entry errors. These mistakes can have a sever impact on the analysis and conclusions that analyst makes. The video below demonstrates how to implement data validation in Excel.

Text Manipulation in Excel VIDEO

Text manipulation is becoming more and more important as unstructured data becomes more popular for analysis. Although Excel is not designed for serious text analysis there are some basics functions in Excel that can manipulate text for additional analysis. The video below address Basics of manipulating text in Excel

IF, AND, & OR Functions in Excel VIDEO

In the video below we have an exciting introduction to several functions that are related to Boolean logic. Boolean logic is essential statements in which the only answer can be TRUE or FALSE. A lot of computer programing is based on this dichotomous idea.

For our purposes, we are going to learn how to employ the IF, AND, & OR Functions in Excel.

Naming Cells and Cell Ranges in Excel VIDEO

Most objects have names. People, places, things and in general most nouns have a name that is used to refer to them. However, computer coding can be much more boring, especially inside Excel. For example, C1 means nothing to most of us and C25:D25 means nothing as well. Fortunately, Excel allows a way to avoid this through name cells. In this post we will look at Naming cells and cell ranges in Excel in the video below.

Making a Function in Excel VIDEO

Functions allow the user to automate redundant tasks that need to be completed. When you first begin making them sometimes it seems as those making the function takes more time than doing it manually. However, everything gets easier with practice. In the video below we will learn how to develop functions in Excel.

Creating Functions in Microsoft Excel

Many people will use Excel for years and never be familiar with some of the more advanced features. For example, there are ways to develop your own functions and even programs inside Excel using Visual Basic Application (VBA). This powerful tool is sitting there waiting to make life easier for Excel users. This post will explain how to create your own functions using Visual Basic Application in Microsoft Excel.

Load the Developers Tab

First, you need to add the developer’s tab to your ribbon. To do this, do the following

  1. Click on File->Options->Customize ribbon
  2. Then click “customize main ribbon” and click on the developers checkbox as shown below

Setup for the Function

Now that the developer’s tab is available. We need to click on it and then click on visual basic, as shown below.

Once you click on “Visual Basic,” you will see the following

To make a function, we need to add a module. This is done by clicking

  • Insert->Module

You will see the following

The Function

We are going to make a simple function that calculates the area of a triangle. The code is only three lines and is shown below.

Function area(base As Double, height As Double) As Double
area = (base * height) / 2
End Function

Line one is declaring the function and giving it the name “area.” Inside the parentheses, we have two parameters, “base” and “height.” Each of these parameters is a data type called double in VBA, which can be used to store decimals. The function itself is also a double data type as this is outside the parentheses.

Line two shows the actual formula for the area of a triangle.

Lastly, we finish the function by adding “End Function.” Generally, VBA does this for you automatically.

Test the Function

Now we can see if the function works by going to Excel. You don’t have to save the function or anything like that in this example. Go to excel and call the formula, place two numbers inside the parentheses, separated by a comma, and press enter. Below is an example.

Below is the output after press enter

Success

Conclusion

This was a simple example of how to make a function in Excel using VBA. There is so much more to explore and learn, but this is a great way to get started.