VBA Programming e-course with Real-Life Projects - E01

hi everyone my name is lung pamai thanks for signing up and for showing your interest to take this online lesson as mentioned in the email the goal of this course is to bring absolute beginners up to speed with vva programming by telling you things only on need to know basis using layman's term and by exploring practical examples this is one of my personal initiatives so this comes absolutely at no cost i will try to wrap this up in about four to five weeks each of these videos will come with simple assignments to check your understanding and you will have to submit your assignments to get access to the next contents once you complete all these assignments or projects you will be issued a certificate on behalf of vba a to z channel that can be verified from our webpage if you have not signed up yet you can find the link in the video description so what is programming programming simply means writing instructions for a computer to perform various tasks these instructions are written using programming language that a computer can understand vba is a programming language which stands for visual basic for applications here application refers to office applications like excel access database word document outlook and so on so vba is specially designed for office applications which means you can automate almost anything you do within these applications and much more using other libraries and once you're familiar with bva you can easily transition to.net g-sharp and for that matter you can learn any other programming language vba comes in built with office applications so once you have microsoft office installed development environment comes with it in my opinion the best thing about vba is it is easy to learn because syntaxes are very easy to read like sentences and it's a great skill to have especially if you work a lot using office applications or if your automation targets application like excel access database outlook word and so on you'll come to know that there are powerful libraries and functions and references that you can use within vba which you can use to connect to web pages servers databases build custom forms user interface functions and and much more in this lesson you'll learn how to set up the development environment get familiar with code editor learn some important shortcuts and tools that you can use while writing your code uh we'll also get a quick refresher on excel objects and then learn how to use them in your program so by the end of this lesson you'll learn how to create this simple form or user interface learn how to automatically show watermark when the fill is blank we'll write a program to read these inputs and submit them to excel table and return the execution status here okay and then i'll be including many more tips now as we go on now let's go ahead and set up the development environment i'm gonna close my excel and start a brand new excel from your program i'm gonna write here in search i'm gonna write excel and open up a new excel okay so once you're here probably you'll see a developer tab here that is what we are trying to enable now go to file and to your options and then from customize ribbon section you just check this box okay developer now depending on the office version you have if you want you can rename it as well for example let's say you want it to be there instead of developer you can do that okay once you check this box you click on ok ok so the developer tab has been successfully added before we proceed let's take care of the micro security if you don't see this button here because of your office version go to file and options and go to your trust center trust center settings and click on this micro settings now you can control um if you want to run a program in your application be it excel access database and so on so if you want to disable this is the option you go for if you want to enable it without any prompt then you can go for this this could be dangerous because if you download something from online which is a vba program or a micro uh it could install you know like i just know software which which could be um dangerous okay uh if you want to get a problem every time this is the option you can go for and if you want to you know like disable all macros except the ones that you trust digitally signed uh you can go for this but for now you can either go for this or this and i normally use this or the other option is uh you can add um a trusted location and you can run um you know like vba program from that location only okay so that is also another way if you're very particular about the security so in that scenario you can add a location for example i'm gonna go to my desktop and add a new folder here called projects maybe vba projects and i'm gonna select the folder click ok i'm gonna include all the subfolders as well in the trusted location and click on ok and then okay uh for example now i've already disabled um all the macros okay so for example i'm going to close this excel this is the one i was demonstrating earlier if i click on this button it'll say that it cannot run because macro is disabled now if i copy this file inside this folder this is the one with the trusted location and if i let's say stephen taylor or something and click submit so it runs from this particular folder okay so that's a little bit on the security part for now um we'll touch base a bit more as we perceive okay so going back to excel um going back to developer tab and the this micro recorder is pretty neat feature that you can use um but today we're not going to use this it can become very handy when you're working with excel objects this portion don't need to worry about it basically you can use this to install or uninstall office add-ins excel add-ins um com add-ins which are developed from other environments okay like vb.net and so on here forms control activex control i'm gonna walk you through a bit more in detail at the later stage but for now um it's not important okay now let's go to visual basic editor uh either you can click here or press alt f11 okay so every time you hover your mouse on this controls if there's a shortcut you normally see them so i'm gonna press alt f11 okay and once you come here if you don't see anything um normally you'll see um project explorer the shortcut is ctrl r this is whatever i'm telling you now this is one time settings and this settings that you do here is applicable across all applications this same window can be launched from access database from word document from powerpoint outlook and so on uh the other window i'm interested is f4 uh project a properties window sorry and just keep this enabled if this is out somewhere uh also it's fine you can just drag it to the extreme bottom here and then it will dock it here okay um and then um i have these two toolbars already added debug and edit i'm gonna go and show you how to bring that in toolbars debug edit okay so if i choose this um and this now you can drag and drop whatever you want if you want to put it here or here for example you can put it here like that as well or like this or even like this okay so it's up to you you can put it this side as well okay so once you organize that um it's it's not a show stopper it's not gonna stop uh us from anything even if you don't add this and then what else we'll go through the main things references it's very important but we're gonna not gonna use it now you'll start using this once you start working with external data and all this okay but just to keep on the back of your mind this is where you're gonna go and add references and then go to options here by default this is gonna be checked just uncheck this is very annoying um if your syntax is not correct it's going to tell you all the time that it's not correct for now um just keep this unchecked and then if you want you can bring it back later on uh here the only thing that you'll see that is different will be the font size and this probably is called a new phone for you i've installed this custom source code pro if you want you can find the link in the video description you can install that in your computer okay but the phone really doesn't matter and then i'm just gonna leave this as it is and then once you come here docking this is gonna be looking like this for you um for me i'm just gonna check this box for now you can just leave that unchecked and then so that's pretty much it i'm gonna talk to you a bit more uh in some time okay so first let's write our code first code and see how it goes okay so for that insert a module go to insert and then click on module okay now once automatically once you insert a module you see that properties window says module here uh you can rename the name of the module based on what code you're gonna write here so all this white space that you see here this is where we are gonna write our code um and then there are additional objects that you see here based on your current workbook structure if you add more sheets here for example like this i added two more sheets you'll see them reflect here okay all these are objects we're gonna talk more about it in a bit so without any further ado let's write our first code okay so there's two type of codes or procedures that you can write one is called sub and then the other one is called function function can return a value you're already familiar with function um i'm just going to press alt f11 to go back here function sum is for example is an example of a function let's see there's 1 and 11 and 2 here and then you put a sum function here hit tab and then you select these two cells this function basically sums whatever number that you select or cells that you select like this yeah so this is example of a sum vlookup all these are you know like sumifs all these functions are something we use on regular basis so function is example of that it can return a value based on the instruction that is inside a function and then sub procedures just execute or whatever instruction is written there so the statement or the syntax to write a subprocedure starts with sub okay after a space put the name of your code so let's say that you want to write a code to greet every time the workbook opens up you can say green or create a user something like that and before we start writing our first line of code the shortcut key that is important here is ctrl j okay so i'm gonna press ctrl j now it will automatically list uh things once you type okay now i'm gonna type here um message box msg okay this is a syntax for a message box and once you hit space you'll see uh the things that you can pause okay the arguments um the parameters that you can pass to this particular method or um yeah to a function so i'm gonna within double quotes i'm gonna write hello from vba something like that okay i'm gonna go outside this box and hit enter okay and the way to run this will be click anywhere within this sub and ends up and run it like this click here so hello from vba okay so that's your first code you can pause the video and then you can write this part now anything that you write between these um you know these two lines is going to be executed when you run the micro okay now if you want to put an instruction like a comment you can just use a single code and write uh this is to uh greet users something like that okay now you can also put a comment here messes box sample code something like that okay this is also something you can do okay now let me also quickly show you example a function now just to give you a practical example let's say that um you have to check if the sheet exists before you write the data in if the sheet is not there and you're trying to write a data to it it's going to give an error right so those are like the function can check those kind of thing if the folder exists or if the file exists and so on but for now in this particular example let's write a function to return um application username okay so for that uh to see that visually if you go to options you'll see here is the username so let's say we want to return a username of whoever is that at that particular point of time running the macro uh for that let's start with function same as sub we'll write function and then we'll write the name of the function let's call this um user something like that okay so once you hit enter you'll see that this is added and this end line is added anything that you add here are the instructions for this function uh in the end whatever you want to return you will say abuser is equal to and then here you pass the value okay so for example let's say that i want to return pom i for now to test this you can copy this code you can go in here type in equal to and then enter so it will return pom i okay now similarly um if you want this to be dynamic i'm gonna remove this part i'm going to hit ctrl j again it's a good practice to do this and then application okay i'm going to use my arrow key up down to select this once it's highlighted in blue you can hit tab and then you can put a dot once you put a dot you'll see all the methods or actions that you can perform with this or properties associated with that okay we're going to talk about it in a bit now from here you can use use type in user and then you can select username option okay now if i run this again from here it's going to return loom for my okay now if i go back and change lumpa my to alpha my and click ok then it should return the updated name okay so let's say someone else opens this micro and runs this it will return their username so instead of hello from vba let's say we want to return hello to that particular user we can concatenate the string here okay now i'm gonna get rid of this hello and then all this now hello and space and then i'm gonna close this here and then to concatenate you can use this and and then you can copy this function and then you can just put it here okay and if i run this now this part will be exec it's here hardcoded but this part will be pulled from here okay so if you want to see line by line the shortcut for that is uh f8 uh the first shortcut we used earlier was ctrl j and then now we can use f8 okay f8 is to execute the code line by line now i'm gonna press f8 and then i'm gonna go line by line i'm gonna press again now it goes to this function because this part once it reaches here it goes in here okay and then it comes here and then it gets the value as i place my cursor on top of this um okay this is actually variable we're gonna talk about it uh at the later stage um and in the coming videos now press f8 again and then it comes back here and then execute this particular line okay and then it finishes uh so this is a very um a simple example of how you can write your first code you pause the video and try to do it yourself okay now let's say that we want the event to fire this particular you know like code as soon as the workbook opens the way to do that will be to use any of the objects um for example let's say i want to run as soon as the workbook open you can use this workbook object okay um and then from here you can select workbook and then it's gonna automatically select opened even okay so this even will this code will run as soon as the workbook opens so whatever code you put in here or instruction you pass here is gonna run okay so let's say we want to run this code i'm gonna copy this and i'm gonna paste it here and now i'm gonna save this okay so once you save your micro it's gonna ask um the location i'm gonna save this here in vba project i'm gonna name this as lesson 1 and you can save vba program or micros only in a certain type of excel workbook similarly with other applications so you look out for that those options for example in excel xlsm xlsb xls uh you can also create add-ins so you'll choose this option if you're creating add-ins okay we'll talk more about those for now you use this xlsm okay micro enabled workbook and then i'm gonna save it here in as a lesson one okay so now to demonstrate this i'm gonna close this workbook go to this folder i'm going to run lesson one okay so as soon as i open this it's going to run that code so hello and then room for my alpha mine okay now if we go back old f11 you can concatenate further okay here um after this you can also put i mean concatenate this uh with maybe exclamation mark something like that okay like this now you can also trigger this code uh using buttons or various other ways to do it maybe from the ribbon as well you can do that let me demonstrate a couple of examples first if you click on macros here the same can be accessed uh from somewhere here as well here once you go here this same option is there so if you go to your developer and then macros you can edit as well or you can run from here as well like this okay or you can also assign a shortcut key and so on if you uh if you're recording you can set this option in the beginning but you can also set a shortcut key and put a description as well about it um or delete it as well okay now um so we have set up the security uh we've written our first sub procedure we have written our first function which returns application username we'll learn how to put a command to the code um let's put a comment here as well this function is going to return uh application username okay um now let's say that you want to run this particular code using a button you can also insert um forms control this button from here as soon as you click or draw somewhere here like this let's say like this you can select the code that you want to run and once you control and click you can edit the text as well like this so test one okay now if you want you can also use activex buttons similarly i'm gonna draw here for this you can right click and go to properties or use this and from here you can change the text as well or i think you can also click there and edit but caption let's say um that's two activex something like that and i'm gonna exit design mode first close this exit design node uh okay going back double click on this and then put a code that you want to run okay like this so automatically this line of code this button event click even has been added here similarly this button has more events okay we're gonna go more in depth at the later stage i'm gonna save this close this and click here exit the design mode and click so it will run the same code that's that's another way to run it and you can also add on runtime controls in this um as well we'll talk more on those um what else okay one last example for this is you can also insert um like shapes any of these shapes and or you can import image from online and then you can insert picture and then you can run the same code using those okay for example here i can say test tree using shapes make it bigger and then you can you know like change the properties and then i'm gonna right click on the shape assign a micro and then select the micro so there are various ways you can execute the macro like this okay now uh this is not so useful yet but it will give you a flavor of uh how to run it how to construct your code sub procedures how to write your function okay in this section i'm gonna show you how to work with excel objects then we'll start creating our first real life project okay as you know excel application contains various objects such as workbooks worksheets range column rows charts pivot tables shapes and buttons as you can see here and the list goes on and on uh each object has its own set of features um also known as properties and then its own set of uses and those are called methods okay now let's uh write this down let me just put it down here so we can visualize so first is our application which is in this example excel now if you're writing in word the application will become word document okay and then within application there are workbooks and under workbooks you can add sheets or worksheets and then you have range columns rows and maybe cells and so on and then within the worksheet you can add pivot tables and then within pivot table for example you'll have uh pivot fills and then it'll have items so similarly uh if you enter the table here they will also will have you know like the body rows and column items and so on okay so the list will keep on going um but the structure basic structure goes on like this okay now let me give you some more examples on application for example uh here we are using a property we are reading application property username and it returns as a username okay for this demonstration let me do press ctrl g for immediate window and then so you can press this as well or shortcut is ctrl z now if you uh let's say we want to uh get application dot version this will return 16.0 so my excel version is this okay um the way to use this immediate window is um if you put a question mark uh whatever you type here it will return that particular value if you type it correctly okay now um you can go to object browser for all these objects and then um or you can press f2 we'll try a few examples for each of these objects okay now here i'm gonna copy this object and paste it here now you'll find uh methods properties events associated with these objects um all of them you can find it here okay so for start you can just get familiarized i have grouped by members so uh this first symbol this sim symbolize properties if i press u so username is here this is something we've used here application dot and then this object dot all this you know like properties methods will come will be listed once you do it uh for example if i don't group members if i type application dot you'll see all these options that are here in this area right okay active cell active chart and all this so that's the way to connect the properties and the object with the properties or methods okay by putting a dot um let me just group this again for now so these are properties symbol and as you scroll down these are methods this symbolized methods and then if you go towards the bottom you see this like lightning sign that is symbolizing events okay so we've so far we've used workbook open even right uh let's try another example let's say instead of this i'm going to copy this block paste it here okay so you can have many procedures and functions as per your requirement let's say uh we just tried application.version let's say we want to get a version f version you can do this right so if i if i want to quickly test the value of this particular function sorry i'm gonna make it here and if i copy this function and paste here and then put a question mark and then put um you know like the code or the method whatever you want here and then once you hit enter it will run this particular uh okay it's giving error hold on uh it will run that particular function and return you a value here okay so for example this one is still referring to this app user that's why it's giving her okay so 16.0 so let's say we want to try let's say build maybe we can try this now instead of running a function like this let me come here question mark application dot build and this is the build number okay so if you go to your product i think you'll find that and maybe we want to find the vbe version or we can try that as well um dot vbe dot version okay so 7.01 hell about this will give us this version okay um so there are many other examples that we can try for example we can just try application dot uh operating system so this will give us uh the windows is uh my windows is seven windows seven and the bit that you're running at okay so application uh this will maybe at the later stage you'll use like let's say that your program will run only for a specific version so we can use validation that okay if the application version is lesser than 16.0 then just give a feedback to user that this software is not compatible with your office version for example okay now let's try um so we've tried about application uh now let's try for workbooks okay uh for this demonstration let me add multiple workbooks press ctrl end and as nancy and then one more i'll add three workbooks okay like this and then i'm gonna bring up the editor again i'm gonna use windows right uh left arrow key and then i'll bring the site i'm gonna close this for now or i'll just bring it out here okay maybe not so before we work on this workbook what are the things that we can do with workbook um you can add new workbook um so for example using application only you can i'm just gonna write a new code sub test uh object of the grapher demo so let's say uh application dot and then you can find workbooks here dot add so for example this is here we are using workbook uh workbook method called add and if i run this it's going to add a new workbook okay so before we go into that that is one example you can add a workbook uh you can get the name of the workbook you can save as workbook into a specific format uh csv xls pdf and so on html you can share unshare you can log the structures and so on there are many things you can do the way to refer to the workbooks uh let me just give you some example so here we have three workbooks okay uh the way to refer to workbooks will be um there are for example once you type in ctrl j workbooks and then as you open the bracket uh it says index okay so you can refer to the workbook by the position index number if i put one dot and then you see that you can activate the workbook uh you can do a certain thing with that particular workflow so let's say activate now if i run this it's going to activate whatever is the first workbook okay this is the first workbook so if i do two it's gonna activate second workbook and start workbook okay now the other way to refer to this workbooks will be um you can specify the name here within the string okay so whatever comes within these double quotes is string okay attacks basically now let's say we want to refer to this workbook which is called lesson1.axalism well let's try that so i'm going to run this so currently i'm activating in book two manually and once i run this it will activate this book okay it will go there okay so this is how you can work with workbooks um let's try another example as you can see i'm commenting on this out so it's gone not gonna run this it's gonna directly run this next available line okay um now let's these books are not saved yet but you still can refer to it for example do and there's no extension yet now if i activate lesson one book and i run this it'll go to this book okay now um since um once you start writing to this cells and range and all this it's important to know that you go by a hierarchy for example activate the workbook and then you go to specific sheet otherwise if you just run the micro it might run in whatever sheet or workbook it is currently open so that's why we need to uh learn all this um and get familiar with all these objects their methods and their properties okay now i'm going to save this the next part about the now we can also take how many workbooks are currently open again i'm going to control g and then i'm gonna put a question mark workbooks dot and then count it'll tells me there are three workbooks open um workbooks dot and then if i add it's gonna open close it's gonna close the workbook um yeah for now i think that's pretty much it um close this now let's proceed to um sheets okay now for worksheet um the way to refer again will be same uh you can refer to this okay sorry uh sheets and then once you open the bracket it will tell you you can refer by uh index um so i'm gonna say one dot activate okay sorry um so at the moment uh third sheet is activated now this code is gonna go and activate this book first so i'm gonna comment this out for now i'll show you how to put all this together in a bit now if i run this we are expecting the first type to be activated sheet to be activated yeah as you can see here now similarly uh let's try the same approach let's refer it by name i'm gonna comment this out just give me one second this is actually in the log mode because i double click here let me go here and then i'll go in here and continue to run this okay now if i put in three here and rerun this it's gonna activate this particular sheet now you can also press f5 and run this code or you as you already know f8 and then you can keep running the code now uh as you know that uh when you're referring to sheets um you can also directly refer to the object name okay so for now we are working with this lesson one.xlsm this is the book we are putting the code okay so when you're doing that you can um refer to the object id itself okay so this is the id and then this is the label for example let me demonstrate that um demo now we can see uh the the demo part is the label okay this is just a caption and then sheet three is the id okay basically um so if you have to refer you can also refer it by like the c3 dot activate okay you can also refer it like that so um you can i'm just pressing f4 uh you can rename this sheet tree the the name the id part as well as maybe you can say um admin or something like that okay so now this id also has been renamed now you can refer to that using demo dot or demo sheet dot sorry admit dot activate okay or you can say uh i'm gonna run this f5 if i go in here f5 so it goes there similarly you can also admin dot select as well okay just press f5 so that this is different ways you can refer to uh just going back to workbook i forgot to mention instead of the workbook name if you always want to refer to this particular workbook uh you can use this object name as well for example like ctrl j this workbook dot activate okay so you can also do that i just run it okay so let's see my cursor um my active workbook is this and i run this it's gonna come back here okay now you can refer to this uh object name itself like this admin uh which is this only when you are in this workbook okay if you want to refer to this book of this particular name for example let's say that you put a name here the object name as uh front-end or something like this now if you try to refer to this dot you're not even getting that option as well because this is in the different workbook okay so if you have to refer to other workbook sheets then you'll go by this particular method okay and then for that you have to even go and activate the work that particular workbook first uh now coming to so that is about the the worksheet how to activate the worksheet now let's say that you want to rename rename sheet for example so i've just created a new process process called a sheet let's try this uh on this particular sheet okay i can say active sheet dot name is equal to uh this is the property and then we can set a value to this let's call this vba test so this uh we have just changed the property that is the caption of this particular sheet um what else can we do with the worksheet um we can um we can add a sheet as well and okay so as we go on we'll we'll explore more examples let's not go too far into this for now now coming to range okay i'm just gonna put a new sub um range example now you pause the video and try each of these um you know like examples how to refer the workbook how to refer to sheets and range and so on okay now when you're referring to range there are various ways to do it um for example the easiest way is uh within this bracket you can specify the object name it could be some not even just a range so for example let's say we want to go to uh d7 i can type in d7 here dot select okay now i place my cursor here when i run this code it will go to d7 okay and you can say d7 dot value we're putting a value to that cell let's say hello okay now this is one way to do it or if you have to read this particular value you can also do it like this it will return whatever value is there so if i put in um if i rerun this it will return that so for example let's say that message box excuse me so this is example of how to read this is example of how to write okay uh if i just disable this part for now and then i say um vba a to z and i run this code it will return me the value of that particular cell now similarly the syntax there are various syntax now range is made made up of rows and columns for example so let's try another example cells and then you can specify here row index column index okay so let's say we want to go here you'll specify one two three four fifth column and then uh fifth row okay so cells five and then column is also five for example and then you say select now let me just disable this part i'll just comment them out uh if you want to comment bulk once you can use this option okay uncomment all comment all okay you can select and do that now if i run again this code it goes to this particular section so this is how you can refer to it by rows and columns now the reason why i'm showing you all this is like uh different scenarios will present itself to you and then you can make instead of five this can be a dynamic value later on similarly the column as well and then the way to set the value again is same dot value is equal to sales example right i'll just say like this okay and if you want to read again you will do the same thing this is box cells this dot this okay if i change um if i comment this out if i change the value here that's three if i run this it's going to return whatever value is there so again this is how you can write how you can you are reading the value of this particular object and then here in this example you are setting a value this object dot value is equal to this so you're setting it like that okay um the other way will be using range statement uh method uh object here you can specify in the exact way which is uh you know like there so let's say e5 dot value and then here you can put in the value hello something like that so let me comment this out again okay and then in the same way you can read again range.value let me just do a debug.trend this so debug.print will basically print um whatever you specify here in this particular window okay now let's oops i think i'll just bring it up here uh because message box keeps popping up so instead of that uh now instead of e5 let's say that this is um g5 so write a value called um range to g5 and then print the value here okay i'm gonna okay so it draw the value here and then it return whatever value was written there as well um what else now you can also let's not go too far into this but i'll show you one more example um within the range you can specify the cells for example um range and then within here you specify cell one cell two okay so cells and then here you put in row one and then let's say fourth column and then second cell so basically it's like you are copying and pasting it here okay now i'm gonna put i'm just gonna talk about it so basically what this is saying is i'm gonna get rid of all this in this method once you follow this approach you can specify what will be the first for example this um what will be the first let's say that you are selecting a range like this okay you in this method you are just specifying the first cell and the last cell like this okay so let's say i want to do this selection like this uh first cell is b2 which is uh column two and row number is two okay row number two column two and this one is um eight right three four five six seven eight and then nine so the column is eight and the row is nine this particular cell right you can also print this by the way active cell dot address something like that right uh so if i run this now okay so this method is also very useful when you are constructing a dynamic range like you have to dynamically set what will be the starting row and column mode will be the ending row and column okay so this is very useful just remember that and just play around with that try more examples by putting another value here maybe um fifth row fifth column like this okay um uh in range let me just talk one last thing about offset um let me copy this uh this feature will be uh this method will become very useful so range a1 dot offset and then break it open uh using this method you can offset like uh number of rows or columns uh that you specified here okay so let's say that um you want to offset um one row down one column um towards the right you can specify offset row one and then dot uh one column okay dot select now if i run this we are expecting to select this okay now if you specify two it will go two columns away okay now if you specify zero here if we say same same row don't offset don't go down or up uh then you can do in this method now instead of if let's say this is a5 you can also go negative like this okay so from a5 it went up one row uh and then two columns away okay so this is very important now if we have to put all this together let's say that you want to write a value to um let's say a sheet called vba test basically how we are going to write it is like demo2 first we want to activate this book let's see that the code was run from another book we don't want to write a value to that particular or for that matter the sheet is not going to be there in that workbook so it's going to give you an error so the way to approach this will be you'll first specify which workbook you want to activate uh this workbook refers to the book where you are writing your code okay now second from this workbook we want we are only interested in this particular sheet so i'm gonna say sheets and then within the bracket uh code double quotes i'm gonna put this and then i'll say activate okay now from this particular sheet let's say that i want to write a value to um let's say a5 here you can say demo to whatever value you want you can pass it i'm gonna press f8 uh now i'm in this book okay book one when i run this it's gonna activate this uh lesson one workbook and then it's gonna go to this particular tab this tab and then write a value in a5 now for testing i'm just gonna navigate away from that okay so book one is here i'm gonna press f8 now we came to this workbook it's activated now if i run this line it's gonna activate this particular sheet okay if i run this again it's going to place uh an a5 demo two value okay so demo two okay so that's how you're gonna go through it um you can also directly um positive value um in one single line which is not important by the way but you can do that instead of activating you can also specify in this fashion and i make this bigger so this line is equivalent to this particular line these three lines can be written in this method as well you don't have to keep activating you can directly go and put a value there okay uh let me make it smaller okay so this this way also works okay now we can also quickly check the object browser for this uh things that we have checked uh let's look for range i'm gonna select range and then these are members for range for example um things that we can do with drains for example we uh when we right click you can see like copy cut and all this delete everything should be here as a member for example um copy okay so here is copy for example if we have to try this out range that's do uh let's say we want to copy from this rings a5.copy e5 let's put a copy number here let's say we want to copy this if i run this now if i run this code it copies right now let's say we want to paste if we want to place we have to use a sheet but if we want to paste bezel then yeah that can be let's say we want to paste special in a6 dot base bezel and then you can choose option you can paste all you can paste only the commands formulas values and so on so let's say we want to paste values only okay so let's run this f8 this is copies a6 it based there okay um so i'm just giving you all this uh small small example so you can get familiar with how you can deal with objects their methods and so on so as we do more examples down the line you'll get more familiar with it for example if you look for paste uh there are so many paste here so uh worksheet so we can say copy this and then let's say we want to select a1 and then we want to paste here okay so active sheet dot paste i hope this is gonna work uh f8 copy selected this range and paste okay so this is another example so similarly um pivot tables are there other rows and columns there are so many things we can do so down the line as we progress uh now that you're familiar with how you can join you know like these objects together and then manipulate uh the property right use the methods associated with it and now let's go ahead and start designing uh our form and then learn how to let me open this learn how to export the data from the form to the data tab here okay uh so here basically uh it's just a worksheet like this if i enable the red line of the grid line is how it is i'll show you from scratch everything and then i'll show you how to move this data or copy this data here okay so i'm going to add a new workbook for now i'm going to close this unused workbook we'll just save this one and forget that okay so this is a new workbook i'm gonna save this as excellent some file to the desktop folder and we'll call this project um i'll just call this project one now the way i design this uh is is a very simple um you know like a worksheet with cells uh for example if i remove this you see there's a watermark coming back there's no code for this uh you can easily design this i'll quickly uh demonstrate that now so first i'm gonna create a block like this okay uh put a border of your choice just like this and then to the top as well something like this and i'll just copy this one this is calibri foreign you can see there is that small like a space there similarly this side as well i'm gonna do like this and then here comes version 1.0 and then there's the date these two rows are resize small like this and then comes this labels for the fields i'm just gonna copy and paste it here okay and then between these cells if i enable the crit lines here show grid lines okay and if i enable the grid lines and then here the row height between these are smaller so i just put it in this fashion okay or you can make that bigger as well and then this column is bigger like this and then there's a small like a hidden column that is i'm just going to talk about it a little bit uh just to visualize it better i'm just going to turn it off now to make this cell look like a text box what you can do is you can bring up the properties window right click properties format cells okay from itself okay so once this year go to border from here you can select uh first we'll select this uh black and then we'll put it here like this and then okay just give me one second we need to select this hidden column as well not hidden exactly but this one let me make it big for now like this okay select these two columns control one is a shortcut key to bring this up now for the this border side i'm gonna use this dart and for this side i'm gonna use this okay like this okay so now it looks like this uh you can just copy and paste this for these two cells okay and maybe we can make this text box bigger okay so this is where we're gonna capture user inputs and here we are gonna put a watermark so for example if i go here i'm gonna unhide everything for example what i did was i just put this text somewhere here like this and then this is basically i'm saying here that if putting a formula here that if this value is blank if it is equal to blank then just show this else don't show this okay now here the what i did was i um you know like align right like that so what i did now i put more space so that this comes in the middle here you can use this properly let me just maximize this okay and then i formatted this particular cell as you know like so that it looks like this not very visible and then i make the you know like colon really small okay so similarly uh i copy i can just copy and paste this here okay so this can be hidden later once you're done for example i'll just select this column ctrl shift right arrow key and then i'll hide all the columns okay hide and here is the watermark okay simple as that now if you type in the name it goes away and it remove it it just shows up there again okay so it's a simple way to guide the user alternatively you can also obviously use uh here validation uh where did i bring this from i always use the shortcut key sometimes i don't remember what are all this data all dl is what i did hold where's d i don't even see the dl anyway so validation list uh instead of list any value you can put um input message like enter client name this is actually not vba you can also do it like this so once they click on the crochet it just shows down here you can also do that uh if you want okay i'm just gonna clear that validation so now the cells are ready uh what else did i do so i'm just gonna copy this these are just dummy text that i put here just to show that okay this all can be done like the sample instructions and the button and then this particular cell so um you can also pop up a message box and tell the user that okay it's completed successfully but once they click on it it's gonna go off uh so the other way to show is you can put um a value here you can write what was uh what did the process last to last execution status right and here for the button you already know for this you can insert this or it's up to you so what i did with that button was i put it like this i think i made it transparent and then submit data or something like that and then i put it the middle make it bold yeah so something like this and then this is where we're gonna put our code now um control s this one is not going to be too visible we don't want to get focused here okay so now we can write a code let's call this form now this input is just a demonstration this input can be anything for example um this is client date and amount which we want to send it here in data tab for now i'm just going to copy this data here so that it does not look too empty i'll just get rid of all this data like this okay so once the user clicks here we want to submit the data here now this is a small demonstration but then you can use the same example to write this particular input to another workbook uh you can submit it to the database to the servers there are so many things you can do you can implement this for let's say recording attendance you're recording cells um there are so many things you can do with this okay uh so now you already know how to refer to the workbook to the worksheet to the range read the value so we are gonna use that um to uh to accomplish this okay uh so i think we are good here i can go ahead and close this okay all right now um let's start writing our code i'm gonna go to visual basic editor and then i'm just gonna copy this and put it here so that i can show it to you um and now i'm gonna close this lesson one file so that there is no confusion and from here i'm gonna hide the headers as well just for now and normally what i did do is like i insert a row here like this and then put the height of the row to one and then i normally hide the cursor there like that okay um okay so going to visual basic editor the normal practice is go to option uh to this vba project properties and then you can name the properties okay so let's say this is um data input project or something like that and then if you want to protect your code you can go to protection and you can put a password here as well if you do just remember that okay one two three i'm just putting one two three okay now you'll be able to see that instead of the book name it has changed the data input and maybe i can just put a pj just for present okay now this is the file name this is the name of the project okay like we did earlier i'm going to insert a module and then i'm going to just rename this module to data capture something like that so let's say that you have lots of other lots of process that you need to create then it's better to rename these modules so it makes sense okay okay so the best way especially in the real scenario if you design something like this for real then it's better to put a name range this for example here i'll put client instead of d9 i'm going to rename this to let's see or i can go to formulas manager or i can click define name and then this is going to be client and then it refers to this particular value okay like this so now instead of that particular cell which was d9 now it's client okay so when you're writing range and then you can put client instead of d5 the good thing about this approach is that even if you insert more um you know like rows for example you have to reorganize your form then you don't have to change the code otherwise let's say that at first you run d12 and then once you add more rows in between like this instead of d12 you have to go back to your code and change it to d13 okay so the good way especially when you're doing in this approach is to rename this this range to a defined name okay so i'm gonna add this as date is actually um could be a reserve name so maybe you can just put a p parameter date and okay similarly i'm gonna do this for amount define name and then amount click ok now we have renamed this cells to a name range okay so it's good because we didn't cover this example earlier so now we can use that example as well then i can make this small so that the focus is not on the version but it's on the name of the form and then now comes our code so let's write a code let's call this sub all right let's put xo this side and put the code this side now sub and then let's call this um that new data uh if you you cannot start with a number and also just watch out for those it will give you an error just put a name similar to this and let's put a command this will capture user input to data something like that so it's a good practice to mention something about this and then you put a date as well for example 18 20 20. and then in the future once you come back you will know exactly what the code is supposed to do okay um the first thing is that we need to read these values okay so um as you already know like you can put range and then you can specify the range that you're interested in so for example we are interested in client like this i'm just gonna show this here dot value so right now it's blank so let's input something let's say combine so it returned from y okay now um you already know how to read and you already know how to write so we are gonna write the data tab so here we'll say sheets and then data activate let's say we want to go in the way we learned earlier and then here we'll say um range let's say a9 and a9 we want to put decline value okay now if you just write it like this it's going to refer to whatever is um if you just hard code like the address like this it's gonna refer to the current sheet so uh the good way is to always uh add the name of the sheet as well for example in this this is form so sheets dot and then here so we're saying that sheets data uh in this data sheet in a9 we want to put the value of uh whatever is there inclined and then from this particular sheet okay so this particular seed range value is equal to this sheet dot range dot value okay for example if i run this code now okay so it added a value there or you can uh for example if you have to do uh in the other approach you can say um range let's copy this first we want to activate this first dot activate okay this sheet is activated now and then from here we want to copy this so let's see range dot copy and now we'll go to data tab this step dot activate and then we can say this value this particular range dot we can paste special and then we can paste it as values we can also do that and then we can say application dot sorry dot copy mode this will simply just clear the clipboard okay this particular line now let me just run f8 now just imagine i'm here oh sorry so it activated this particular tab you copied this value and then it goes to data tab and then in a9 it pasted and then if you look here the base is enabled but as soon as i press f8 this will get grayed out okay because the clipboard is gone okay all right so this is one way to do it and this is another way you can directly specify that okay this particular range from this particular sheet value will be equal to this particular value okay so this how you can set it as well now similarly if we just replicate these three lines for client p date and amount and then here uh a9 b9 c9 right um so i'm going to clear this i'm going to put a date here and the amount here now i'm gonna run this okay so the value has been added here in this method this is more efficient way to do this actually i'm gonna run again okay so i just added but in this if we follow this method it's gonna keep adding to this particular cell only because this is static okay so now i'm gonna teach you a simple loop that you can use and then we'll find the last row with empty cell and then it's gonna write it there okay now let's uh create one more process here a loop test test okay now this loop uh will learn due until do until this is uh this is the syntax statement um here we'll put a logic okay we're gonna put a logic here and then uh we'll put a loop whatever you put in between this this particular area will keep on running till the time this particular logic becomes true okay so let me demonstrate this here for example let's say we want to keep running this particular code till this this particular cell value is 10 or greater uh what we can do is range a1 dot value is greater than or equal to 10 okay so at the moment is blank so it's zero and then we need to increment this particular cell as well maybe at first we can say zero and then i'm gonna get rid of this we'll increment the value of a1 a1 dot value is equal to whatever value is already there plus one okay i'm just gonna increment one one two and then as soon as it reaches equal to ten it will stop and then it will go down here okay and then here we can see mrs box complete something like that now i'm gonna do f8 okay so this particular line executed and this turned into one okay now this this particular block is going to keep running till the time this is then so here the logic could be whatever uh you know like it could be based on scenarios but now the idea is just to learn this is how the loop works now f8 again so it changes the two changes to four five and so on and then now it's nine now it's already ten so it's gonna go down here okay all right so that's how it works uh this particular loop do until the condition is true and then you write some code here to alter whatever that particular scenario is otherwise um it's going to run forever or maybe um we can put a condition again in the next chapter following chapter we're gonna go into all this but for now we're gonna keep it simple okay now going back to our code what we can do here is we will activate this particular sheet and then now we are gonna write this uh use this loop to keep going uh from here all the way down and then whenever it finds an empty cell it's gonna write to that particular line okay so let's try that um going back up here i'm going to disable all this so all these are commented out now that means this part is not going to run so first it will activate this particular sheet and then it's going to activate um a2 range activate or select okay range a2 dot select and then from here we want to start looking down till here or whatever is the last row so we'll say do until active cell dot value just go to blank and then we're going to say loop and here we can say active cell dot offset we talked about this earlier now we want to offset at first it is here okay now you want to offset one row down so one and then zero column dot select okay now this code will keep on running it will keep on doing next next next night still the time the cell is blind okay uh now i'm gonna just write a stop here okay now when i run this it's gonna stop here or it's not a big data set i'm going to press f8 okay so it's selected this cell f8 keeps on going selecting next cell the moment the cell is blank it will stop running that okay see that so now we are gonna put a value here okay now i'm gonna copy this block of code now instead of um specifying this particular range i will say active cell dot value this value is going to be whatever is their inclines we're saying that this particular value is going to be whatever is here okay sheets form dot range this claim range client dot failure okay so similarly um we want to write in the next uh the date value right so we'll say uh so just imagine it is selected here and now to write to this value we will say from active cell we want to offset offset we're not going to go down so zero rows comma and then we'll say one column okay one column dot value is equal to now here we'll put the p date now p date is uh daydreams i mean the name range again this value okay so similarly for the i'm just gonna copy and paste this code this line copy paste now in the second offset second one two offset two times and then here is gonna be amount okay and the amount is coming from here amount okay so now we can test this code let's say this is lucy and then this amount now let's run this code from here f8 okay i'm gonna make this smaller so we can see side by side okay now it's gonna start going down so it's checking if the cell is playing it's not then this particular line just selects one draw down right so it keeps on going down till the time it finds that cell now it will start running because the cell is empty the loop will stop running this particular part and now it comes down to active cell active cell is the cell that is currently on and then it plays lucy there it plays the date replace the value okay hold on activecell.offset oh my bad this particular part is supposed to be this range is the amount and this is supposed to be like this okay and there comes the amount now we can format this as well to currency when you're working with amount uh it's advisable to use currency okay so that is what uh uh we want to accomplish today maybe once you're done uh we want to write last execution status um now we can just say um like this so for me sheet one so we can say one dot and here we can say range five dot value is equal to for now we just have this option okay tomorrow or uh in the next video when we learn uh how to use if how to use additional loops um how to you know like um use variables and all i'll make it very simple for you uh for you guys and then uh it should be easy okay so now let's try i'm gonna add maybe items adam and something like that okay now we will hook our code right click assign program add new data okay and then okay so adam maybe we can return a date and time here so along with this status we'll say um ampersand and then here we'll just say now so now it's basically just going to return it's uh in build function now it will return date and time okay uh let's try and see okay so date and time is included when it last executed okay now if you want if you don't want to go here um maybe after it is completed you can come back here don't activate now sheet1 is from basically form the word form is the caption and this is like the id okay now if i write here as um maybe sylvia and then here you put in the amount now this amount again can be currency and this can be put here in the middle similarly like this okay so it goes there but then it comes back here okay so this data has been submitted successfully okay so that is gonna be all for this uh for this lesson try to go through um you know and type out everything it's very important that you type and if you don't understand specific part of it rewatch it again um try to go slow try to understand um at first it might be a bit uh there's um it'll take a little bit of time to get used to this uh syntax and it's though it's very simple um at first there is always a curve okay and then uh if you are not able to resolve anything uh you put your questions in the in the video comment section okay and then i'll try to come back to you guys as soon as possible um so i work full time um but mostly in the evening i am free so i'll try to come back to you guys but then there is a waiting window there um but i'll do my best to come back to you guys as soon as possible and if you have any questions again do not uh send the email i won't be able to read all your emails instead put in the comments so that others also can benefit from your question and the answers and um the assignment for this particular lesson will be um you'll replicate one more uh input box here okay and then that input box is gonna be uh like the client type you can call it client type and then um so here you'll create a client type you'll add this same box with the same watermark similar like this and then when you click you'll add a new column here and that value is supposed to write down here in these four cells okay so that is your assignment once you complete this assignment then i'll send the uh in the following video whoever completes the assignment will get the link to the following uh tutorials or lesson okay so i hope this uh has helped you um this is a bit long video because i don't want to rush into it but i want to go slow and so that you guys are able to uh grabs the you know like how you can uh work with objects how you can access the properties how you can use the methods and so on so uh that is all for this particular lesson thank you so much for watching and yes if you have any specific requests uh maybe you can email me but uh it will be more beneficial if you could comment in the video section video comment section okay so thank you so much for taking this lesson again and i'll talk to you guys in the next uh lesson thank you bye

Channel: VBA A2Z

Views: 81,032

Rating: 4.9446688 out of 5

Keywords: vba, programming, beginners, excel vba, access vba, advanced, free, excel 2019, excel 2016, office 365, free course, programming concepts, get started, how to vba, absolute beginners, outlook vba, read excel, write to excel, excel macro, vba automation, vba programming for beginners, vba programming tutorial for beginners, vba excel programming tutorial advanced

Id: 9lEhwpGNlYU

Length: 93min 13sec (5593 seconds)

Published: Sat Jul 18 2020