Local arrays are a way of storing data in Excel. In this video, we will look at two different ways that you can place data inside an array in Excel VBA. The first method is used when the developer wants to hardcode it themselves. The second approach allows the user to enter the data.
Category Archives: Excel
Navigating Workbooks in Excel VBA VIDEO
Moving between workbooks with the mouse is how most people do this in Excel However, with VBA all of this can be made automated for various purposes. The video below shows you how to code this process using VBA,
Worksheet and For Loops in Excel VBA
This video will combine knowledge of for loops and worksheets in two different examples in VBA. The first example will show you how to insert data into several worksheets. The second example will show you how to create worksheets and rename them using Excel VBA.
Working With Worksheets in Excel VBA VIDEO
Most of us are use to using the mouse to accomplish various task on the computer or in Excel in particular. However, this is not always a practical way to approach matters when coding. In this video, we will look at using VBA to move between worksheets and accomplish various task. This is really beneficial for people looking to automate some tasks.
String Functions in VBA VIDEO
This video explains how to use string functions in Excel VBA. This is only an introduction as there is so much more than string functions can do. For now, like and comment on this video and let us know what you think about this.
Arrays and Functions in Excel VBA VIDEO
This video explains how to use arrays and functions together to achieve a goal in Excel VBA. It is useful to know how these tools can cooperate in order to develop more complex tools when employing VBA. Be sure to like and comment on this video.
String Functions and Excel VBA
This post will provide several examples of using string functions in the Excel VBA developer. There are a large number of string functions that are available in Excel but we are only going to look at the following…
The first example will use most of the functions listed above. Below is the code for the subroutine we are creating.
Sub modString() Dim text As String, text2 As String text = "educationalresearchtechniques" text2 = "EDUCATIONALRESEARCHTECHNIQUES" MsgBox InStr(text, "cat") MsgBox InStr(10, text, "i") MsgBox UCase(text) MsgBox LCase(text2) End Sub
This code is rather straightforward. We are manipulating the phrase “educationalresearchtechniques” or the same phrase in all caps.
The InStr function takes the text variable and searches it for the string “cat.” The output gives the position at which you can find the phrase “cat”. Below is the output from the message box.
The string “cat” begins in the fourth position of “educationalresearchtechniques” which you can see for yourself.
The second InStr function tells VBA to skip the first 10 spaces in the string and then look for the letter “i”. This was done because the letter i appears twice in the string so this allows you to find the second appearance of the letter “i”. Below is the output.
The second “i” appears in the 25th position of the string.
The last two functions are similar. The UCase function makes all letters capitals and the LCase function makes all letters lower case. Below we have both outputs shown.
The output to the left uses the variable “text” while the output to the right used the variable “text2.”
The coding for the array is more complex. Our goal is to remove the spaces when we join the text in an array. Below is the first part of the code
Option Base 1 Sub joinText() Dim word(4) As String, combineMsg As String, msg As String word(1) = "z" word(2) = "ys" word(3) = "x" word(4) = "w" combineMsg = Join(word)
In the code below we declare several variables that we will need and we add the content to the array we created called “word.” This array is a 1 x 4 array. Each time we type word(x) we are putting the string after the equal sign in that position. A the bottom of the code we use a Join function to combine all the strings in the array into one string. The problem is that this string has spaces between the letters. To fix this, we will use the for loop and if-then statement below.
For i = 1 To Len(combineMsg) If Not Mid(combineMsg, i, 1) = " " Then msg = msg + Mid(combineMsg, i, 1) End If Next i MsgBox (msg) End Sub
This for loop goes from i to the length of the combineMsg string. In the next line, we use the mid function to tell VBA to start looking at whatever ith position we are at and to take one character from the string. If the character is not a space ” ” then we will add it to the new variable called msg. If it is a space we ignore it. Finally, after this has looped several times we exit and print the new string in a message box. The output is below.
What you see above is the locals window from the VBA developer. You can see that the combineMsg variable has spaces between the input from the word array. However, this is fixed when you look at the msg variable in the next line. Below is the output from the Message Box.
String functions are another tool that has many applications in Excel VBA. We only touch on some basic ideas here but there is so much more that can be done.
Arrays and Subroutines in Excel VBA VIDEO
Arrays and subroutines can work together to do amazing things. In this post, we look at a simple example of cooperation between arrays and subroutines in Excel VBA. Be sure to like and comment on this post.
Using and Arrays and Functions in Excel VBA
In this post, we are going to look at using arrays and functions together. Our goal is to create a function that can divide the numbers in an array and count how many have a remainder.
Declare Function and Variables
First, we need to declare our function and variables. This is done in the code below.
Function remainderCount(rng as Range, n as integer) as Integer Dim i as Integer, j as Integer Dim nr As Integer, nc As Integer, c As Integer nr= rng.Rows.Count nc = rng.Columns.Count
The name of our function is remainderCount. It takes to arguments. Rng is for the array and n is for the divisor. In the next to lines, we declare variables for the following.
- i for row
- j for column
- nr for number of rows
- nc for number of columns
- c for the number of numbers that have a remainder
- The last to lines are for counting the number of rows and columns
Next, we need to create our nested for loop. This code has been used several times on this blog so we will not explain much here.
For i = 1 To nr For j = 1 to nc If rng.Cells(i,j) Mod n >= 1 Then c = c+1 End If Next J Next i remainderOne = c End Function
The for loop goes first across columns and then down to the next row. In each cell, it checks to see that if the number in that cell is divided by our n if there is a remainder or not. If so then the c variable increase by 1. After this, the output of the function is set to whatever value c is,
Below is the full code followed by what this looks like in Excel.
Function remainderCount(rng as Range, n as integer) as Integer Dim i as Integer, j as Integer Dim nr As Integer, nc As Integer, c As Integer nr= rng.Rows.Count nc = rng.Columns.Count For i = 1 To nr For j = 1 to nc If rng.Cells(i,j) Mod n = 1 Then c = c+1 End If Next J Next i remainderCount = c End Function
Below is the output
The answer is 4. This is because there are six numbers in the array. Two of the numbers are divisible by 4 (12 & 24) but the other four numbers are not (14, 18,19,27). The numbers that are not divisible by 4 make up the answer.
This is one of the more practical ways that you can employ knowledge of arrays with functions. Of course, there is an infinite number of ways to use these tools. This means that practice is the best way to learn how to apply these tools.
Import/Export Arrays in Excel VBA VIDEO
There are times when it is necessary to move data between Excel Worksheets and the VBA developer. The video below provides an example of how to complete this task. Please like and comment on this video so we can find ways to improve our services.
Arrays and Subroutines in Excel VBA
In this post, we are going to go over a simple example that will demonstrate how subroutine and arrays can work together to achieve a specific purpose. What we want to do is for our subroutine to be able to take a number from the user and determine if when we divide each number in the array y the user number if the answer has a remainder of 1 or greater.
Below is the array as it would be found in Excel
Notice how the cells are selected. This is necessary for the code that we will use.
Next, we need to set up our code. We will begin by declaring our subroutine and variables as shown below.
Option Base 1 Sub remainderOne() Dim i as Integer, j as Integer, n as Integer Dim nr As Integer, nc As Integer, c As Integer nr= Selection.Rows.Count nc = Selection.Columns.Count n = InputBox("Enter a number")
The variables above will be used to store data we need to achieve our goal. I and j are for the rows and columns, the n variable will store data from the user, nr and nc are for counting the rows and columns, lastly, c is a variable that will count how many times our criteria is met.
We now need to include the nested for loop with the output
For i = 1 To nr For j = 1 to nc If Selection.Cells(i,j) Mod n >= 1 Then c = c+1 End If Next J Next i MsgBox("There were " & c & " numbers with remainder of one when divivded by " & n & "." End Sub
The nested for loop goes through each row and column seeing if the number inputted by the user results in a remainder of 1 or greater. The details of for loops have been explained in a prior post.
The code was explained in parts but here is how it should look in the VBA developer
Option Base 1 Sub remainderOne() Dim i as Integer, j as Integer, n as Integer Dim nr As Integer, nc As Integer, c As Integer nr= Selection.Rows.Count nc = Selection.Columns.Count n = InputBox("Enter a number") For i = 1 To nr For j = 1 to nc If Selection.Cells(i,j) Mod n >= 1 Then c = c+1 End If Next J Next i MsgBox("There were " & c & " numbers with remainder of one when divivded by " & n & "." End Sub
We will now run the code.
The textbox asks for a number and we put the number. Below are the results
The message box tells you there are two numbers that have a remainder of 1 or more. Doing some basic math you can see that those two numbers are 41 and 14.
With a little bit of work, it is possible to use arrays with subroutines to do powerful things inside VBA. This example is one of many of how this can be done.
Importing and Exporting Arrays in VBA
There are times when you need to move data between your worksheet and VBA and vice versa. This post will explain how to move data in either direction.
In this context, importing will mean moving data from the worksheet to the VBA developer. We are going to move the data below into the developer
Below is the code to do this
Option Base 1 Sub bringArray() Dim A() As Variant A = Selection End Sub
The code does the following
- Line one is an option to set the base to 1. This makes sure that VBA does not add an additional column to the array.
- Line 2 is where we begin the subroutine
- Line 3 dim or create our array as a variant. A variant is a highly flexible data type in VBA.
- Line 4 tells VBA to take the data from the highlighted cells in the worksheet and save them in A.
Below is the output
THe imaage above is sharing with you the data that is now inside the developer. The numbers in the value column are the same as the numbers found in the worksheet.
The code below is slightly different in that it can be used when you know exactly what rnage of data you want to take from the worksheet.
Option Base 1 Sub bringArray() Dim A() As Variant A = Range("A1:B2") MsgBox A(1,1) End Sub
The only difference is in line 4 where the range function is used rather than the selection one. Since the output is the same as the previous example it will not be displayed here.
For export we are taking data from the VBA developer and placing it in an Excel worksheet. This code will involve the use of nest for loops. Below is the code.
Sub sendArray() Dim i as Integer, j As Integer Dim A(2,2) As Integer For i = 1 To 2 For j = 1 To 2 A(i,j)=3*i-j Range("A1:B2").Cells(i,j) = A(i,j) Next J Next i End Sub
Here is what the code does
- Line 1 declares the subroutine
- Line 2-3 declare the variable and array we need. The array is set to 2 x 2 because we know how big it will be .
- Line 4-9 are the nested for loop. The outer for loop is for the rows and the inner is for the columns. For the array we are taking the value of the row and multiplying it by 3 before subtracting whatever the value of j is. For example
- i =1 and j = 1 then
- (3*1)-1 = 2 [This is the output for row 1 column 1
- The range function is used to know where to place the results
Below is the output for this code
You can see that the results to the left are duplicating what is found in the right indicating that the code is a success.
The variation below allows you to create the array in the developer place it in the worksheet and also copy it to a cell range you select. Below is the code.
Sub sendArray() Dim i as Integer, j As Integer Dim A(2,2) As Integer For i = 1 To 2 For j = 1 To 2 A(i,j)=3*i-j Range("A1:B2").Cells(i,j) = A(i,j) Next J Next i Selection=A End Sub
What is new is in the second to last line. Here we set selection to A which will be the new location for our output. Below are the results.
You can see the second array that is copied below
Importing and exporting arrays is sometimes necessary when developing VBA code. The examples providing here give insights into how to do this.
Working with Arrays in Excel VBA VIDEO
Arrays are the selection of multiple columns and rows in Excel. In this video, we will learn how to manipulate an array with simple multiplication. Learning about this in isolation is not highly applicable. However, what is learned here is important for more practical actions when using VBA.
Using For Next Loops in VBA VIDEO
For Next loops are another tool for use in Excel VBA. It allows the developer to determine how many times they want their loop to run. This allows for all kinds of various actions in your code. The video below explains how to use For Next loops in functions and subroutines.
Creating Local Arrays in Excel VBA
Local arrays are arrays that are stored in Excel but are not necessarily seen in the worksheet. We will now look at two ways in which we can create local arrays in Excel using VBA. These two ways are
- Hardcoded by the developer
- Inputted by the user
Below is an image of what we want to create.
The numbers in green are for labeling the rows and columns. What is really going into VBA is the numbers in black. For example row 1 column 1 contains the number 10, row 1 column 2 contains 12, etc. To do this we need to create a subroutine that makes an array. Below is the code
Option Base 1 Sub newArray() Dim myArray(2,2) As Integer myArray(1,1) = 1 myArray(1,2) = 2 myArray(2,1) = 3 myArray(2,2) = 4 MsgBox myArray(1,1)
Here is what we did
- Line 1 we set an option called Options Base 1. If we do not o this, VBA adds an extra column to the array called 0. THis is not wrong but can cause problem later if you are not aware this can happen.
- Line 2-3 we intitate the sub routine and Dim the array with a 2×2 strcuture
- Line 4-6 are used to set each cell in the array to a specific value.
- Line 7 outputs the value in Row 1 column 1 as proof the subroutie worked
Below is the output
The one in the message box is from row 1 column 1 of the array. The output below is from the locals window in the developer.
This image provides evidence that the array works. You can see that each position of the array has a stored value.
User Inputted Array
It is also possible for users to input values into an array. Doing this requires input boxes and the use of nested for loops, both of these were discussed in a prior post. Below is the code.
Option Base 1 Sub makeArray() Dim myArray(2,2) As Integer Dim i As Integer, j As Integer For i = 1 To 2 For j = 1 To 2 myArray(i,j)=InputBox("Enter number for element " & i & "," & j) Next j next i End Sub
The first 4 lines are the same as the last code. Line 5 is where the for loop begins. The purpose of the for loop is to go across each column then skip down to the next row. The input box asks the user for a number as shown below
If you put the same numbers into the input box as in the original array you will see the following in the locals window.
Local arrays can be useful when combined with other ideas in Excel VBA. This will be covered in a future post
Intro to Arrays in VBA
Arrays are simply the use of multiple rows and columns in Excel. Generally, this happens through selecting them. The benefit of arrays is that you can perform calculations on several rows and columns of data at once. Below is a picture of an array.
Our purpose is going to be to take an array and multiple all the values by 3. In order to do this, we will have to use nested for loops and learn some terminology.
The for loops are difficult to master if you are new to coding. However, what we need is for VBA to loop through all the rows and loop through all the columns completing the calculation we want. This is commonly done by labeling the rows as the letter “i” in coding and columns as the letter “j”. Below is a picture of what I have just said.
For the actually coding here are the variables we need to declare in our subroutine
- i for rows
- j for columns
- nr for number of rows
- nc for number of columns
We need to count the rows and columns so the for loop knows when to stop.
Below are the functions we will use
- A function that counts the rows
- A function that counts the columns
- A function that selects the cells
- Multiply by 3 the selected cells
Below is the actual code
Sub timesThree() Dim i As Integer, j As Integer, nr As Integer, nc As Integer nr = Selection.Rows.Count nc = Selection.Columns.Count For i = 1 To nr For j = 1 To nc Selection.Cells(i, j) = Selection.Cells(i, j) * 3 Next j Next i End Sub
Here is what the code does
- Line one gives the subroutine a name
- Line 2 declares the variables
- Line 3 and 4 tell VBA to count the number of rows and columns
- Line 5-10 is the for loop. i and j are set to 1 initially and continue until whatever value nr and nc have. Whatever position the selected cell is at is multplied by three.
Below is a picture of the before and after effects of using this code on an array.
In the picture above you can see the array is 3×3 and selected. The code is to the right. In the bottom right you have the locals window which will provide information on the variables that are created. Below is the output
Here you can see that all the values in the array have been multiplied by three as planned. The highlighted yellow in the code indicates that we are at the end of the code and are ending the subroutine. The information in the locals window is hard to read but simply shares information on the values in the various variables that were created.
With this information, it is possible to create arrays and manipulate them in Excel VBA. As already mentioned, this information must be used in combination with other techniques to truly be useful for the average developer.
For Next Loops in Excel VBA
For Next loops are a type of count controlled loop in VBA. By count controlled it is meant that this loop is performed a certain number of times as determine by the criteria set by the developer. In this post, we are going to learn how to use For Next loops with functions and with Subroutines.
For Next Explained
Below is a high level overview of the behavior of For Next loops in a visual.
The For Next loop begins at i. This is normally one but it is flexible. In addition, the value normally increases by 1 also. Next, we have the loop it repeats until i reaches whatever the limit is. Often the limit is set by the number of rows or some other means and is generally called n in coding. The limit continues based on an if then statement. If i is less than the limit then it will increase by one and repeat the process. If i is greater than the limit then it will exit.
One thing that is missing from this diagram that is also important is some sort of way to count how many times the For Next loop finds what the developer is looking for. This value is often set to c and increase by one each time through the code. This will make more sense in an actual example.
Code for Function
Below is the code for a function that uses a For Next loop. This function will check if out number is divisible by 4 or 7.
Function numberCheck(n As Integer) As Integer Dim i As Integer, c As Integer For i = 1 To n If i Mod 4 = 0 Or i Mod 7 = 0 Then c = c + 1 End If Next i numberCheck = c End Function
In the code above we are doing the following.
- Creating a function called “numberCheck) that sets n as our variable for the limit
- We dim the c and c variables
- Next, is our loop, i is set to 1 and will continue until n which is picked by the developer
- After this, is our If Then statement. If our number from n is divisible by 4 or 7 then the c value will go up 1.
- Then we repeat this process until we reach the end of n
- Exit function.
The image below shows this function in action.
The function is counting how many numbers between 1 and 16 are divisible by 4 or 7. The answer is 6.
We will now complete a similiar task with a subroutine. This time we want to count a selection of rows and see how many number 3s it has. Below is the code.
Sub CountThrees() Dim nr As Integer, i As Integer, c As Integer nr = Selection.Rows.Count For i = 1 To nr If Selection.Cells(i, 1) = 3 Then c = c + 1 Next i MsgBox ("There are " & c & " threes in your selection.") End Sub
This code does the following
- We give the subroutine the name “CountThrees”
- We dim nr for the number of rows, and we also dim i and c as in the previous example
- The for next loop is next and goes from 1 to the end of all the rows
- Inside the for next loop the if then statement looks at the cells that are selected in the Excel spreadsheet and searches for those that are the number for. Then these 3 are counted and saved in the c function
- Then the process repeats it’s self
- At the end, a message bx appears telling the user how many threes are found
Below is an image of this codes actions
Here is the data the subroutine will analyze. You can count for yourself that there are three 3 in this column
Below is the result
The image below shows what is happening in VBA
The image above comes from the locals window in VBA. You can see at the end of the four loop that we ten rows of data (nr) i was set to 11, which is greater than nr which cause the loop to exit. Lastly, c was set to three which indicates that there were 3 threes in the selection
For Next loops are another useful tool for VBA developers. They allow you to let the context of the code determine how many iterations the loop completes.
Do Until Loops in Excel VBA VIDEO
Do until loops is one of several loops available in VBA. This loop in particular will execute a command until a certain criteria is met. The video below provides one example of how to implement a Do until loop in VBA code.
ElseIf Statements in Excel VBA VIDEO
ElesIf statements in VBA allow the developer to include multiple alternative actions within a subroutine or function. This allows for the code do execute goals that are more complex than just If-then statements. The video below addresses how to develop a function that includes Else-if statements.
Do Until Loops in Excel VBA
Do until Loops in VBA are for repeating a task until some criteria is met. How this is used can vary widely depending on what the developers goals are for implementing the loop in the first place. In this post, we will look at an example of the use of a general do loop. By general do loop we mean a loop that has no predetermine number of times it will run.
Our goal is going to be to create a subroutine that ask the user for a number. This number will then be squared until it is greater than 2000. Once the number is larger than 2000, VBA will exit the loop and share the results with the user.
Below is a picture of how the Do loop is suppose to work.
As you can see the loop takes a number, squares it and then checks if the number is larger than 2000. If the number is larger than 2000 VBA exits the loop. If not, the new x value is the square of the old one and the process repeats.
Below is the code for completing this task in VBA
Sub squareLoop() Dim x As Double, y As Double x = InputBox("Enter your number") Do y=x*x If y > 2000 Then Exit Do x=y Loop MsgBox ("Your number is " & y) End Sub
Here is what each line of code does.
- Line 1 declares the subroutine called “squareLoop”
- Line 2 we dim the two variables we need
- Line 3 is the code for creating the input box for the user and saves this information as our x variable
- Line 4 is the beginning of or do loop
- Line 5 is were we square our x variable and name it the y variable
- Next we have our if-then statement. If y is greater than 2000 than we exit the do loop
- In line 7 if our y value is not greater than 2000 than we save it as the new x value and return to the beginning of the loop in line 4-5
- Once we have a value greater than 2000 we skip to the bottom of the code and output a message box
The output is shown below
First Excel ask you for a number. For our example, we put the number 10.
Next, VBA squares the input number
10 * 10 is 100 which is not greater than 2000 so VBA goes through the loop again
The second time through are value is greater than 2000 so we get the message box with our value.
This is a relative simple process that has greater implications.
Do loops are another powerful tool you can implement in Excel VBA. The ability to automate repetitive task is valuable for saving time to deal with other matters.
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.
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.
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.
Our subroutine is a currency convertor. It specifically converts Thai Baht to US Dollars. Below is the code.
Here is what this code does.
- Line 1 declares sub along with its name
- Line 2 Declare variable we will need
- Line 3 will cause a box to appear that will ask the user to input the amount of Baht to convert
- 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
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.
If the user inputs numbers and presses okay the subroutine works fine as you can see below.
Here is the answer below
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
- 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
- Line 8 is executed whenever there is an error and this brings up the box “There was an error”
- 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.
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.
Subroutine Errors in Excel VBA VIDEO
Sometimes an application does something that is unplanned. When this happens an error has occurred. When developing an application the developer has to have ways deal with these issues. In the video found below shows ways to Solve subroutine errors in EXCEL VBA.
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
- Create a variable to store input from the user
- Ask the user for a number
- Test if this number is divisible by 5
- If the answer is yes do one thing
- If the answer is no do something else
- exit the subroutine.
Below is a visual of what is described above
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.
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
- Click formulas->Create from selection
- 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.
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.
- Ask for the length of the triangle
- Ask for the width of the triangle
- Calculate the area
- Return the answer to the user
The code for this is not as complicated as it seems below is the code.
- In the first line we declare a subroutine use the Sub followed by the name of the subroutine.
- Line 2-3 is where we declare the two variables we will need. Input boxes appear to get this information
- Line 4 we calculate the area
- 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.
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 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.
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.
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
Making Tables in Excel VIDEO
This video provides a brief explanation into how to make tables in Excel. This is mainly for making a set of data visually appealing. However, there are some aggregation tools available as well. Please like and comment on this video and let us know how we can improve.
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.
Autofill & Fill Series in Excel VIDEO
Auto completion of text is great feature that is available in Excel. With a few simple clicks Excel will know what you are typing and how to complete it. In the video that is provided below we will learn how to Use the autofill and fill series tools in Excel.
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
- Click on File->Options->Customize ribbon
- 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
You will see the following
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
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.