Introduction Course to Excel VBA | Jack Tellington | Skillshare

Introduction Course to Excel VBA

Jack Tellington

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
4 Lessons (16m)
    • 1. First Steps in VBA Excel

      3:09
    • 2. What is The Difference Between Sub and Function ?

      5:13
    • 3. Learn How to Use Variables

      3:26
    • 4. Use Loop While or For ?

      4:01

About This Class

You have decided to learn Excel VBA for Finance and you do not know where to start?

We have designed this free Discovery Course with the ambitious goals of:

 

  • Providing fundamental notions about VBA to quickly learn how to code in the next classes.
  • Bringing you confidence  and improve your ability to write small programs.
  • Positionning you so you can compete for Investment Banking jobs and bring your competencies in programming.
  • Assisting learners who have no prior programming experience or knowledge of computer science for an easier entry into computer programming.

 

In this course divided into four classes, we will see:

  • How to get around Excel and VBA.
  • What is a Function and a Sub.
  • What is a Variable.
  • What are Loop, Conditions and how to use them in VBA.

 

Again, this course is designed for new learners so the course will be adapted to your level. Once you master the taught skills, your will become more familiar with VBA and will be able to develop your knowledge with more in-depth classes.

Transcripts

1. First Steps in VBA Excel: Welcome to this tutorial by VB a finance dot com. This tutorial is aimed at beginners that want to learn visual basic applications, which is more commonly known as Vehbi eh? If you already have some knowledge about XlV B A, you can jump directly to the next tutorial. Excel is a powerful tool, since everyone has already used it at least once. Thus, this familiarity allows even the novices to know how to use the tools designed by excel. This is also a reason why Excel is widely used in the banking industry today. First, let's do a quick reminder and have a look at the main components of Excel. At the center. There is the grid. Here we can select cells grouped them, format them, such as setting the layers in the font color. We can also merged cells, right text change, the font size and so many other things. However, as you certainly know, the main use of Excel is to input numerical values and run calculations or analyses on it. Here on the top, we have the formula Bar, in which we can write calculations between different cells and display the result in the selected cell Let's take a quick example by adding another value here, and we're going to calculate the difference between these two cells and display the result in the selected cell, which is D five. Here we write equal. We select C four minus C five enter and the result displays here in this cell, lest at the bottom of the page we can see several tabs. Each tab corresponds to a worksheet we can add or delete a worksheet to add a new worksheet . We click on this small icon, insert worksheet and right click it. If you wish to delete it. We can also rename the worksheet. The whole set of worksheets is what we call a workbook, which means whenever you save an Excel file, you are actually saving the workbook. One of the main other functionalities of excel that most people do not know about is the opportunity of using V B A. In Excel V. B. A is an integrated programming language in Excel, which allows the user to do more complicated actions than just running basic formulas. Everything you do on a spreadsheet can be completely automated using Vehbi eh? And I'm going to show you that it's not as complicated as it might sound. No toe access. The V b A editor. It is easy. We can access it using the shortcut. Ault F 11 Press These two keys simultaneously. Here is what the VB editor looks like. In the next video, we will see some of the important functionalities that are relevant to understand the other courses. If you liked the video, feel free to follow us on our channel or go directly to VB a finance dot com, to find more interesting content about VB, Excel for finance. 2. What is The Difference Between Sub and Function ?: Welcome to this V b A tutorial by vb a finance dot com from Excel we access the V B A editor here on the left you can see the Project Explorer. A project contains the V B A objects associated to a workbook. It can be a VB, a code or user forms. The V B A is written in what we call a module which are just like a text file that contains code to create a module. We right click on VB a project insert and click on module. So the editor displays here and we're going to write the module content on the left, we can see the property panel of the module where we can change its name from the Excel grid away. To use VB, a code is to call a sub to define a sub. We write directly in the module editor. We write the keyword sub in the name of our sub and the parameters in parentheses parameters are values that we send to the sub. These values are used by the sub to be executed. We can write this sub to do any actions. For example, here we write a sub that will have to new miracle parameters. We will calculate the difference between these two numbers and display the result on the top left of our worksheet. So let's create this sub. Let's call it subtract and in parentheses are to input parameters. Value one as double and value to as double here as double means that the values we send to our sub our decimal values automatically. The software detects that we created a new sub and has added the in sub keyword. So the content of our sub will be between these two lines of code. Now let's go into details and write the sub content. We want to display the result of the top in the A one cell. To do so, we will write range a one dot value. So this line says to our program that we target the A. One cell here dot value indicates that we want to change its value. We could have modified another setting, such as the dot format condition or the fought type using dot font. Here we write dot value equals value one minus value to and press enter. Now our sub is ready to use. Let's try it directly on Excel. To test this sub, we create another sub that defines the input values for the subtract sub. We will call it sub test by the way, I change their sub name to avoid confusions with the default Excel functions. To call our procedure, we use the call keyword and we set for example, 150. Then we will launch this test sub using the arrow button run sub. Once we click, there is no error message and the results display in a one as we expected. Here is another way to use VBS through the functions. The main difference between a function and a sub is that a function will return a value, which means it's result will be displayed directly in a cell Just by calling the function from the formula bar, let's go back to the V b a editor. The function syntax is quite similar. Instead of writing sub, we write function sub s t f to differentiate it from our sub. We have the same input parameters. The only difference is that we will declare the output type value Here it will be double we press enter like on the sub here end function displays. Instead of defining the cell where the result will be displayed, we write sub S t f equals value one minus value to here. The value of the difference will be the output value of our function to use it. Let's go back to our worksheet. We select any cell, let's say a three in the formula bar. Let's right equals here. We can see that Excel detects the function that we created. So now we can use it like any other basic Excel function. Now we have to choose the parameter value for sub stf. It could be two cells. Let's pick these two. We press enter and we can see the result that displays here in the cell where we modified the formula bar. Now we know how to write a simple program in V. B. A. In the next tutorial, we will learn how to manipulate variables. If you liked that video, feel free to follow us on our channel or go directly to VB a finance dot com to find mawr Interesting content about VB Excel four Finance 3. Learn How to Use Variables: Welcome to this V B. A tutorial by vb a finance dot com. When we use a programming language, we often need to temporarily store data to do so. We use variables. What is a variable? It is simply a memory slot that can contain new miracle text or other type of values to manipulate a variable in a program we call it by its name. In our previous video, we were already using variables. Indeed, the input arguments of a function are automatically stored in a variable. In our function, we had two variables. Value one and value to. Let's now imagine, we want to create a function that will have to new miracle input arguments. Its goal will be to permit the values of the two variables. Basically, we want to get the value of value one in value to and the value of value to in value one. Let's look at the function we used before. The first thing we're going to do is to display the value of the two input parameters so we can be sure of what their values are for that we're going to use the debug dot print sub, which takes for argument. The text we display. So we display open the quote value one closed the quote. Here we had an ampersand to indicate we want to concoct, innate or merge. If you prefer that term in a single character, string the rest of the line. Here we write our variable value one and we can Canton eight, with the character string value to and the value of the value to variable. Now we're going to try by launching the text sub. The debug dot print will display the character string in the immediate window. If you do not see it, you can go to the view tab and click on immediate window. We can see it now at the bottom, so we go back to the sub test and launch it by clicking on the green Arrow. We can see that the variable values display as expected now two per mute, the variable values we're going to add 1/3 pivot variable. The first thing to do is to declare this variable so we go at the beginning of the function and right dim pivot. As double. We do not have to declare the variable at the beginning of the function, but for more clarity. We usually do that way in V B A. Then we can use this variable in our function. First, we assigned the value of value to to pivot. Then we assigned the value of value one to value, too. And finally we assigned back the pivot value to value one, which contains the value of value to Then we write this line at the bottom to check the changes, and we launch the test sub. Again. Let's look at the output. We can see that the variables have been per muted. In the next video, we will learn how to use conditions in loops in V. B. A. If you liked that video, feel free to follow us on our channel or go directly to VB a finance dot com to find MAWR. Interesting content about VB Excel for finance 4. Use Loop While or For ?: Welcome to this fourth beginner. VB A tutorial by vb a finance dot com. Let's talk today about the conditions. Conditions are a fundamental tool in programming. It allows you to run a test on variables and run a task depending on its value. This in tax is as follows. If here the condition, then end if and hear the code to execute. We can also write else Kate an action to execute if the condition is not respected. Another great tool to use is the loop. It allows us to repeat a piece of code a certain number of times pre defined or not. The two main loop types in V B A. R. The four and the do While loops. The four loop is used if we know in advance the number of loops to execute. For example, four counter equals 1 to 100 step one here, the code to execute and then next counter so this piece of code will run 100 times. The counter value will be incriminated with a value of step, which is one, and we will be able to use it inside the loop. Here. The step one is optional because the default increment ation is one the other type of loop that is also widely used is do while it will loop as long as a condition is true, the syntax is written as follows here, the code to execute and the key word loop at the end. To see a direct application of this tool, we will write a function that will return the factorial value of an integer number. Mr. Remind you what the factorial is. It is the multiplication of the input number by the previous number until it reaches one. For example, the factorial of five is written as follows five times four times three times, two times one To do so, we will create a new module that we call factor and a new function. This function will have an integer for input and also for the output value. We declare the rez variable with an integer type. It will contain a result. We also declare a counter variable that will be used for our loop. We initialize rez value at one. Then we will use ah four loop with a negative step. Be right. Four counter equals value one. Our integer input value 21 step negative one next counter here at each loop, the counter value will decrease by one until it reaches one. We will multiply this value by rez value by convention factorial zero value is one. We need to manage that case with a condition in order to avoid an error when executing the program. If value one is different from zero, then we can execute the code. Otherwise the value of reds stays at one. Then the function will return. The result best dysfunction. We will use our test sub using the debug dot print. As seen in the previous video. We will start by factorial five. Here. The name of function is the same as the module, which is a bit confusing. I will therefore modify the name of the function to fact without forgetting to modify this part. Okay, look here in the immediate window, we can see the factorial value. Now you've mastered all the concepts necessary to follow the next tutorials. If you like this video, feel free to follow us on our channel or go directly to VB a finance dot com to find more interesting content about VB. A excel for finance