Excel VBA - Beginner to PRO Masterclass with Code Samples

oh hello hello hi can you hear me all Welcome to our live stream sorry uh sorry for the late start I was here on time but I think I didn't press the right button or whatever I'm having a little bit of technical difficulties um so first up let me know if you can hear me loud and clear in the chat otherwise I'll make my adjustments on the mic and once all it's good we are gonna go into some really awesome VBA live stream this month yes yes yes okay cool people have said uh they can hear hear me all right that is awesome um Tom says maybe I overslept [Music] yeah yeah yes so that's all good um foreign tell me where are you watching this from uh which part of the world you're tuning in from and uh you know what sort of VBA work have you done previously in the chat or if you're watching it in replay later on as well uh so yeah which part of uh world you are I know we have got a kind of like a semi busy chat room going on before the stream started I even had a poll asking you whether you have done any VBA code and we have got 300 votes and about 220 of you have not done any VBA coding this year it could be because you didn't want to or whatever but it was good to see that oh whoa whoa whoa whoa this this chat is running crazy I probably won't be able to catch up with you all uh India Tamil Nadu says [Music] she's uh or he watching from London uh and uh Brian from Montreal Quebec Canada Eddie from Nigeria mess from Chennai India I got people from coimbatore Nepal Sweden here is a funny story exactly 14 years ago even though while I was not in Sweden on this date I was in Sweden 14 years ago in the month of September October November all through 2009. you might think oh why are you being so specific that's because today happens to be a very special day for me it's my kid's birthday I have got twins some of you know it so it's their 14th birthday and uh and I was in Sweden when um when my wife was kind of about to deliver and I flew back to India to be with her for the delivery and you know welcome the twins into the world Moses from Singapore and uh Hassan from Gambia and uh wow wow wow UK working as a data analyst in a company where Excel and BB are widely used it so I have been building a lot of tools with VBA okay so this was the scary part for me when I wanted to do a VBA live stream I have been for the lack of better words you couldn't call me as a hobby coder when it comes to VBA I mean I do know quite a bit of programming I have done uh more than my fair share of coding in my life but I am always like to dabble in things so I Like To Learn Python VBA this and that a little bit of this little bit of that so when it comes to vbn nowadays I consider myself to be like a VBA occasional developer so we'll talk more about that in a minute but it is good to see all of you coming in and you know sharing your your time with me and being on this live so so good to have you all um will put our official welcome message which is let me know what is it that you're drinking I'm gonna take off this message as well so we can Narendra says happy birthday thank you thanks for the happy birthday message to the kids and uh I'm just gonna move this guy a little bit about so let me know what you're drinking uh that was the other reason why the stream was kind of started a couple of minutes late because I went downstairs to grab a cup of coffee uh let my dog out because it's quite early in the morning here in Wellington New Zealand by the time I got back up I thought okay I had everything ready and I hit the button and then obvious gave me a message saying you need to set this up so that was that um yeah so it's it's uh oh we got the hazelnut coffee from Jim enjoy the drink my friend and uh Randy is having water a lot of people are drinking coffee um and Ben posted something that looks like some really fancy thing it's just tap water nothing wrong with that um green tea and Swati says it's dinner time here in India Hyderabad obviously it's dinner time so yeah enjoy the dinner with a little bit of VB according uh Anand is probably having my favorite drink in the world mango juice all right uh whatever you're drinking or having enjoy it sit back and you know join me on this kind of like I would say this is an intermediate beginner to intermediate level VBA session I'll try to add a little bit more advanced glimpses of EBA if possible depending on how far we go so what are we gonna do um we're gonna do three things I made a note here on my notepad to make sure that I'm not missing any of them the number one thing that I want to cover in the first five to ten minutes of the live stream going forward is a quick introduction to VBA so maybe you'll do that for first 15 minutes I want to explain what VBA is how it appears within Excel how to use it and how to get started with it so that's the first goal then the second thing is to create a simple practical scenario based thing using VBA so that you can understand what VBA is and how it would fit in to the world of doing things in your job for this purpose I'm gonna pick something relatively simple because again the idea is to not complicate the things so that you can understand the language the system the concepts better so the idea that I am having you can put a feedback right away now say that oh no that's not a good idea let's do something else or let me know if you're happy with it and the idea is I'm thinking to build a simple to-do list application using Excel it's a simple thing you can understand it and even if you don't know VBA you can use something else in Excel to do this so you're not going to miss out tremendously but because it's simple you will will not be lost in too much of the detail so that's what I thought but if you think there is some other simpler thing or different thing that we could do um put that as well I'll try to pivot now excuse the pun um and and do it and then the third one so once we cover the basics once we build a really simple thing then we are gonna whatever that simple thing is we are gonna take that and enhance this before we go further into the actual VBA side of this stream I want to give you some couple of kind of like announcements number one is use the chat to talk among yourselves or ask any questions while I'm explaining things if you're asking a question put a q in the front and ask me that question that way I can spot it uh in the hundreds of messages that are coming and the second thing this is a really important in in live streams like this is if you put some message and I don't see that don't spam it I can see somebody Mohan Krishna or whatever spamming same message posting it 10 times this is not cool so be nice if you post it once someone will see it otherwise you know don't worry about it and the third thing if you know a better way of doing something that I'm explaining put the comment or chat message there so that others can see it and help each other out as well as help me because like I mentioned earlier even though I've done a lot of VBA coding in my life as we are moving more and more towards futuristic Technologies and enhanced Excel I I'm using less and less VBA so I'll talk a little bit more about that once we get into VBA but that is that so I'll quickly address some of the questions that are coming um faceless says will this video be available to watch later it's night and bedtime yes of course that is the plan usually for my live streams I don't advertise that they can be watched later all the time simply because it's all live so sometimes there can be a technical difficulty or something funny going on or whatever and you know I may not want to show that thing later on feel free to check back tomorrow if you have got to go to bed um the other reason I try to put it in the weekend is because of this so even if you you know have you can always stretch your time a little bit in the weekend the weekdays is a little bit busy um so that's that uh yeah Tom says sounds like a great plan so let's go with that um [Music] and we got some specific questions already like you know is this is module the only place where we can write VBA code Etc you will understand some of these as we get into the actual stuff right now we're still kind of building up you know think of this like the introductory scenes in a movie uh I also want to mention that how long this is going to take uh right now it's 5 15 so 15 minutes in into the start time even though I'm hoping you know for the next 60 to 75 minutes is going to be the real content and then I'll hang around for a bit at the end to take and answer questions and Share My Views Etc thing that I will briefly talk during the stream is how VBA compares to python office scripts to other programming options right now available in Excel as well as how VBA is used by me these days like how do I use VBA because you know everybody has got their own things with VBA but personally for me what I see as a valuable thing in VBA where I use it and where I don't use it so I'll talk about that because this is really important when you are trying to learn and use VBA if you don't know when to use it then chances are you will abuse it or you will misuse it half the time so that's a good practice as a as a developer to keep in mind VBA is a tool and you may want to use it only for certain things and not other things so that's gonna be another thing all right with that introduction I think we have built this up enough so I'm gonna hide the what are you drinking scrolling message there um but whatever you're drinking you know feel free to continue that let's let's go into that uh all right so I'm just gonna quickly start up Excel and move my windows a little bit around so it all looks a little bit too busy in my mind let me minimize this guy and okay so here I have got Excel I'll show you a simple demo because when I wanted to run this stream I I posted a message yesterday and in that message I wanted to tell people that hey I'm doing a VBA stream tomorrow come and join me so I wanted to create something fun so I made this a little little VBA thing uh it's a it's a one page VBA application you know it just says click me when you click on it it's gonna show a box here uh it says don't forget VBA live stream tomorrow and then a button there see you soon okay close the software you can click so this is a a really simple VBA thing that I built just to kind of build the buzz around this whole thing uh you know I thought it'd be a cool thing to see this is not a practical application for VBA unless you have a YouTube channel or whatever and you wanted to kind of create some interest on something there is no other practical value for this kind of thing maybe there is some I don't know um but anyhow we'll gonna create a blank file I'm gonna close this other guy around I'll make one other adjustment to my computer I should have done this before but sometimes uh you know life happens so you tend to forget these things which is I'll change my cursor color to a different one so that you can actually see it better okay so you can't really see it while it is in the Excel grid but you can see it when it is up there in a purple color one so I like to have it like that and I'm also gonna quickly enable Zoom it there we go there all right so we are here in Excel right now I'm not doing anything so when I'm doing something I will obviously zoom into that area so you can see what I'm doing uh but I'm using Excel 365 even though um I'm using Excel 365. you can literally use VBA to in pretty much any version of excel like unless I don't really want to say unless what because I don't remember what version of excel didn't have VBA I think it has been around for a really long time so yeah you will have to be using something very very old for VBA to not work but uh yeah the one thing that you may want to do again this is not necessary but it helps you a lot is to enable the developer ribbon if you want to use VBA quite quite often it's not requirement because even if that is not there you can still use VBA but if you have developer ribbon then you can get into VBA quite quickly so if you don't see developer ribbon it's a really simple thing you can just uh right click anywhere on the ribbon customize the ribbon and once you're on this screen you want to check the developer option right here so that's the developer thing right there you want to enable that and click OK and that's going to add the developer ribbon it doesn't require any restart or anything you don't need admin rights you don't need to have special privileges or whatever you can just enable that and that will give you a ribbon through which you can get in and out of EBA and customize some of the other things quite easily it's not necessary like I said even if we don't have it you can still use VBA but I like to keep this on because it gives me other things so what is vbm VBA is a programming interface built into Microsoft Excel as well as other office software so you once you know how to use VBA in Excel potentially you can use VBA with Outlook you can use it with PowerPoint you can use it with word you can actually use it to connect one to another so for example you can write some VBA code in Excel that can connect to Outlook and do something there so what you but you press a button in Excel it can take a screen value that you're seeing in Excel and then compose an email with that in Outlook and send that email this is a the real powerful scenario of how VBA works in the Practical world and pretty much that is how I use VBA anytime I'm actually building something for somebody or for myself like take what is in Excel and then use it elsewhere in a more elegant manner what does the words VBA VBA stand for it stands for Visual Basic for applications I think so and as the name suggests it is a programming language that is derived from the programming language called basic now I don't know how many of you have actually done any coding or learning how to code back in school or college days but when I was learning how to code this is like many many years ago so my first programming language that I learned is basic language so basic is a computer programming language using which you can tell computers how what you want to do and how you want it to be done and computer will do it so you can ask computer to do things like you know go through each of these files and do me something there you can use basic programming language to do that you can think of the basic as a language same as python or Java or JavaScript or in any of the language like r or rust or whatever so Microsoft added that same basic language an enhanced version of that as a backend tool to all these office applications so you can use that to tell Excel how you want certain things to be done now back when this was added let's just say 2003 XL I don't know exactly when this is added but I remember personally using VBA since Excel 2003 even before probably Excel had quite a bit of capabilities but it also had a lot of limitations so having a programmable interface that I can use to customize what I can do with Excel and I can take the capabilities of Excel and enhance them two different levels was really game changing because that means there is no limit on what I can achieve with Excel I can build an Excel file but that can connect to a database and post values through the database and all of that so people who are building all sorts of crazy amazing and awesome applications with VBA fast forward to today 2023 when you are learning this it's not the same Excel anymore Excel itself has come really far there is a lot more capabilities built right into Excel a simple example of where I would have used VBA back in 2000s uh 2010 2015 and now is for example if I if I have got some values in cells so we'll say some thing here so three values nothing really fancy just three values are in the cells and I want to combine everything into one big text so I would like to see something here that's really what I want maybe I want to have some spaces in the middle so something like that this sort of a functionality is not so easy to do with older Excel because we didn't have any function that can easily take a bunch of values and combine them for example if these values are number so I have one two three I can use the sum function and combine these values like that we already know these kind of things but if I have got text values there was no function available in Excel at some point to combine them so the only function that we had was called concatenate yeah and it would take it wouldn't take three values so you see what happens when I try to use this right now it works I mean it doesn't work really because it's kind of using a spilled range to return the same thing again uh whereas the correct way of doing concatenation here is you would have to say D3 D4 D5 like this and then you'll get something here this is painful especially if I want to combine 50 values so I wrote a VBA function to concatenate values and create one big value out of that and and then install that VBA function into Excel so that every time I use Excel it would do this for me and I even wrote an article about this on my blog this is like many many years ago and for a really long time it used to get hundreds of people come to that article every day copy that code and use it in their work because there is no easy way to do this in Excel at that point in time but now now we have got a function in Excel called concat that can do exactly what that VBA macro was doing something here in fact you can you have another function called text join that can give take a delimiter so I can say use space on this and I'll get something here so you see what's happening the capabilities of excel have evolved which means there is a less need for us to use VBA for some of the more obvious things there is still a lot of scenarios where I would use VBA but most of the things for which I had learned how to use VBA are no longer relevant a more classic example is I used to write VBA code to combine things in different files but now I use power query for that okay enough introduction of all of that we're now gonna do something with VBA so you can understand exactly where this technology would fit in I'll add another sheet and let me quickly scan through the chat to make sure I'm not missing any important messages like we can't hear you kind of thing okay cool I think everything is good um there is a all right we don't need to worry about that there's some messages there but I'll come back to those messages I don't think anything um pressing is there um and so our first example is we would like to just quickly understand how to get into VBA write some code Etc one of the awesome things with XLE is like if you're learning most programming languages you wouldn't find this anywhere except VBA Maybe is you have a built-in recorder that can listen to what you want to do and then write the code for it I mean there's probably some parallel examples in other office applications like powerapps Etc have done this but there is a recorder built into it and this is the reason why I asked you to enable developer ribbon if you go to the developer ribbon you'll see that there is a uh record macro button here using which I can record a macro macro is a piece of VBA code that can listen to what you are doing and then it can like a parrot repeat your actions when you execute it again so we are going to use this to kind of get into the world of EBA um you can just add a blank sheet and be you can do some actions here while you are doing that you can record and you know you can repeat it I like to use this very simple example of a a really repetitive and extremely boring data job imagine you have to do this job every day like you're paid good money to go to office every day open an Excel file go to the cell B3 and fill color into the cell B3 this is all your job there is nothing else to your job all you have to do is every day open a file put B3 into yellow color after doing it for a couple of weeks you thought ah I don't want to do this I want to automate this so I can sit back and let Excel do this work for me so that's what we are going to do we want to write a macro that would fill a color into a cell that you have selected so we'll go to the developer ribbon click on record macro you can give it a name I'm going to call this as fill color and click ok right that's you can just you can put any name there it doesn't really matter and once you've done that you may want to click on the cell where you want to color this so I'll go uh to B3 I'll select that but let's just say you were somewhere else you you may want to bring the cursor back to B3 and then fill the color that's it your macro action is done you you've selected the cell you failed the color and now you can go back to the developer ribbon and stop the recording you can do it from here or you can also do it from the status bar very bottom there is a stop button here it used to be a lot more discoverable earlier but now it's kind of in single color so it's harder to see anyway you stop the recording a key idea to keep in mind when you're using the recorder is you want to do just the necessary actions you don't want to do anything unnecessary like save the file or scroll down or move to a different place right click somewhere none of the actions that you don't want you don't want to do that just the bare minimum so we're going to stop the recording now and now our macro is built it is doing that action that you have told it to do but how do I see this and how do I more importantly repeat this because tomorrow I come I don't want to record again and fill the color tomorrow I want to just hit the button and it should be doing so tomorrow's scenario is we go to a blank sheet so we'll imagine this is the tomorrow's workbook and here I want the color to appear in B3 so you can go to the developer and hit down macros it will show you the fill color macro that you have here you can select that and run and you can see that the color immediately appears here okay next day macros run the color appears again so this is in really simple words this is what VBA is you record something and then every time you want it to happen you just run it it runs you don't even need to know what is happening behind scenes for this to run as long as you took the precautions now let's go ahead and see the code so you can understand what's happening like you know what does this language look like how this all behaves so you can for example click on the macros and select the fill color and edit it alternatively from the developer ribbon you can click on Visual Basic whatever you do you will be able to get into a separate back screen application called Visual Basic editor this is called vbe or Visual Basic editor and if you expand the modules on the current workbook module one you will see your code for this this code I can read it all right on my screen but because we are streaming I'm just gonna make sure that the text is a bit zoomed up which will also give you an idea of how to change the editor so we're gonna muck around with that I'm hoping yeah so you may want to go into tools options editor format and and then the size is 14 points I'm gonna change this to 20 points and uh I want to try a different font Korea new is all right but it doesn't have a little bit of density to it so let's see if there is something else otherwise we're just gonna keep it like that we're gonna leave there right I don't know if we can make it bold anyhow hopefully this is readable if not let me know and I'll try some other font I also want to quickly give a shout out to Vino the for a Super Chat thank you so much vinod for that lovely gesture and let me see if there is any um so there is a question from Bilal I was going to get into this question I'm just gonna make sure I just give me a second here I want to thing otherwise I don't know what to do okay hopefully you can read the code all right and the question from Bilal says has it been on the same workbook the macro that you wrote what if I want to run this macro on a different file every day so we we have been running this color thing on the same file all this while but what if you need to color this but on a different file every day talk about this as well hopefully a little bit later not right now but for now we want to understand what is going on so this is the code anything uh in the single quote here it's a comment you can literally write anything here like you can say I allow my dog and VBA wouldn't really bother about it it's a comment so it doesn't pay any attention to that the word sub is an indicator that this is a contained piece of code so anything between sub and end sub is all one piece of code this reminds me sub subscribe to the channel I couldn't really help it so if you have no if you're new here you enjoy what I do and you're thinking oh this guy is good we like what he's doing feel free to subscribe to the channel I post a lot of content about Excel VBA in power bi and other data Technologies but more important is if you are watching this feel free to like this that really helps me in real time when we are doing the stream anyhow uh sub and then the name whatever name we have given when you're recording the macro so you set sub fill color and ends up so that's the piece of code sub and end sub is the starting point and ending point for that macro it's a older Computing word subroutine is the full word but you just say sub band that's what it is so the first step we are doing is we are selecting the cell B3 so we're saying range B3 dot select and then you're filling the color even though filling color is a single action VBA recorder kind of Records a lot of things so it says with the selection dot interior that means the internal portion of the cell you want the color to be a solid color not a pattern color and it should be team color accent too so even though it's an orange color that's not really how VBS is this color it sees this as in the Excel color theme so this this is the color theme that we are right now using I think these last six are called accent colors so this is one two three four five six so we are we have picked the second color so that's what it is so that's how it it really so it doesn't really use an RGB kind of a thing unless you specifically go to more colors and fill the specific color up until that time it's gonna just use the theme color so it's not technically Orange see what happens new page and I go to page layout and change my color theme from this to some other theme like let's go with this one right and then run the macro [Music] uh probably because of the stream but now the color has changed so it's no longer Orange it's violet color so that's the thing in fact that's not the only violet color all of these would have also changed it back to Violet this is because they never had orange to begin with they had the second color in the color theme so when you change the color theme they will all have their colors changed we're going to switch back to the office theme this way it will be consistent otherwise you know it looks kind of crazy everywhere and let's go back here and then it also has tint and shade and some other blah blah blah stuff whatever two so it's saying second color and First Step option of the color that's really what this is you can add other stuff in here if you want for example let's say your job is just got a little bit more interesting you got promoted so now your job is no longer just to fill the color with orange every day but also print the word awesome in that cell every day go to B3 cell change the color print the word awesome you might be tempted to record Another macro but you don't have to now that you're already here you can write some code so let's write the code that would put the value awesome into the cell B3 you can do it in two ways you can kind of do it like this range dot B3 dot value is equal to awesome so that's the extra line of code that you can write um here we are saying range of B3 but remember what is the first operation that we are doing we are selecting B3 and then filling the color so we have already selected so notice what the recorder did the Second Step it's not going and saying with range B3 dot interior it is saying with selection so whatever is selected do this operation on top of it so you can use this special thing called selection also to do this so you can say it like this you can alternatively I'm gonna leave it like this for now but feel free to test it out you can replace the word there with selection.value and then try that out as well so I'm Gonna Save uh how do I want to save this right now we'll save it later for now uh we are gonna test our new macro on a blank sheet so I'll add another blank sheet and every time going to the developer and clicking the macro and running is getting a bit painful so I wanted to have an option to trigger this macro by clicking a button but it's not that simple we will have to think a little bit more logically about it for example you can insert a shape here and then write the word do my job on this and then when I click on do my job this is the this is gonna run that macro behind and then fill the color that's the intention so you can right click on this guy assign a macro to it I'll show it in slow motion right click assign a macro this is the button you can put this assign macro on any kind of shapes images or icons that you're bringing into Excel and and that opens the same screen as earlier and you can select the fill color from here and click ok and now if I click on do my job this is going to be a painful thing to see every time I guess because we're live streaming and I don't know what else is going on in my computer hopefully it's not running a video game or something but it did the job it it printed the color and it wrote the word awesome in the cell B3 the problem with this button is if I add a new sheet and I want to do this now I must bring that button here again and do it it's kind of like useless isn't it because I might as well just go to the developer and click on Macros and just run it from there rather than trigger it from the button so the button approach is useful if I have got a macro in you know I want to just run it but for this kind of a thing where I need to run this on different places every time it's kind of helpless so this is where you need to have another technique something that doesn't sit in a page so that would be like having the button on the ribbon up here if I have got a button here in my ribbon that says do my job and I click on that you know I I can go to any sheet I'll have that button right there so that's what we are going to achieve next for this to happen let's first save this file this way you know we won't lose anything I'm Gonna Save this when the live stream is done I'll post a copy of this file in the video description so you can download it not that you know there is any tremendous piece of code in here or whatever but it's always helpful to have something referring to refer to but feel free to replicate all these steps on your own you know that's a good way to learn actually so we're gonna save this as live demo and this is the critical thing when you have VBA code you can't save the file as Excel SX anymore you need to choose a format that is enabling macro so there is an option called xlsm you can also use binary option but let's go with this and that's that's the important bit also when you have xlsm files when you reopen or you email this to colleague and they reopen Microsoft throws a boarding saying there's macros in this do you want enable and blah blah blah because VBA code can get a little bit crazy and sometimes do harmful operations on your computer like you know delete files and whatnot so it will show a warning to anybody when they open a file with VBA anyhow now that we saved I would like to add a button on my ribbon that will trigger this macro on whichever sheet I am so I'll go to my ribbon I'll customize this customize the ribbon right click on that and and from here where it says popular commands I'm gonna choose macros so from popular commands you want to go to macros and you're going to find all the macros that are available for you in in your Excel I have got a lot because I have added some add-ins and whatnot but here is our fill color macro so this is the little guy um you want to locate that and find this and then you want to add that using this button here to one of the ribbon so when you are adding you need to First pre-select the ribbon and an area of the ribbon where you would like to add this I don't think you can add things anywhere directly you may want to create a group or whatever it will show you a warning when when you can do it so we'll try this we'll select this and we are going to add this to um let's go with the font font areas right here yeah so it doesn't want to add there because it it's a built-in ribbon so what you want to do is you want to create a group first so you click on new group it will create a new group here you can rename this you can say my macros and that will be the group and you can add the fill color into that you can leave it as it is you can also rename it and you know kind of change the icon if you want let's go with something that looks a little bit fun yeah I think that's fine so that will be the thing and when you click OK you'll now have a button here in the ribbon that says fill color and and wherever I am I click on that that's gonna do that in this is really easy it keeps happening we'll just ignore that for a minute so we're gonna be seeing that for a while if anyone know why this is happening let me know I am trying to see if there is any programs that I can close I'll close the some of these things I don't want to close other things because they're all necessary for the streaming stuff and I definitely believe there is enough memory it's probably something funny going on somewhere anyhow there is also a super chat from Akshay thank you Akshay he also says love your videos I switch your job and got 2x hike thanks to your teachings uh that is really awesome brother uh congratulations awesome chocolate case studies help would be a lot thank you chandu you're welcome and more power to you all right um so so far so good now this particular fill color thing is not a great way of doing this this because that macro exists only in the live demo file if I go to another file if I open a new workbook now this workbook doesn't really have that code but the ribbon still shows feel color if I try to do it it will work because what is happening is that other file is also open right we have got the other file open on my computer so it can locate that macro see what happens you know I don't want to jinx in anything because sometimes these errors can actually crash Excel or whatnot but if I close the live demo file foreign now try to go here and fill color I'll get a um you know some sort of a weird warning message because what we are trying to do is run the macro in this file but that macro is not here it's somewhere else that is closed so it needs to open the file and you know it kind of creates a security hole in your system this is first time uh in a long while I'm seeing this but if I enable and then then it wants to open that file so now I have to enable macros here and at this point it has actually opened my live demo file for this to one work so this is the problem with this kind of a technique there is a better way to do this called personal macro workbook feel free to Google it up and find the instructions on how to do it I got articles on my website on how to set up personal macro add-in and then use that so that any macro you write is consistently available across all your files but for now we got the basics covered that is the first part of the video where I wanted to showcase the introduction to VBA I'm gonna close this guy I don't need to save that and then let's go into the second bit which is where we are going to create a simple to do application that will use this very technique that I have shown you to create a simple application that can kind of do the task for us um so this happened like somebody was following up and their laptop crashed sir my laptop just crashed oh my God so yeah sorry I didn't mean to get you into there brother uh and it shouldn't really crash because we're not doing anything like we're not playing with fire or whatever but many times when you're coding uh you need to be careful with the actions that you're doing because otherwise you think you're doing something but the computers they hear it in a different way so make sure that you are um that and I'll come back to this view because I would like to show some of the messages and kind of address them but I I didn't set it up all right and I'm just looking okay so that's what I wanted to show oh out here you stay here so Tyson asks can you include the use of chat GPT for VBA um I know there is a lot of Temptation to use like AI tools like chat GPT and whatnot but probably not for simple examples like filling color you don't want to ask chat GPT to teach you how to fill color it's color produce either more clumsy code or something unreliable or whatever definitely use it to get into more interesting Concepts but for this it's highly recommended that you try it out these are simple examples and learn on your own at least understand the basics then you can get into more uh chat DVD kind of a thing so hopefully that is kind of giving you some idea I might do if there is sufficient interest post a comment chat or you know um tell me in some other way that you would want to see a chat GPT plus VBA kind of a thing I'll make a video explaining how you can use it personally for me I don't use it that much simply because I know VBA enough that if I go and ask it and then come back and my total time to develop would be so much more than if I just start coding right away so that's pretty much my way of looking at this but I would use chat GPT for other things but anyway if you guys want to learn that put a comment in in you know I'll make a note and I'll prioritize that later on as per my plan um there's a lot more messages so next okay whatever this is how do you change the column it's automatically awesome is running into C3 you cannot contain awesomeness but on a more serious note what if we want to change the column width you know we can take this up as a little challenge we're gonna try this as part of the next exercise so that we will bring this as a feature into our to-do list application so let's keep that in mind um and and then Randy says pivot table in VBA with criteria um you can do it uh personally pivot tables or anything more com more simpler than working with simple ranges it gets a lot more complicated very quickly this is because even though for our eyes pivot table is just like a bunch of cells on the spreadsheet uh from a back end perspective everything is different for VBA so this is where it's helpful if we go back to the code View here what VBA user says I'm just gonna hide your comment so we can kind of look at the thing VBA uses a special type of programming called object oriented programming so everything is an objects like range B3 is an object of the type range and then you're doing operations on it you're setting the colors or setting the value so far as a pivot table is like a cell or a bunch of cells but actually pivot table is a pivot table object and within the pivot table you have different objects to hold different things so you have got a row row labels and values and calculations and whatnot totals and subtotals so this is where even a simple operation like changing the layout of pivot or adding something else or moving from one place to another might involve writing a a tremendous amount of code to achieve that so and the problem would be you won't be able to do that unless you have good clarity about the object model behind scenes that is like how various objects are linked up and what not again this all sounds technical but essentially um I'll put a blank canvas notepad here I'll do one of these crude drawings just so you can understand what it is so essentially you might think pivot table as one object okay I'm I'm probably wrong here when I'm explaining the structure because I don't remember what the internal architecture of pivot table object model is but it could be that internally they're represented as um labels that things go on rows or columns then you might have values as two objects belonging to pivot table and then there might be a separate sub object called totals um that kind of corresponds to how the totals are um and you know there might be another object for the slicers or the filters that connect to pivot table and many many other things so unless you have some idea of how these objects are all placed and Link it together you will not be able to [Music] get very far even simple thing like forget about pivot table if I am doing uh something like changing the color of a cell for us in our mind we have got a cell B3 and it has a color this is how we are seeing it on the output level but internally you have seen the VBA code the color doesn't really belong to the range it belongs to an object called interior so you can't really set the color on B3 you have to set the color on b3's interior and then the color goes there so you wouldn't really know this concept unless you are familiar with the object model or the recorder is properly recording and showing you and many times what the VBA recorder does is it doesn't record everything that you are doing on the screen because it has it was not built to do that or those new objects were not available when the coding engine was initially built or whatever so for these reasons it's kind of trickier to directly jump into more even though they sound simple they might get more complicated and this is also the reason why I kind of discourage people who are starting to get into chat GPT kind of things because while chat GPT or AI assistants can produce the code um they might give you a solution that is a little bit more over engineered or for it or it would require sufficient knowledge of the underlying model for you to actually customize and change or work with later on again no nothing against those things but if you are going down that path you may want to learn a little bit more or you may want to consider the Alternatives like you know is it worth the effort to write 300 lines of VBA code just to change the pivot table or do it manually and you know just suck it up as part of the workflow so this is where the trade-offs come in again um we'll go there in a minute but for now let's go back and Define the agenda for our next part which is to create a simple um to-do list application so here the idea is we want to build a to-do list it's it's really simple I'll keep it as simple as possible but we will add layers of complexity as time permits so in awesome chocolates everybody is assigned a bunch of activities and as part of the activities that you are supposed to do every time you finish an activity you're supposed to say done against that activity and also record the date and time when you finish that activity so this is the job so you can have like a to-do item and then status timestamp three columns literally okay and then that's the heading so we'll put that bold underline this and this is where my actual stuff will go okay I'm not even using tables for this even though you can use tables for now let's not use tables because again in your eyes you might think table is a list of cells but VBA sees that as a separate object called list object and we'll get bogged down into the murky details of what is a list object how to access things in in all of that it can get crazy very quickly so we'll use a bunch of cells because this is more perceivable in our eyes so here um We Are Gonna Fill some activities I don't want to do anything crazy so we'll just say Activity one and then I'm just gonna drag this down so we'll get 21 activities okay and as you finish an activity you want to say done and then fill up the timestamp here so 603 am something like that not exactly 603 because this would not be 603 of today it will be 603 of 1st January 1900 or whatever so you will want this to be 24th September 603 am kind of thing um so that's really what it is but you don't want to type these things manually what you would want to do is initially you want to pick a cell and when you run the macro like a macro would be there that is called Mark as done it needs to for whatever cell you selected it needs to say done and then in the adjacent cell it needs to print the timestamp this is the initial piece of code later on we may want to customize this a bit more so that's the intention there here again we don't want to use the recorder we'll want to write this code ourselves that way we can see the flow of the logic and figure out what errors can happen and whatnot and when I run the code I want to also make sure that it doesn't do anything garbagey like for example if I select this and run the code I don't want to overwrite this cell with it done I would want the activity to be marked done only if I selected the status column and then later we'll debate whether that is user friendly or if there is a better way to handle this but for now the macro should only run when you are on this column but if you are somewhere else and try to run it it shouldn't even run it shouldn't even bother you so how do we do this for for the sake of Simplicity what we are going to do is we will keep it simple but later on we'll try to add the complexity so we'll make a note that column C is where the status thing is and you know this is this is what it is so we are going to go to the developer thing and this is module one you can add different modules if you want module is where a chunk of code can sit you can write multiple pieces of code in the same module I'll add a new module this way you'll know how to add things as well so you can add a module when you right click on the module you'll see insert and you can add a module user form or a class module these are different kinds of things that you can introduce into VBA for now we'll stick to module later on you can figure out how to use the other tool so in this module we are going to say we would like to write a sub so sub and then we'll want to give this a name so we'll say this as Mark as done a key thing to remember is you can't really have spaces like this that kind of a naming is not allowed so this is where most coding you would see this kind of a camel case kind of a thing Mark has done other people might use Mark as done I'm not a huge fan of underscoring in my variables and things like that it takes too many keystrokes so I like to use the camel case and you don't need to open bracket as well you can just hit enter and VBA will write the necessary end sub capitalize things for you I don't know whether you can make it out in the live but that sub is in a different color of blue they're kind of very subtle I'll try to tweak this and then see if that keyword text okay let's just make this something ridiculous like that you can see now sub is in a red color and Mark has done is in black color and it might not be a bad idea to add a comment at the beginning to explain what your intentions are this way you know what you're trying to do again a good practice not necessary but feel free to do what you want uh check if you are all column C then print done in the selection that's pretty much it so for now we'll ignore the check part we'll just assume we are already there and then do this and then later on implement the check so you can use either selection selection means selected thing but many times what happens is if you are in Excel you can select a cell so right now I'm selecting a cell you can also select other kinds of things so for example right now I am selecting an object a box object I'm still selecting but I am selecting an object whereas we would like to do things only when I am selecting a cell not the other kinds of things so because of this the selection object is a little bit dicey one to work with it will if you try to say selection.value it it has only meaning in the context of a cell but if I select something else it doesn't have a meaning so this is why um you may want to try some other options there is another object called active cell this is the cell that is currently active where your cursor if you try to edit would be edited so the active cell is okay not the intention there so if I select a big bunch of cells my selection will hold all of this range but active cell is the top corner you can see that that's kind of highlighted in a different color so that's the active cell is usually the top left of your selection again quite quite helpful if you want to do things like that so you may want to try these the different things so we want to say active cell dot value is done here the assumption is we are already on column C you are there when you initiated this macro so we'll just assume you have done your part and now we are going to print the word done there so here uh we will put that and then we can run the macro in different ways but uh you know you could also put a button I'll show you a third way we have seen from developer macros you have seen right click assign macro but the Third Way is you can also switch to the Visual Basic editor this is really helpful when you are trying to test things out so normally this is how I would develop things and test things I'll keep my excel in Focus I'll keep the VBA both windows tile them on the screen and that way I can select a cell so right now I'm selecting this and I can go to my code so you select you go to the code and then while you are looking at a sub place the cursor inside and then you can run the code you can hit that run button you can also press F5 here to execute the code so this is another way to run the macro this way you don't have to build like hundreds of buttons especially helpful when you have got lots of macros and you're trying to test out internal parts or whatever you can use this you know no if you have done programming in other types of software you're probably familiar with these kind of Ides where you can run pieces of code and add breakpoints and test things out step through the code and all of that I'm not going to get into those kind of technical Concepts that are more neutral and relevant for any programming you do um so that's another way of running um and so this works if I select a cell and I run I'm getting done there which is good but if I select excel in timestamp and then say done I'll get done there if I go to an activity and say done I lose my activity value there if I go to a blank seller and then say done I'll get the word done there so this is this macro is only useful as long as the preconditions are satisfied right another headache with Visual Basic key is you can't undo the action so I've made some mistakes I've filled done in the places where it shouldn't be and then I realized by mistake I'm like oh I want to undo if I try to press Ctrl Z nothing happens because any action you are doing in Visual Basic through the code is not going to be added to the undo stack so Excel doesn't really know that this is happening it's kind of happening through back door so there is no way we can undo these actions this is also why you should be extremely careful when you are trying to implement VBA in in any of your workbooks you have to probably take backups or get into good practice of writing more bulletproof code all right let me just quickly glance through the chat to see what is going on uh uh yeah I don't think there's anything going on and some lots of interesting questions but uh we are good to go feel free to put any questions [Music] um yeah someone says don't even pay attention to questions just go on I wish I could do that but the whole point of doing a live stream instead of me recording an hour-long video and Publishing is so I can interact with you I can help you out I can kind of play around with my ideas and bounce things with you anyhow we got that but it doesn't work if I pick something else so it would be a good idea to implement that check now so let's go that instead of doing this I would like to First make sure that we are on column C again there are different ways to do this I will kind of try this out for example before checking I would like to at least see what column you're on and then figure out that information so we can for example say active cell Dot and this is a great way to kind of self learn the language you can select any object type things and then press dot it will show you all the methods and properties available method is a a macro or a function that you can call on the object and a property is it's kind of its thing range dot value is a property for it and you can see that there is a thing called address so it will tell you probably the cell address what we really want to know is what is the column of this so if I type column I'll get that I don't really know what this is going to return so one way to see what this would be is you can use debug dot print right so dvac dot print activesell.com print and the reason why I don't like red color is normally red color is reserved for code errors this is by I see red I'm like oh probably there is something wrong with that but it's not really wrong it that's the color that we went for keywords so that you can actually make it out so I'm gonna comment out this portion all it this will do is when you run this macro it will print the active cell column somewhere uh in in there it where it's gonna print is it's going to print in the immediate window underneath this is where all the messages and alerts can be printed so let's go back here and uh and let's select this and then run so you can see here it says three right if I go to the fourth column it'll it's gonna print four you can see three was the original now right now we are on column D so for if I'm going here and then running that you'll get one so active cell dot column is going to tell me what column we are on all we want to make sure is that we are on column three before we can set this to done so this is where if condition can be helpful if if active cell dot column is 3 then active cell value is done and if so this is how you would write an if condition you'll say this then and then anything you would put in the middle that will only happen if you are on column three that's the check that we are doing um so at this point I'm gonna edit the text and then say Mark as done so this is the macro we are gonna and then assign the macro to so we don't have to switch back and forth so now if I select timestamp let's select this first and then Mark as done we'll get done there but if I select this nothing happens because you are not on column three so it cannot damage these values it can still damage outside it can damage the header like the word is now status is gone and it's done so those things it is still doing what as long as you're on column three you're good to go okay so that's what the code is a good part of any any development is always trying out different things and testing to make sure that only the intended thing is happening and nothing else so our base condition check if you're on column C is not sufficient if you want to be careful you do you would want to limit this to the size of the to-do list so the cells where there is an activity otherwise you don't want to do anything but that can get a little bit more technical I'll leave that to you for homework instead what you want to do is when it is done you would want to add a timestamp okay so that's the next activity that we want to do so we'll need to calculate the time value this is uh we're going to try out a few things so and add time in column 4. so that's the purpose of this code law what we can do is so we'll mark it as done and then we're gonna declare a variable you can declare a variable with the word dim dim dim uh time now and then time now is yeah another way I oh God I'm now is now another way I test out my VBA code is again I I don't do as much VBA coding now then back even five six years ago so sometimes I go into the flow of coding and I'm doing it for a month or two then I'll remember everything back but if I'm not doing it like probably this is the one of the first few times I've done coding this year in VBA I don't remember exactly what it is called so I'll type something and then I'll go to the next line and if I see that capitalize it like you know if I'm typing no so normally I type in lowercase and then if I go to the next line and I see that n is now capitalized that is an indicator to me that I probably got that word right I was trying to access them now but if you don't know whether this is the correct one or not you can also use the immediate window to test things out now this doesn't want to okay talk on this side here so you can also say question mark now and that's gonna print that now result right here so immediate window is a great place to test out little pieces of code to make sure what it is like for example if you didn't know what actorsell.com column would do you can say active cell dot column but into the number four so we didn't have to actually test it out like this we could have done it like that as well but this is really what we want we want that to be printed in the cell so time now is now so we're creating a variable we are storing the value of now into that and then since we are on column three in the next column we should have that value go so here in the next column how do you go to the next column you can use active cell dot offset and you can specify how many rows and columns you want to offset so from active cell I want to go to the next column so I want to stay in the same row next column 0 1 you don't have to really say 0 you can ex directly type comma and one so you might see this kind of code quite often when you go for help or ask chat GPT to write the code for you please we'll have to test this out and make sure that it works correctly but for now that's the thing and save this come back and our custom we did get something here that's the 24th of September 6 21 remember I live in New Zealand so I'm a little bit ahead in time compared to where most of you are watching this live but if I go here and try to do it or if I go there and try to do that that wouldn't really do anything so I'll get this and I can see 38 and full full time here clearly if I want I can change the formatting of that to get the actual Precision in seconds and whatnot any questions so far I think this is a not the greatest piece of VBA code but it kind of introduces you to a couple of interesting Concepts like the if Branch conditions declaring variables and you know offsetting and storing things so I would want to claim that as a win and then make sure that I'm catching up with any questions so there's a lot of interesting discussions happening for example Attila says in order to avoid the header being not overwritten you can also double check with active cell value not equal to status so that's a good idea but see what happens with that check if I am out here that doesn't have status so I'll get done there we don't want that I either we would like to ideally limit this operation to this bunch here nothing else so that might be a better way of thinking about the problem okay um again if you have got any any other questions or comments at this point feel free to put that but I think we are pretty good with what is covered so far so now let's think of some enhancements so that it can be a bit more friendly for example what if I already finish an activity right and then I click on Mark as done now ideally because I've already done this I don't want to Market as doing it again and overwrite my time stamp but in this case if I am already done and then say done I lose the 621 and I'm now up to 624. this kind of thing is either a feature or a bug as developers like to call it so you decide for example if you think ah this should not be happening if I'm already done something I don't want to redo it again and change my timestamp because that's incorrect alternatively you might say I know what I'm doing I have selected the cell done and then I'm marking it as done that means I've made some amendment to my my to do item I probably haven't finished it and now I am doing it so I would like to change the time steps so depending on which way you view this you can change the code for example let's argue that if I have already done something I shouldn't be redoing that activity and I shouldn't mark it again as done so I don't want to overwrite this I wanted to kind of maybe stop the code there so let's do that let's go ahead and add more checks here so if you are on column three we need to do this but before we do anything like whether you have done it or not you can set the value to done again that's not really going to change anything in my opinion but what we would like to do is we would not want to do the timestamps thing if if you have already done it so this is where before setting the value it might be a good idea to kind of see what is already there in the cell and then do the operation so here um I'll declare another variable you can do it in any number of ways you can write even smarter shorter code but when you are explaining somebody it's always a good idea to write a little bit longer code so you can see all the logic so I'll declare a variable dim car value you'll see these kind of variables in some of my code that I have published online as well I like to have car value pre-value like that current value of the cell and you know we don't have to declare the variable or declare its type as well VBA is a little bit friendly if I don't say anything like I don't have a variable called dim car value and then I say car value is equal to active cell value it's gonna create that variable for me but as a good practice I like to declare the variables curve value and then use them if you want to get a little bit more precise you can also write the word option explicit up top what this ensures is it it ensures that any code that you write here all the variables are declared before you can use them so see what happens with option explicit if I don't declare this variable now and I try to use it when I try to run I'll get a compile error so this is an error that is happening before even the code can run and then it says variable not defined so this is a good good way to check so you can write option explicit and you can kind of then it will check all your code before it can do any damage on your workbook and then when it is in the debug mode you'll see these kind of yellow highlights and you know a a marker where the code has found an error and it would have already highlighted that bit there for you and at this point you can reset The Code by using the stop button to reset and you know fix the problems but if I don't have option explicit I'll comment that out then if I try to run I'll I'll not get any error the code will run and it will do what it needs to do so for example we have selected that it will run it didn't have any problem with the car values item so we'll use option explicit and then we'll declare dim car value and then when you're declaring a variable you can leave it as it is or you can also specify the type of it you can say as in the data type like string or you know all these data types are available sometimes you don't know what's going to come so you can leave it as it is or you can also use a variant data type which says it can have anything in it you don't have to do anything you just leave it VBA is lot friendlier that way it will adjust the code as per whatever values come into the thing so we'll say car value is my active cell value and then we are gonna do all of this guy if it's not done right so in fact we don't even have to do all of this we could kind of enhance this condition to do an end operation so that says you're on column three and you don't already have done then only we should be doing this but I'm kind of elaborating this code a bit more so you can kind of see a few other things so we'll say car value is active cell dot value in fact I'm gonna cut this and put that there so we'll Begin The Code by declaring a variable and holding the current value there and then in here we'll write one more if condition if curve value is not equal to done then operation I don't know I want to do it like this probably okay yeah so again this is what happens if you don't code with VBA every day you forget what these operations are because some programming languages probably python uses exclamation equal to whereas this uh I thought it's not equal to but anyhow um then we'll do it all of these things right but else that means it's already done right a case in this case what I want to do is initially we can later later on add another feature I want to show a message that's saying you know you have already done this hence we are not changing the timestamp so you can show a message using the MSG box method you can say message box you've already finished this dummy and and then you can just say VB information plus VB okay only like that so what this would do is if you have already done something it's going to show a message boxing you already finished this let's test this out so we'll select a blank we'll get done we'll select something that is already done we'll get a message that says you already finished this dummy with an information icon and OK button and you know whatever you put in the title area that goes there so this is um kind of like a little bit more helpful in a way uh I mean obviously you don't have to be offensive like this but here I am the developer and I'm the user so I can call myself whatever I want but if you are building this for somebody outside or in your organization you may want to craft these messages in a more approachable manner but um so that's that you may want to take this to the next level and then say instead of showing a message like when I select this and done you've already finished this dummy I want to give a choice like you have already finished this do you want to overwrite the timestamp and then it will have an option OK and cancel and if I say okay I'll take overwrite the timestamp you can also have S no cancel kind of a thing you'll see these kind of things in computers all the time right you've done something Windows or Mac will show a warning when you say yes it will do something no it'll do something else so you can also do that kind of a thing where instead of okay you can give them a choice take the choice information and then Branch your code further out based on that feel free to again take that up as a small Challenge and figure out the code take the help of your trusty chat GPT or whatever AI tool that you would like to use or figure it out on your or I think that's more fun you'll learn a little bit more and you know you'll you might amuse yourself um so that is the second part ideally the third thing that I would love to do and this is something that I am now guessing is probably not worth the time it will take not worth as in it's definitely worth for me to keep talking for another hour but you know we already been going on for 90 minutes so I just want to mind make sure that we don't go overboard so the third aspect that I wanted to cover is once this is built many times you keep doing and you keep adding new activities so anything that is done I would like to take out those activities either delete them or archive them to a separate page so they're all done we don't need to see them anymore we can just take them out so that was the third aspect that I wanted to introduce I want to plant the idea and I'm hoping that hopefully you can with some knowledge of VBA go and implement it what I will show you is wherever it is done Maybe you know kind of take that scan through the list and if an activity is done then kind of change the font color to let's just go with that something like you know make it dull so it doesn't get the attention so any activity done it should be having a different color but we don't want to manually color it we would want to do this through VBA at this point I would even challenge you that you don't need to use VBA for this you can apply conditional formatting as well so that you're not writing the code Excel will do this this is what I mean by Excel has features that will make the idea of using VBA for everything irrelevant you can use the other features and not write the code so there is a there is a simple idea that I live my life by which is I want to be as lazy as possible so if I can get if I can do one thing instead of two I'll do one if I can do 0 things instead of one I'll do that so this is the whole thing you know you don't have to write VBA you can select this you can write a new rule that simply checks is dollar C4 done if so format my font in this color and that thing is automatically done and as I as I do something it will automatically change the color you can see that but this is uh probably not what you want you want you may want to learn how to do a little bit of VBA coding so this is where the thing that I I'll show you quickly how to scan through a list and perform an action for everything it's called looping it's a very good programming concept as well so we're gonna [Music] clear the rules and any activity done we would like to change the color that's the last activity that I want to show meanwhile if there are any questions or any thoughts or comments or um things like that I want to also address this kind of a thing Joshua says python is equal to more powerful Ms Excel ideal for small data presentations come on man this is not at all useful I I think this is the mindset where uh did it's not really helping yourself forget about organizations or anything I don't think this is true at all I I view I mean I would have viewed Excel as a less powerful option many years ago but given what it can achieve and How Deeply it is integrated with rest of the workflows not I am not not I what I do in my computer but when I go to clients place and consult or teach people or show I feel that Excel can solve almost everything that you are trying to solve with other things probably not some of the more complex machine learning or stuff like that but that is anyway a known limitation but in all other cases you can use Excel just as well you just have to know where the things are at this point you might argue I don't know Excel well but I know python well that is all good so a good argument to make is if you are comfortable with something you can say I know this I'm using it but don't draw a box around your mind saying that I will only use Python for more complex and simple things going to excel that is limiting yourself you can test the limits of excel you can test the limits of python as well just as that you can also use Python for doing smaller data things or large data things you can make things slow in Python fast in Python anywhere so don't think like this because this is not going to be a helpful mindset a good mindset in my opinion is think of these as different Tools in your toolbox so you have got Excel as a tool you have got python as a tool you just ask yourself what is it that I can use today either to have fun or learn new things or do my job so then it opens more possibilities if you're always saying I'll use this hammer or I'll use screwdriver then you know it kind of limits you I feel like that I've seen people get into this kind of some sort of a very loyal Behavior towards programming languages or things that's not necessary we we can use all of them as we want anyhow um with Indra says the online vbr difference is way too technically written in hair heavy to read it isn't very helpful to quickly unless you already know and need a refreshing in my opinion yeah so probably that brings to the concept of how do you learn good way to learn VBA is just start using the recorder the recorder kind of gives you some interesting pieces of code that will give you an idea of how the object model is I've I'm pretty much self-taught when it comes to VBA I've learned most of what I did by just experimenting and testing it out and reading through forums nowadays you can bounce your ideas off with chat GPT as well so you can say here is a piece of code it's doing this but I would like to add this extra thing what you what would you do and then you can ask follow questions like why does this work what does this object do and it will explain things to you so try that out another thing that I also from time to time do is maybe read a book so VBA power programming is the title of the book there is different versions for different versions of excel so get the power programming VBA book and test it out or get any other book you know VBA has been around for 25 years or so more than that I guess so it's a very mature technology there is a lot of help books resources available and it hasn't really changed it that much in the last 10 15 years the Visual Basic editor is exactly like this in Excel 2007 maybe one or two things are missing but looks exactly same so you can read a VBA book in Excel 2007 and you will still be able to do pretty much everything in Excel 365. only thing that you might miss out is the newer objects like Excel 365 would probably have a different pivot table object model and a table list object model and all of that then the older one so getting a newer book is a good idea but yeah get a book because that will give you better explanation of objects and how they work and all of that so John says can you write the done to a separate sheet and remove from the working list that was my idea but I'm oh I don't want to get into that because it can get a little bit more clumsy when you are dealing with two sheets because you need to scan here and then go and put that there and keep coming back and forth which will involve more coding but I have got examples on my channel that kind of do more or less similar thing with other situations so feel free to search VBA on my channel you'll find some other previously published videos that take the concept and cover it in a more detail in more different ways so try that but anyhow I'll show you how to Loop the key idea is to Loop through things so once you know how to Loop you just have to add write instructions to cut and paste and all of that okay so like I said we would like to scan through this and anything that is done we would like to change the color that dull color while it is all right it's kind of not visible on the screen so what I thought would be funny is Maybe um use a strong color and bold this anything that is done it will pop out so you can see it the problem with this kind of thing is I don't really know how to apply bold effect or change the color of text fortunately I don't have to know that I can just select something and then record a macro so record this I don't have to name this and then once I select it I just want to see what would be the code to change this to purple color and apply the Bold effect and maybe while we are at it you know even give it a background color so this is what I want to do for my done activity so once I have done this I record that I'll stop my recorder then I'll go to my VBA code and you know look in the module three or wherever it adds so it says this is the code to do that little bit you can ignore the last one because we selected some cells at the end with the selection not formed change this font dot bold is true so make it bold and change the interior color to that so that's the tinting shade that it is using to bring down the orange color in the cell so this is the code that I want to apply for all my done activities so I'll go here and then uh Mark has done is one macro we'll write one more we'll call this as sub highlight done I'm writing it as a separate one instead of part of this because you can Mark things as done on on their own and then when you run this macro it's going to highlight all the done activities for you to visually scan them so in this the intention is we'll go through the activity list and then for each activity if it is done then we will apply that kind of a formatting for all of that so here this is a good place for you to start setting some boundaries for the code so that it will only do this and not more Etc that way it's helpful if you have if you want to scan the entire column B There is a million cells there and it will just be wasteful so you would want to scan this for the range B4 to B24 alternatively you can also go from B4 until the last cell whatever you want so normally you don't know how big this is you know that it starts on B4 and it could be B24 now but tomorrow there might be 25 activities so you may want to stop further down so we know the start cell is B4 so we'll say uh dim tart cell as range yeah start cell range B4 now you might think this is a good piece of code but here when you are saying range you are not specifying which worksheet it is so this is the reason why our fill color thing works because all we are saying is range B3 without specifying the worksheet number so that it will work on any of the sheets in fact it works on the active sheet whatever sheet is currently active that is the B3 cell of that sheet is being picked up but when you are writing this kind of a thing it might be a good idea to over specify that is specify the um sheet name as well so you can you want to say sheet9.b4 but then with the problem with that is if I rename sheet9 to something else then my VBA code needs to be updated so everything comes with a little bit of cost so you need to think about that we'll leave it at this I just want to plant that idea in your mind and then what we want to do is we want to capture all the cells that are going from B4 until there is a blank cell um we can declare another variable called um I'm just being a bit lazy with these names usually whatever names you're using you may want to give them good names so they they don't look silly later on there was a news story the other day you know about somebody in Australia or maybe it's New Zealand I don't know um the name when you have a child and you're in hospital you have to give the child a name there are some keywords or phrases that you can't use as the name apparently I didn't even know this but the news new story goes like this so a journalist wanted to test out this so she uh I don't know whether she had the baby or she persuaded somebody who is having a baby to test this out but they wrote the name as a methamphetamine rules drug that rules so that was the name they wrote it on the baby's name thing and it went into the department of internal affairs apparently anything that is like that keywords they they they'll just deny the name and they'll assign officially one name randomly pick a name so she wanted to see what that random name would be but this fell through the cracks nobody checked it and the kid's name is now methamphetamine rules uh same thing with your variables you may want to name them something that you want to later find find the meaningless or clumsy a classic mistake that many people will do is declare variables like a or n or X or something you know this is really confusing later down because you won't know what x means when you're writing big piece of code so my range is a range and what my range should be is whatever is the range of values that go from B4 in fact we didn't even need to have start cell we could have done everything in one go but we'll say set my range is and then this needs to be a range a range can be either a single cell or it can have two and then it will capture both so it needs to go from start cell and then go to start cell dot end Excel down so that would be essentially saying same thing as I'm here and if I press Ctrl shift down I'm selecting so that down arrow option is the end option so control down arrow when you press it goes all the way here so if I am asking somebody pick this all these cells this is what I would sell say go here and then go to the very last cell that has the value so you're picking all the cells so this is what that would pick up hopefully so we got that and then now what we want to do is for each cell in that we want to check if your highlight if you're done then we want to apply that color so we'll say a cell as a variable and then write for each cell in my range next cell so for each cell in the my range which would be all the activities we just want to check if you are neighbor says done then you need to be doing this so if Cell offset comma 1 value is done then that means that activity is done if so I wanted to highlight the color and whatnot so the color code is here so I'm just gonna copy this and uh I'm just gonna adjust all the indentation so okay so now if you see this code this only works for selection we don't want to do it on selection we want to kind of go from cell so instead of selection dot font will say cell range cell and then cell offset 2. so that means the code will go it say here this is done so cell is here so then it from here it goes two cells that's what that range would capture um then and then replace the all the selections with that you may want to declare that as a variable like current row and then use that variable as well but it's not necessary so that's the piece of code that would check for each cell if the neighbor is done then apply the font coloring and bold effect and then continue that kind of a looping for the next cell it looks a lot complicated mainly because this internal portion is so verbose even just to apply Color and bold and background color three things but there is like 10 lines of code but you know that's that's really how VBA is anyway the important bit is building up a range that captures all your cells and then using the for each Loop to Loop through each of the values so again if you have done some coding in other languages you may have already been familiar with the far kind of concept but if you've never done any coding what far does is it will take a collection like my range that has all the cells and for each Cell It's Gonna Loop through and and do the operation so let's test this out um a good idea to see both of them and then while we are here I'm gonna undo this action and and then let's test it boom that worked yay I think we should just celebrate and quit while we are ahead a common problem when you're writing any loopsies you might get into crazy things called infinite Loops again you'll hear this when you learn programming is when a loop keeps on running because it doesn't know when to quit normally you wouldn't even have that problem for a for each kind of a thing because you're running it against a collection which has a finite set of objects in most cases but if you are writing a condition or Loop where you are testing for some sort of a condition and that condition is never true then it keep on running like for example you say for and then you specify 1 to 100 but internally you you don't really keep changing the value back to one so it never gets to 100 then it never goes there so that is something that you need to be mindful when when it goes into Infinite Loop VBA keeps running until your computer freezes and then at that point you'll get a kind of Frozen error or crashing Excel or whatever but yeah instead of doing the highlights you may want to think I don't want to highlight I want to take that row copy it Ctrl C go to another sheet and paste it and then well we do and then repeat that process this is the archival process this is what I was hoping to demonstrate but I'll leave that to you as homework so yeah that's uh kind of in a nutshell how to use VBA I hope this kind of gave you the initial confidence and put you over that hump many people face when they want to get into VBA but don't know where to begin or what it is or how it applies I'll talk a little bit more about how it Compares with python in Excel right now probably some of you already know this but Excel now also has built-in python support so you can see this here on my computer I can write python code in Excel right likewise you can also write office scripts in Excel so how these compare with VBA and you know when I would use each of them and when I would use none of them and other features of excel so I did talk a little bit in the beginning I'll talk more about it later once I finished scanning through questions in the comments but uh do you enjoy it do you have fun I had lots of fun I was kind of skeptical because anytime you're showing coding there is all sorts of things that can go wrong like we did have out of memory error that was something that I didn't see many times before um but I'm glad everything went well and we we have achieved everything that I wanted to achieve so cool let me um vikrant says can you apply filter and add blank row on every row below done you can you can add whatever you want you just have to record doing some of these actions to figure out the individual pieces of code and then later uh you know do it I would I would also ask what is the intention if intention of filtering and then adding blank row and should those operations be even done in that order or maybe there is a different way of doing this but yeah can you declare module level constants which are accessible across different subroutines obviously you can declare a variable above here like above all the subs and then that will be accessible at all of them in fact there is so much more to VBA than I am showing I mean obviously there's so much more but we haven't really touched down many many things like you can add events things that will happen only when certain thing is done so for example every time you click on a cell you can run a macro so the macro wouldn't run until you click on the cell or you double click on things like that so that's a cool way to enhance the functionality for example a simple obvious fix for some of the usability issues is if I double click on a cell I don't want to type anything I just want to automatically mark it as done so double click should be done you can easily add that kind of a thing to your code and you know just make sure that the double click only checks on column C and not on other columns that's another great way to enhance the thing asks can I use chat GPT instead of learning excel in power bi yeah um I mean you can try if I'm imagine for a minute there I'm not trying to be a friend and I don't have to be I'll be honest here I'll say um while that there is a lot of Temptation to use these tools so that you can substitute the hard work uh we are not there yet so don't try to take the shortcuts do the hard work it doesn't take that long to learn these Technologies and once you know then you can use the tools better okay yeah that's it really I mean you can spend hours and hours on chat GPT or other tools and you might still be going in circles so it's better to invest sometime learn I'm not saying invest money or invest buy courses or anything that's not necessary uh eight times out of ten but yeah put in some time put in some hard work do it yourself then you'll know how to use the chart GPT even better um so this person asks will there be other lessons in VBA um I would love to do another live stream sometime down the line I do have some dedicated videos on VBA on the channel I also Run avba course if you want to learn more about VBA feel free to check that out and sign up and yeah if there is sufficient demand I would love to do more VBA videos as well yeah as you can clearly see I enjoy VBA coding I like talking so I might do some if you like this feel free to like this video comment let me know tell your friends to watch this that way I know that people are watching this then I might make more of this okay so let's uh bring this to the next thing that I thought would be highly relevant given the number of things that you have already in your disposal at your disposal and then the number of things that Excel keeps piling on top where does VBA fit in and why or how relevant is VBA in the landscape of things that we got today so this is where personally my usage of VBA overtime has been coming down this is a the reasons for this are twofold number one I'm doing less and less work not because I don't like doing work it's just that the nature of my work has changed I used to work as a business analyst I sometimes now work as a data analyst or data lead or whatever based on the work that I get as a contract but more and more nowadays I am into teaching and you know working with on YouTube and all of that so because of the nature of my work has changed there is less need for me to code the second obvious reason is and this is probably even more relevant when I'm working with Excel either with the client or when I'm teaching I tend to use the features that are natively available that means less code less maintenance less hassle so going back to my core principle which is I want to be lazy I can see that VBA gives me that chance to be lazy I can hit a button I can sit back and let the code do its thing but if I don't even have to hit the button and the thing is already happening then even better right we don't have to do anything so this is where features like conditional formatting would have been a great fit instead of us doing the highlighting because it can highlight the things for us automatically like here I could have written a rule to do that instead of macro and it will work so this is where uh the amount the number of reasons for which I need to write VBA code keeps coming down over my lifetime but going back now given the reasons to code what would you pick whether you go with VBA python office scripts or something else so here is the latest thinking for me this is the thinking as of 2023 September this will always change as the landscape evolves and new technologies come in right now any kind of data automation that is if I want to take data from different places combine it mash it you know take this file in that file and merge them together this kind of things I used to do with either complex formulas or VBA back in 2010 now I use power query for all of those things power query is built into Excel it works great it works within Excel as well as power bi so it's kind of learn once and use it many places amazing so data automations I use power query you can go to data and then see this I got plenty of videos on the channel my Excel school and power bi course online courses cover in depth on power query as well so I teach this I encourage people to use power query for data related stuff then the second thing any kind of uh looping kind of things like go through a list of items and do certain actions I normally evaluate the reasoning for that like for example here the highlighting is a cosmetic thing and I'll try to avoid writing code and use features like conditional formatting or whatever but if it has to be a little bit more elaborate then I'll try to use the formula approach as much as possible for example if I want to go through a list of items in Excel and then do something on them earlier there were not many choices we would have to either go to VBA or do something but now with the Lambda functions in Excel you can scan a list and then do things on it you can map a list so you can take something and then apply some activity on top of it another formula operation or you can reduce it which will take a list and shrink it but you don't even have to use them you can use other things like um you know data operations can be solid with v-stack or filtering kind of operations can be solved with Filter removing duplicates can be solved with unique so all of these are new functions that are available in Excel now which were not there just a couple of years ago so this means I don't have to think about VB as an option because I got all of these here but let's say we come to a problem that cannot be solved with what Excel has to offer then what are my choices my choices depend on where I need it if I'm doing it on my computer for myself I don't mind writing a bit of VBA code because for me coding is not just to solve problems it is also uh you know like doing a crossword I like doing things just for the sake of doing things so I like to write code for fun this is the reason why yesterday when I um when I wanted to kind of have something fun to test my live stream I created this because I wanted to see how to show a message box but with an image of my live stream thumbnail and a button and a custom format title uh initially it didn't occur to me and then I thought oh I'm a dummy I know you can make a user form so I made a user form and used that to create that so you know for me coding is a little bit of fun I like to do this just for the sake of doing so if it is a simple thing and I I find that Excel cannot do it I write a BBA code to automate it I have lots of fun I know the language and I can pick up and learn new things or relearn things I also like to play with the python feature that they have added but the limitation of python is excels python can only talk to the cell values in your workbook it cannot do anything more than reading the data and building something in Python for it for example you can't use the Excel python to highlight things like this that's not possible you can't even use it to Mark things as done that kind of thing is also technically not possible technically you can kind of tweak it around and write some very weird code that can produce that but let's just not go there so the python in Excel is good for reading the data and doing some operations on top of data and return the python related objects like either data frames or images or charts or whatever onto the grid so I find that python is just to be honest it's not as exciting to me in Excel as normal python is like I like working with python normally outside of excel but inside Excel the implementation is not really up out there plus the code doesn't even run here it has to go to the cloud and run so there are lots of other issues with it I got a video on the channel that talks more about that then comes the third one which is office Scripts and the problem with office scripts is twofold one it's a good thing because if I write an Office script I can run it on my computer I can also save this Excel file on SharePoint or online places and run it there as well so that means it it can run the code in browser whereas the VBA file that we have built it will only run on the computer if you try to open this in browser view or whatever it wouldn't run the VBA code so it has a limitation of being able to work only in a computer say the PC or Mac whereas The Office script would work elsewhere and it can also integrate with other office platform tools like SharePoint or power automate or powerapps or whatever so that means I can have a power automate flow that would trigger an Office script which will then do something and then send an email through Outlook and I have got a video that explains that entire flow on my channel like how do you send emails from Excel through Outlook using power automate and office Scripts but that is very rare case in in majority of the cases the automation tends to be a bit more involved like being able to do things here as well as sometimes even integrating with the windows API or something like you know you want to be able to do something on the Windows itself like a classic case for which I built a VBA code and you know got paid very handsomely is using the data in Excel generate and automatically send emails with attachments and letters and templates and formats this is something that I repeatedly used in clients places and every time I would get really good money out of it so that kind of a thing would have been pretty hard to achieve with the office Crypt automation whereas VBA would have been like really simple for me to do because I can write a piece of code in Excel I can write a piece of code in word word also has VBA and connect this and that to automate the whole flow with the click of button so that's how I would I have used VBA so I hope that gave you some clarity of where these fit in personally for me still the go to choice is VBA if I have to automate something but the better question that I always ask is do I need to write code or can I avoid it all together so I'll try to avoid it as much as I can but if I'm writing I'll write it and I like to take it up as a challenge like you know if I can't figure it out then I'll have fun figuring that out but many people you may not have that luxury of thinking it as fun you may already have a deadline or you have a boss or a client saying give me this quote by tomorrow or whatever in that case you just have to pick and choose your battles so if you have somebody paying you to write python code do that if you have somebody who says we want something that is deeply integrated with SharePoint or whatever then maybe use Office Scripts or if you have got a client that says I'll pay you good money to build a VBA macro just do that um yeah hopefully that gave you some clarity let me see what other things are there yes KV says what is python in Excel can do check out my channel there's a video on python in Excel that I put recently so so yeah it's just a couple of weeks old definitely watch it I mean not just you but anyone who is Keen to know what python in Excel can do what are some of the limitations and what I think about it feel free to check that out and the other thing that I forgot to mention with Eva is VBA is super compatible it will work in pretty much any version of excel whereas python in Excel only works in Excel 365. right now it is not even generally available office scripts only in Excel 365. so most of these things require you to use a specific version of a version of excel there is VBA Works in many versions of excel so yeah if you are dealing with clients or customers who might be using different computers in different versions of excel VBA would be a better way to distribute things because then you don't have to think about oh would this work there or not would you start with vbr python both languages seem like viable Alternatives um yeah I would probably learn whatever is my immediate need but I wouldn't ignore the other one so whether you are like if you are in college and you're just having all the free time in the world to learn probably start with python but if you're on the job already using Excel every day then maybe learn VBA because it can immediately take your productivity and improve it it can automate things for you or it can give you some interesting challenges but then later on definitely learn the other thing whatever you pick first so Attila asks this I probably don't know the answer to this that's why I didn't highlight this earlier but can you obtain the result of add sure CLI output to your Excel let's say uh modified all the available machine sizes I'm sorry I don't know um what CLI is or I mean if it's some type of data source available in Azure then you can pick it up using power query but I have never done it so I don't really know how would you go about automating an approval process that requires multiple conditions I was told power automate can do it but not familiar with it any other choices I think uh yeah approvals that kind of a thing workflow where multiple users would be involved and they will all need to open the file and do something or click a button power automate is probably an easier one um yeah search it up I have even though I've personally done it I have not put a video on it on my channel I'll I'll I might do it later on this year or next year I don't know when I'll get to that but yeah search it up it's not that hard you might find somebody who's more well versed in power automate and probably ask them to help you out for 30 minutes or something on Consulting basis or whatever so yeah that could also be an option but yeah try it out all right um so that is kind of that concludes that I'm gonna make sure that I save this file and I upload this as a link in the video description so you can grab this you know it kind of looks like a mishmash of sheets but all the code will be there you just have to right click on any sheet and go to view code or go to developer Visual Basic you'll see the code there all the things that we wrote so feel free to check this out and definitely try VBA even if you don't have any intention to use it practically VBA is a good Gateway language to learn it's a very easy language to learn and once you know how to code in one language newer languages become increasingly easier to learn so don't listen to people saying VBA is dying don't learn it that kind of nonsense people have been saying this pretty much all my adult life not that I'm old or anything but I'm hearing this for the last 15 years now and it gets old I mean this technology is here for a long time it'll be there for some more time for sure but whether it is there or not learning how to program in itself is a good skill once you know how to think in that direction then you can actually go really far with either VBA or other languages so definitely learn it definitely use it in some of your work and yeah go from there it was really fun doing the live stream um and I'll hang around for a bit but if you've got things to do feel free to drop off and catch up with your life uh and thanks for joining and you know it was fun I'll see you again next month around the same time but also on the weekly videos I put videos every Tuesday so feel free to check that out on the channel and if you're about to leave feel free to hit that like button and subscribe if you've not already done that JC says hello using data and reforms and VB I have two questions how do you simulate a master uh relationship and is it recommended to share the workbook in a network hmm okay so the the couple of things here in probably I have you have to explain more about that uh Master Slave thing that you're talking about like what do you mean by that is it one form having another form on top like that kind of a thing or something else that you're thinking so clarify that but um for me personally the go-to choice especially if I have to have a form and get people to put some data in and then capture all of that and do something with it whatever may be the thing here the problem is because multiple users need to use it and sometimes they may have to use it simultaneously not one at a time and they may have to use it from their own computers or different places I'll try to think of a solution that is not purely VBA because VBA has those limitations right it has to run on your computer and it's kind of like one at a time so if one thing is running the other operations will have to wait and if somebody is doing something then probably the other person opens it's going to create some more complications that you may not have anticipated so a better solution would be again the spare outfit everything but a solution that I would try to think about is maybe set up a forms like Microsoft forms or or some other data entry forms to capture the data collection and then use the automation tool say that power automate power query or VBA or office Scripts pull the data into into one place and then later use the data for my analysis or whatever so this is what I mean earlier when I said any kind of data automations I don't use VBA so this in my mind is kind of like a data automation you're trying to get some data and control and validate it so I'll try to avoid VBA for this because it it involves a lot more work than you anticipate especially if you're building forms then they can get more complicated quite quickly than you you would imagine and the number of lines of code you have to write and touch points you have to set up Edge conditions you have to test all of this is too much work for very little to gain hopefully that is helpful but yeah please clarify what you mean by simulating that other thing I'll try to add my two cents later I do all my work with Excel and Excel with VBA yeah that's great Ryan says we be is amazing automated multiple reports and saves me roughly 12 hours a week so there you go all these testimonials for VBA which is nice presentation I appreciate the fact that you started this at 5am a few hours into daylight savings time this was the crazy part I scheduled it I completely forgot that we are switching to Daylight savings so even though it's 5 AM my mind and body is it's till 4am for me and yeah given that it was our kid's birthday we had a big night last night as well but yeah no regrets I like doing this I enjoy uh doing my live and I'm glad I could do it um signer says I'm your old subscribers sir thank you so much learn with Salem says what software do you use for recording and making Excel videos I use techsmith Camtasia amazing software definitely try it out says can you share more insights on the features and capabilities of the immediate window for interacting with code in the Excel sheet so immediate window is kind of like a one line code at a time kind of a thing you can write one line of code with the question mark and it will run that code and if there is an output it will print that you can also use immediate window to send any debug dot outputs so if you say debug.print something in your code when you are looping let's say you are looping through and you are highlighting but it doesn't highlight the third column and you don't know why that is happening you can use debug.print to figure out what were the edge points of the address and why it is working or why it is not working um yeah I mean there's not that many features in it it's kind of like a backend console that you can see in between outputs or things that you don't want to see on the main thing but you just want to see in the back to scan through the results intermediate results hopefully that is helpful gopinath says what sort of coding you're suggesting for Excel work in Google Sheets also I don't think they are compatible yeah probably just stick to formulas in that case but the Google sheet has got its own scripting language called apps script or something so I don't I have not used that at all um JC says thank you you're welcome first uh face to finger says how well does VBA work with power query can we automate data extraction with power query thanks I think it kind of works a little bit not all the way this is because VBA is all object driven thing so it can only act on objects that are available to it but the object model of our query and powerpivot are not fully exposed to excel so we can't do everything inside power query through VBA I think you can still manipulate some of the query objects and stuff like that my friend Ken pulse has done quite a bit of work in this space so you may want to check his website it's called Excel Guru or can pulse just search online you'll find and see if there is any tips on automating power query with Aba I'm not too sure what VBA is acting you probably have just joined the stream and watching the end of it just rewind and watch the beginning you'll understand all right uh I think that's it uh I hope you all had fun I had lots of fun and you've been talking for like almost two and a half hours so I'll shut up now and I'll go back to uh yeah probably go downstairs and you know start prepping for the breakfast it's gonna be all celebrations today kid's birthday and you know later in the evening we are going out to a desert so it's gonna be fun um yeah air engine says how to protect the code you can protect the code it's not bulletproof you may have to try a few other things like compiling and distributing it as an executable or something if you want to be really sure about your code um but yeah oh sorry I just says thank you thank you so much everyone have a good weekend and uh stay awesome I'll catch you in the next one bye

Channel: Chandoo

Views: 68,413

Keywords: vba tutorial, vba complete tutorial, vba full example, excel vba demo, how to use VBA in excel, automation with VBA, excel vba free course, vba full course

Id: 7hd1Nn4cNyI

Length: 144min 10sec (8650 seconds)

Published: Sat Sep 23 2023