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.
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.
Posts Related:
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 - Make Automate
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.
