4. Nested Loops in VBA Excel (Visual Basic for Applications)

This fourth clip in this VBA video series shows how to leverage loops within loops (known as nested loops), in Visual Basic for Applications (VBA) in Microsoft Excel.

hello guys my name is Khan and actually in this video I will talk about nested loops so - in this video is the continuation of the first video which is also about loops but this video in this video I will talk about nest in do what does mean but nested loop that if you are using loops within loops that thing is usually known as nested loops and actually in this video I will give you a one hypothetical example and then based on that example I will show you guys that how you can use nested loops to make your life easy so actually this just gives you a kind of a hint that how you can use nested loop and then you can devise your own way to use nested loops according to your own problem so I will start with this triangle as you can see on your screen then this the base of the triangle is a perpendicular of the triangle is B and then we can calculate this hypotenuse of the triangle based on this formula so suppose if you have a different parameters for perpendicular supposed to 3 4 5 6 7 and in the same way you have some base parameters do 3 4 5 6 7 so I just created these parameters and perpendicular parameters and as you can see that you can create different permutations between base and perpendicular to have this hypotenuse so actually I want to fill this whole table which you can see with the wammy the possible values of this hypotenuse so I want to know that what will be the hypotenuse if the pay if the perpendicular is 2 and the base is 2 and then I will be saying what would be the hypotenuse if the perpendicular is 2 and the base is 3 and then so and then to fill this whole table with the possible values of parameters for different combination of base and perpendicular venues so for that I will be creating a nested loop so now what I will do I will take you guys towards visual basic so in order to go so you have to open your you basic you have to go to the Developer tab and then you have to select visual basic and within Visual Basic you can you you can see that this there is a symbol which you have to click this down arrow button and then you have to insert the module so now we have opened the module and now I will start making my own macros so if you're free if you're writing your macro the first thing is that you have to mean that macro so I have this same so I will be saying sir and then I be saying you can name it anything you like but I have been calling him nested and then I will enter so so the Excel have created telling me that you are making this macro which will start from here from this sub line and ending at the answer and my all the port will now appear within these two lines so after that what I will do I will firstly I define the define the scalars or you can call them locals which I will be using in these loops so actually and but in order t aside to now and generate a new scalar which I will be calling be as in DJ so so the B is actually the base it will be capturing the base so it will capturing the values in this cell one by one so base will be two in the first time three in the second time and four and so on and after that I will need I will need scalar to capture these columns because one by one I will come first to see column then I will D column and in the same way I will be you I will need some vector that allows me to move to other rows from rho4 rho5 and row sixth and seven one one so for that I will be having one scalar as C and I it will again as integer because I'm saying that I'm saying that these are integers because as you can see even the perpendicular and the base taking integer values and in the same way the column number then the row numbers are also taking integer values so that's why I am calling them integers so the type of these scalars will be integers in the CV I will be creating one scalar for and I'm calling it off so our will remind me that it is you it is you going to use to when I will be defining rose we call it again as a new teacher and up that I've invented now I will initiate my first loop in this macro so I will be using a for loop after that the first loop that I'll be using will be on this row so will be on this row vector so in order of at least Ross Geller so I was saying are equal to and now you have to be a bit careful because my rows are stopping the first row is starting from the fourth row as you can see so my R will be starting from 4 then 2 and then you have to see that very rows of ending my rows are ending in the ninth row so that's why I will be writing 9 here and then enter and now I will be creating an other loop for the columns because what I want to do that first go to C