VBA For Beginners: Mastering Variables Like A Pro

hello everyone this is Randy with Excel for Freelancers and in today's VBA for beginner series we're going to be covering all about variables how do we Master variables what are variables in the VBA code how we can use them to our advantage and how we can create incredible code using these variables we're going to get started right away once you're able to master these variables you're going to be able to write any type of code very clearly and you're going to be able to return back and understand exactly what the code is doing and I'm going to walk you through every step in today's tutorial so what is a variable right a variable is basically an assigned name or think of it like a container that can help and guide that set of data and the different types of variables can help us do different things we can display it in a certain way that data such as a date or number or a decimal or maybe a true or false or we can use it sometimes in math we can multip apply it or add it based on the variable type we're going to be getting into all that so we can use it to manipulate or display it so we have really five main variables there are many variables but we're going to focus on just the five main ones today and the first is a long variable long variable which we're going to be using in code in just a moment it's basically a data type to store an integer it can be a very high or very low number negative but it's always a whole number it's non-decimal number so it has no decimal and it's used whenever you need a whole number long is a great variable to use next up we have a double variable now a double variable does use the decimal it does contain that decimal and it's used to store that floating Point decimal number and it can be represent a decimal or it can also be used in a whole number and also using calculations so it's often used for Finance and Accounting and uh currencies and things like that next up we have the extremely common string variable now string variable is always entered using double quotes and it can use any type of data and it's stored as a text value so keep that in mind strings we use those all the time and you can't use strings with any kind of math or anything like that next up we have Boolean bullan is a very simple true or false type of variabl it's either true or false and that can be really helpful when you have just two options such as a true false for those Boolean types and lastly we have date variables date variables can also be used for both dates and times or combination of dates and times together so we use date variables often when we're doing scheduling and things like that what we're going to do right now inside this training is I'm going to get into a little bit of a sample of what we can do and how we can use all those variable types in a simple macro so we're going to be going over a macro right now all right so let's get into it in this sheet I've got a list of four employees and I've got whether they've completed the work what type of work a pay date an amount and whether they've been paid or not and I've got a little employee step here on the right here's the employee name a pay date work completed so what I would like to do is I would like to go through each employee and I'd like to create a macro that places the employee name the pay date the work completed the pay amount and then I'd like to print this little paste up so I want a macro that's going to help us to do just that and we can create a very simple macro so how are we going to do that well first of course we need to launch the developer VBA tab here within the Visual Basic editor we're going to need that the developer tab if you don't have that open that's okay you can also right click on anywhere and click customize the ribbon and you can find the developers tab right here that's the quickest way you can also go through options make sure that developers tab is selected we're going to press okay you can also use a shortcut key alt f11 to get you there and that's going to open the Visual Basic editor you're going to see something like this so the first thing what we want to do we've got two sheets here sheet one and Sheet two you can often rename the sheets and that's a really big help to you I always suggest to rename the sheetss at least so that you can recognize which sheet is which so what we're going to call sheet one that's basically our information for variable so I'm just going to call that we're not going to use that in the code but it's always a good idea to rename that now there's two ways to rename it you can rename the sheet here or we can rename the code name of the sheet so we're going to focus on that code name because that's the one that we're going to use inside VBA and so to do that you'll launch the properties now if you don't have the properties available all you would need to do is just click here or use F4 and it's going to launch that so we're going to change sheet name we're going to call this variable sheet and also we're going to call this just something simple sheet two we're going to call for employee sheet so we're just going to give it and we're going to refer to this sheet inside the codes you can keep them short you don't need to keep them long just make sure they're unique and understandable okay so now that we have our two sheets named what we want to do is we want to enter a module now that module is where we're going to write our code so you can right click on any of the sheets here and we're going to click insert and then module that's the module we want to enter and here's where we are going to write our macros and so the first thing what we want to do is create a macro name so we're always going to start out with sub which show for sub routine we're going to give it a name just going to call it pay employees okay so that's sufficient enough now we're going to switch to sheet two now what I would like to do is I would like to Dimension or declare some variables for this and that's very important so what we want to do is create those variables so we're going to always start out with dim short for Dimension some people refer to it as declare in memory but also Dimension I'll say Dimension and the first one we're going to do is employee name now when you're naming your variables It's always important to use a clear variable name I see a lot of people in their code use something like I as long are these one or two characters and I almost never do that because I just don't understand I'll come return back to my code and I'll be like what is I I don't know what i is so your variables should be very descriptive so that they're very clear as to what data they're holding right so very very clear make them descriptive you can make them long don't use such short variables that you don't understand what data they're actually holding so that's very important so the first one we're going to do is employee name so we'll do employee name as and then what kind of variabl so you have a lot of variables here we're going to use string so we see the Intelligence coming up so that's the first one we're going to move to the next line and we're going to do the next one and we also want to know the work completed work completed is also going to be a string so if we have more than one variable we can also use a comma like this and then let's do work completed coom and I like to also capitalize the first letter of each word to help make it easier to read and also another trick that we're going to use later we completed as a string okay great now what we want to do is go to the next line and we're going to do one for the pay date so we're going to Dimension di IM pay date as what a date so we're using a date so we want to look for that date variable next up we want amount so we can use Dimension dim pay amount abbreviated as we're going to use a double because this contains a decimal so very very important double okay second to last we also want our paid Dimension paid as what it's a true or false so we're going to use Boolean okay very good now one more very that I want I'm going to run a loop and we haven't gone over Loops yet but basically I need to know what row to extract the data from what row are we doing so we're going to use two long variables we'll start out with just one but I want to know what row right notice that our employees are on three four five and six so exactly what row are we going to pull the data from so we can use Dimension di IM employee row as a long variable a whole number variable okay so we'll write in whole whole just for that we can use a comment on this hole anything after the apostrophe is a comment whole number variable okay this is a true or false variable true false variable this one is a floating decimal floating decimal variable okay this is obviously a date variable and these are text or string variables so this is kind of self-explanatory string variable okay so now that we have all of that we get ready to write some code so what is it that we really want to do what I'd like to do is I'd like to take information from a given row and I'd like to put it in these cells right here cells K3 for our employee name work completed will go into K5 I want the pay date to go into N3 and I want the pay amount to go into N5 and I only want to pay those employees where in which I have not paid yet so anywhere that's been false I want to pay I do not want to pay anybody that's already true and after they've been paid I would like to mark this as true so the first thing let's say we were just paying one employee if we wanted to pay just one single employee I would do something like employee row equals we'll just use Row three which is Fred freder so put three in there so that's the employee and it's always good to comment your coder employee row next up what I want to do I want to use that variable inside some code in order to transfer the data from this table here over to this little form here this paste up form here so that's exactly what we're going to do and so to do that we're going to use a range so for employee name all of our attention is going to be focused on this sheet which is the employee sheet so this particular sheet we want to do something like this so we're going to write with employee sheet and the reason that we're going to do this this is because when we move data from one side to the other when we're manipulating data inside that sheet it's always good to specify a sheet sometimes it gets confusing when we have multiple sheets so we always want to specify a sheet because VBA won't know what sheet we if we don't specify so it's very important so we're going to specify the sheet once we do that we don't need to write it again unless we get out of the width so that means every time we write dot it is going to assume that it's inside that sheet so if we didn't we would have to do this employee sheet. range let's say we're going to do employe K3 do value equals so what's it going to equal it's going to equal whatever's in column B right so again we don't need to specify the sheet but I'm just showing you how employee sheet and if you're not sure you got it right you put the period there if it doesn't show up notice it doesn't show up that means we spelled something wrong so we make the correction again and when the tense shows up then we know we've got the sheet right so the employee sheet. range so what is it it is column B that's what we're extracting name and what is the row we're going to use a variable so that row is and employee row again if we have the spelling right in employee row and we make them all lowercase do value notice as soon as I hit enter it goes to uppercase however if I get it wrong right if I use the wrong variable or something incorrect right like this you see how nothing changes that's why I like to use capital letters because I can easily quickly tell as I'm programming that something is wrong okay so notice that I've specified the sheets here but since we're within wi since we've already told it yes we are working directly in we do not need the sheet here so we can remove the sheet here and we can remove this sheet here now of course if we're going to write to a different sheet we would need to put that sheet in there so that means when we type in dot it's already assuming that we're putting something about that sheet that's why we're within the width statement so so this is going to transfer the employee name from whatever is in B in the row we've already specified the rows Row three it's going to transfer directly to K3 so now that we know that we're going to just put a notation here employee self-explanatory but it's always good employee name okay so now that we have that we can make things we have four more to do so we're going to copy here and we're going to paste here and paste here and then paste here now we can just update so so now instead of K3 we're going to focus on work completed work completed is in K5 so we're going to change that variable notice that work completed is in column C so we're going to do that column C so we're going to put here work completed work completed okay next up now instead of K3 we're going to focus on pay date pay date is in N3 so it's going to move into N3 and where's it coming from it's coming from column D so we're going to make that update we're going to call that the pay date okay now we know that lastly is the pay amount and that is going to be in n and we see that we have inside column e so that's what we would do if we wanted to make a straight transfer but we want to use variables right so how do we take that information and use variables so what we're going to do is we're going to transfer them to variables so how would we do that so we assign these variables to the values so for example we would do employee name is equal to so what we want to do is we want to take whatever's in B in the employee route we want to assign it to that variable and that is the employee name so instead of making the transfer we can do that so now we see the employee name is contained on that so we're going to copy and we're going to paste it there now instead of coming from B we just need to replace this with the employee name so we can use employee name and we would do that for each one so that's how we can do it so that way the string value is held directly inside the employee name and we can do the same for work completed is also equal to what's located in column C so that would be here column C so we just need to copy that change it to column C and this is called the work completed now instead of column C here we can actually move it to work completed work completed which is the variable right here okay so now that we have that we would just move up next up we also want to know our pay date so again our pay date is equal to whatever is located got too many equals there whatever is located in column D perfect okay and that is our pay date and then instead of here we can actually use our variable name which is pay date okay so we can separate this and lastly we have the pay amount so we can put that pay amount it's going to be be stored inside that variable and we'll see how that works pay amount is going to be equal to what's located in E okay all right e and lastly we also want our paid we need to know whether it's paid or Not So Paid is equal to what's located in column F so we're going to change that to F so this is whether it's been paid or not F so here we go this is our paid and then we could put true or false so this is a paid amount okay so now now instead of N3 is going to take on our okay amount here amount so we make sure we got the variables right and this is going to be N5 okay great so now what we're doing is we're taking the variables from our rows B C D and E and F all on the employee row we're storing the values in the string variable or date variable or double variable or Boolean and then we're going to place it in here but we don't want to necessarily place them in here we only want to place them in there if are paid equals false because they have not been paid yet if it's true that means they've already been paid so we don't want to pay them so what we're going to do is we're going to use an if then statement to check and we're going to use something like this if paid equals and notice when I put equals because we have already set that as a Boolean variable our options are simple either true or false so if paid equals False only this then are we going to continue and we need to make sure that we put end if at the bottom and we want to bring this out and tab it so we can see so if paid equals false not paid yet so only then are we going to actually take that information and place it in there and lastly what do we want to do well I would like to print it out print it out to the default printer how are we going to do that well to do that let's just do it easy so what I'm going to do is I'm going to highlight all this and all we need to do is go into the page layout and then what we can do is print area and set the print area that's it that's all we have to do if we were to preview it we can take a quick look and we'll probably set this to orientation here we can change that to landscape so that it all shows up perfect so we can go back to there and now what we can do is go back into and we can create a print out now we're already inside the employee sheet so what we're going to do is we're just going to create a print out dot print out and we have some options here and what are those options we just need to press a space from or two is basically number of sheets from one sheet to two sheets we don't necessarily need that we'll just leave a blank you just add a comma comma we don't need multiple copies so we can just put a comma leaving a blank do we want to preview it we'll put false we don't want to preview it maybe we do maybe we don't but false means we don't want to preview it do we want to print it to a specific printer or do we want to print it to our default printer I want to put true means our active printer whatever the default printer is that's we're going to print to we don't want to print it to file so we're going to leave a comma there colate we don't want to give it a print file name so again we're going to put a comma and do we want to ignore print areas and no I don't want to ignore them I want to put false because I want to keep the print area okay great so that macro is going to do that so now what we're going to do instead of just running this macro the first thing what we want to do is we want to use f8 we're going to step through the code using f8 and that is going to help us with that so we can see here we can indent we have some options here we can toggle the breakpoint but what I want to do is I want to use f8 and it's gonna highlight this line and we're going to go through step by step first thing is we're going to set employee rows to three so if we look we hover over this we see employee rows already three now we're going to focus on the employee sheet the employee name when we tab through that we see it's Fred Freds we see this is employee Row three and Fred frers so we can really see how those variables contain the value work uh completed is is website design the pay date is 615 it's coming from the boy the pay amount is 454 56 and now what we see is the paid now we see that this is paid is false right so we know that if paid it's not paid yet so now what we're going to do is we're going to assign the variables that employee name is going to move directly into K3 so we see right up here automatically moved there next up the work completed is going to go directly into K5 so we're going to tab through there and we see the website design coming to K5 next up N3 is going to take on that pay date so we're going to move over there we see that pay date and lastly N5 is going to take on that pay amount 454 now we can format that as an amount field next up we want to print out so we're just going to tab it it's going to automatically print to my default printer which just snag it and it's going to print out okay so that worked really great but what I want to do is I want to run a loop I want to use one of those long variables in fact we're going to need two notice we have the employee row but I I want to rotate through all of the rows I want to go from three to six and I want to check if they have not been paid yet there's one last thing we want to do we want to mark it as true so we're going to do that too so the last thing after it's printed out what we're going to do is we're going to take this value right here and we're going to make it true because they've been paid so that's the last thing we want to do equals true and we're going to put Mark as paid okay very good so now that we've marked it paid that way they don't get paid twice so what we want to do is we want to create a loop so we're going to run a loop we're going to start at Row three then we're going to move to four five and six how do we do that first thing we already have the employee row but what I want to do is start here so that employee row is going to be variable means it's going to change from three to four to five to six so we're going to do what's called a four next Loop four then our variable employee row is equal to three 2 six and anytime you start a four you must also close it and you close it by using next employee row okay and also what you want to do is you want to make sure that it's tabbed over so that you can clearly see that you're inside this Loop and then you're inside this if then statement so what it's going to do it's going to rotate it's first going to check to see if paid but what we want to do is actually we want to bring it right up here we want to change those variable names to make sure that they change each and every time so that's very important so as we Loop through each one of those we want to update the variables we want to update everything so from 3 to six so this employee row is going to change from 3 to four to 5 it's going to check if it's not been paid yet we are then going to pay it and then it's going to print each one so now let's take a look we're using this long variable row it's going to become Dynamic it changes through that Loop so let's take a look and see how that work we're going to use f8 okay so the first employee row is going to be three I can move a little bit quicker here we're going to gather that same data it's not been paid yet so we can then go ahead and print it out it's going to print out okay perfect now the last one we need to Mark F and the employee row is true so we want to make sure as we tab through that that gets marked as true so it doesn't get paid again now all the variables are now getting updated with the new amounts as we move through that now then if paid equals false which it does so we're going to pay so we're going to then add those variables so we can see the values are continuing out as we tab through the code using f8 we're going to print this one out and it's now going to print out so we see Lisa Smith site presentation and it's printed out for her now also if we tab through the code gets marked to true however Sally Strickland has already been paid so we're going to skip that if we look here we see the value of paid is already true so this isn't correct we're only going to continue if paid equals false so what it's going to do it's going to go from here it's going to skip everything and it's going to go right to the end if and so if we use f8 we see that it's skipped right there and it's going to move on to the last employee so we go through we add all the information going to take those into the variables we're then going to add them directly and update the cells accordingly we can see all the cells here inside our paste up update printing out one more time and then lastly we are going to then Mark this employee paid as true as we move through the code that's it so that is a great lesson on how we can use these type of variables in multiple ways and we can use them and place them directly inside cells so was a really great little lesson we learned um five different variable types we learned how to move ranges we learned how to create a very small Loop also to use the width statement and how to print out to the active printer so it's been a really training if you do like these trainings you want to learn more about VBA my good friend and Mentor Daniel strong has an incredible 30-hour VBA course that you just going to love it's going to Skyrocket your VBA skills so that you can start creating some incredible applications like I've been doing for the last six years on my YouTube channel so I hope you like these VBA for beginner trainings I do appreciate it don't forget to subscribe like and comment below let me know what you'd like to learn in VBA thanks so much and we'll see you next time

Channel: Excel For Freelancers

Views: 13,806

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, excel vba tutorial, learn excel vba, excel vba tutorial for beginners, vba, VBA For Beginners, variables, excel vba

Id: T_5BK7X0Gek

Length: 24min 30sec (1470 seconds)

Published: Sat Nov 25 2023