At what we have learned so far so first we've learned how to write a sub procedure called greet users and then this basically just return hello to the user who is currently logged in and this returns the application username this is the example of function we went through similarly application version we've learned various ways to refer to workbook one is by the position or index one is by the name uh the workbook has already been saved.
And then the workbook that is not saved yet this workbook refers to the workbook where we are writing the code active workbook refers to the workbook that uh we are currently in uh our culture is in so this is the active workbook now and if you move it here this is the active workbook and coming down here similarly for the sheets uh we've learned how to refer it by position by the name how to activate it how to refer by the the id or by.
You know by the caption or by the label similarly for the range coming down here we've learned how to use this um the square brackets uh and then the object name goes here uh we'll learn how to read from that as well and here another way to refer to uh the range will be using the cells method where you can specify the row and column uh similarly written right here and then coming down here referring it by the address of the range.
Uh specifying the string and then you can definitely go further by putting more range here so we have gone through this as well we've learned how to use offset how far we want to offset away from the particular range let's say we want to offset uh from this particular range how far up we want to go or how far down hover right or left for example from that particular position and we have even used this in our project and here's um how we implemented like activating the workbook going to.
Uh particular range specific range and then second we learned how to loop through identify the last row with the data for example last row with the data and then we write a value there that is also what we have learned and learn how to read and write how to refer to a named range uh and then you know like return the execution status with the time and then we've learned you know like a couple of other things how to look through and all this let's start off today's lesson by.
Learning what are variables and how we can use them to make our program more dynamic and robust variables basically are the names that we give to computer memory location you can pass a value to be stored there and then you can read from that particular location later on by using these variable names once we do few examples you'll be able to relate to it much better let me go back to our editor and i'll just replicate this part of the.
Code okay i'm gonna insert a new module and let's call this version two and i'll get rid of this for now okay so uh just to demonstrate this um i'll say variables examples okay so in order to so the official word that we use is declare okay to set up the variable we have to declare it.
And the syntax to do that is you have to specify dim okay after that you specify the name of your variable okay it can be anything that makes sense to you so let's say that we want to assign um from our project from the form we want to assign a value from here which is client so i'm just gonna say dim v client s and then you specify the data type here so do you want to store the text data do you want to store dates.
Do you want to store numbers uh if numbers is this currency or you know like all these things so we'll do more examples so for the text i'm going to specify string okay so here i've declared a variable that is a gun gonna be used only within this code okay and then to set a value you can just say weak line is equal to and then you can specify for example um amanda something like that and now we have already assigned a value.
Posts Related:
- Excel Today - Using Excel for Data Analysis
- 100 Sky Overlay Free Download Urdu Hindi Sheri Sk
- How to Design a Flyer in PowerPoint Office
Called amanda to this memory location a variable and if you have to read from here - ID Card Make
Uh you can say message box this right that's how you can read let's put it down here and if i run this now says amanda for example if i just copy this code from this uh read from this range uh you know from this sheet from this range so if i copy this code and put here instead of amanda so whatever value that you put in here.Will be passed to this variable okay so it says error now uh if we have to implement in this particular code we can uh declare the you know like all the variables at the top here and so i'll since it's the same thing i'll just copy it let's say we want to declare four variables one is a string this two will be string this is gonna be date is gonna be currency amount now i'm.
Gonna show you a table where there is you know like all this data type after we do this small example let's call the second one maybe date to represent the transaction date and the data type is going to be date here and then for the third one it's going to be amount let's say the amount for transaction amount as so especially when you're using currency um if the amount.
The number that is there is to represent um money or currency uh there is a special data type for that so use currency for this and then this is going to be string okay so now um here we have declared variables this is how you'll declare variables and once uh we have to assign a value i'm gonna copy the entire code and put it here just for.
One second and this one is gonna be control jv client okay doesn't show that i'm just going to copy and paste here and then second one is going to be redade this one is the amount is going to be from the amount and then this is going to be client type okay so now we have to clear four variables here and then we have set values to these four variables by.
Reading the form which we've built here right so client p date amount and client type okay so we have done this part now we've set a value set value to variables okay now uh i'll just keep this part as it is but once we reach here instead of reading from the form now we can read from this variable okay.
Now one thing you could ask here is uh why do we need to use variable when we can directly assign a value here now this is one one way of utilizing variables there are so many other purpose which we have not started looking into it yet just try to understand how to declare variables how to set a value and how to read from it for now so now if i run this code it's still going to yield the same result.
Okay let me put in another name and submit so here it produces the same result okay so that's how we can use a variable now if i have to just demonstrate this by um going through line by line so once we pass here see we can see amanda has been passed here the t date and the amount and then.
You see there is type a assign and then once you come here where is the workbook okay here it is so looping okay so i need to rerun this part f8 so starting to loop here as you can see.
And now it's going to activate the and then add the information like that
Okay now um as soon as the you know like the process completed as soon as this particular uh subprocess sub procedure is complete the value that is set here is gone now so if i ctrl g and try to print this out here in an immediate window there the value is gone okay so this the scope of these variables are just within this.Particular procedure at the moment now uh there will come a time when you want to read um when you want these variables to be accessible from all the you know all the modules uh and all the class okay all the other objects so for that you can use um one is public and then constant uh this one is uh when you use this constant you.
Assign a variable which uh the value will not change okay so constant and then you put maybe like uh application name and string is equal to let's call this uh application as uh excel app now this will be accessible from all the modules okay like this for example let's see that we have another module here and this code is to do something.
Unless this box you can you know like call this yeah or we can say um i'm just gonna copy our initial code and bring it here every time uh you pass them uh normally what i do like i i create a constant variable like this and then i refer i you know i show it up in a message box every time there is some notification so here for example i can say exclamation vp exclamation that will be the type of.
Message box and then comma here's the title that you can put in the title i normally saw the app name like that so if i run this code now it's going to say hello my and then the app name is up there so if you do not specify this part if i comment this file normally it will be just microsoft excel so you can show your app name there this can be also baby information so here you can see like the message box type has changed right so that's how you can utilize as well.
Maybe like uh you can also use constant variables to uh store you know like where you save the settings at the later stage when you develop you know like a more complex um application you might want to read the user settings from certain places and all this so you can specify all the constant variables all the variables that are not gonna change okay maybe the password it could be um the email id or the url that you want the user to navigate if there are some issues and so on so we'll do more.
Examples as we go on and the other one is uh the one we're talking about public variables like here all these variables that we've declared is just for this particular um process okay uh if there's any other function that we are going to call uh we can pass these values but then you know like the scope is just within this particular process now if you want the variable to be accessible from all these um.
Other processes then you'll have to declare a public variable so we can say if it is not constant variable then we can say like um let's take one example let's say that um application username right done done maybe you just use the same example for now like if i copy this weak line here i'm just going to save the just to give it different weak line as string okay.
Now if you put a public variable like this this particular value will be accessible from any code so for example let's say that you are running a code this you are setting a value here okay let's say you set a value like this uh and i run this let me just run this first okay and if i try to print this now this value is still there in the memory okay so if other process runs it it will be able to read from here okay so.
Once we start utilizing this uh you know like doing more more practical example then we will come across a scenario where we can use this okay uh okay um later on you know like when we do the variable uh with array and all this then all these are gonna come in come together okay now before we go ahead uh let's take a look at the data type that we've used here this is a data type summary table uh you.
Know like everything that you're learning in this lesson for example be it you know like data type and all these things are you know like uh universal uh with any programming that you're going to be doing be.net or c or i mean yeah python or any other programming you do this things remain uh the same okay the concept and everything remains same so for example so far we've used.
String okay so here a string uh and then these are you know like approximately how many characters can go in their lane of string for example and then we've used date for date this this much of memory is going to be used for example and here this is the data you can assign to that particular variable if anything goes beyond this then it's going to give an error uh just to demonstrate that let.
Me write one small code here so okay we can just reuse this let's say that uh dim x number as uh byte so byte can handle only number up to 255 okay now if i say by uh my number is equal to two and i'll just say next number.
Is printing two okay now if i put this 255 it'll still give us but the moment i do 256 is gonna give overflow that basically means that you know like this is beyond you know like what um it can handle okay so if you work with variables at the later stage and you come across overflow error then you just come to know that okay maybe for this number or whatever you're doing you can use um maybe currency that can handle you know this much of how much of this.
Um well that's a huge number um so it can handle a lot of numbers so you can go for currency uh you can go for double double can handle lots of decimal points if you as you can see here and then um integer can handle only from you know like 32 000 minus 32 000 to positive 32 000 approx and loan can also handle a lot of number but these are.
If you can see here these are only for positive numbers so uh when you are dealing with currency especially with money to not use integer or long maybe uh i'll show you an example where you'll be using this um absolute numbers and then we'll talk about other these variables like long long and all this these are specifically for 64-bit platforms uh i'll we'll go deeper into that at the later stage if you do not specify a number uh i mean data type.
Uh the code will automatically assign a variant to it so meaning like uh if i use a like dim dummy variable as for example you just say dm and then you just put it like that or you don't even assign anything like that okay and you directly set a value you know like something like that in the in that sort of scenarios what uh the program is going to do is is going to assign a variant to that and.
The first number that you assign to it will be taken as uh you know like the data type that you want to use okay um and then we can also use user defined um variable type okay so just before we go in there i want to demonstrate um some more examples because this this part is actually very important as uh and will help you to construct dynamic range will start you can even you know like create object variables so.
Uh these are all gonna come very very handy so i'm gonna give you you know a couple of more examples to get you will verse and very familiar with variables now let me do one thing let me show you how to dynamically identify let me close this first let me show you how to identify the code uh i mean identify the last row with the data and then instead of looping it down we will write a value there okay.
Now the loop that we have learned here it's very useful okay there will you it will present you many scenarios where you have to use this but in this particular scenario just imagine you have like 40 50 000 rows of data you have to keep looping and identify and then you know like every time you have to add a record that may be not very efficient so uh we will directly identify what should be the last row with the data and then we'll write as a data just below that okay so normally the way to approach there are multiple ways to do.
It uh one of the way i do it is i go to the last end of the data here okay so for example if i need to get let me organize a bit if i want to get the uh total rose count i can go here and i can print out rows dot count this will be the rows come so if you're dynamically constructing you can use this instead of uh using.
A specific hard-coded row number like this for example if i say range a and i'm concatenating it and then i'll say rows.com dot select now if i put my cursor here and i run this code like this it goes down here okay so that way i know uh that it has this many rows and then from this particular location we can use and method so once we use n we can specify from this particular.
Uh from this particular range we can specify to uh actually it's telling us the direction where you want to go so do you want to go to the end up or you want to go and to the you know like end of this column for example or let's say your range is here do you want to go this way to the left so we can point to uh we can point this direction so in this scenario we want to go up okay so up and then dot.
Draw okay now if i print this or maybe first i can select just for demonstration if i run this it goes and select this okay now if i want to return the row number instead of select i can just say row this will return 14. now if i copy this down further now we will the code is going to return 18 okay so 18 is returned and if i get rid of all this it will return nine.
It should return nine okay so that's how you can identify what should be the uh the last cell with the data and then you can append the data right beneath it appendments add the data okay now i'm gonna replicate this block of code so that you can refer to it later i'm gonna add another module you can add many modules uh that shouldn't be a problem and now i'm gonna declare a new variable.
Okay this variable is to hold the last row with data so dim and let's say last row or we can say new row as long okay so this can be absolute number because rose does not have decimal points and then and here we can say new row is equal to and then we can just copy this code okay.
Now we want to identify the last row that is totally based on this particular column okay so watch out for that if you follow this approach if this cell is blank for example it's going to overwrite this data right so you have whenever you use this method just choose the column where the data is not going to be blank like like the id or the amount something which is not going to be blank and we also want to make sure that it's pointing.
To the right sheet always remember that so i'm gonna put data here okay from this data sheet identify the last row with data question mark just to read it so now this is returning as the last row with data so i'll put plus one this will be the next blank row okay now coming down here i'm gonna get rid of this loop we do not want to loop anymore we don't have to loop because we already know the row number where we can write to okay um.
So we don't even have to activate it now i'm just going to show you one more example when we use with method okay with and then you can specify the object and then you can end with end with now when you follow this you can from this instead of writing you know uh instead of writing this multiple times the appropriate example also comes in here for example we can say width like this and then we can say n width.
