welcome to this yl tutorial in this video we're going to teach you the absolute basics of writing code in EXL VBA so we're going to start assuming that you don't know anything at all about VBA so we're going to be gentle with you and we're going to begin with the absolute basics of writing code we'll teach you how you can begin writing a sub routine or a program we'll give you some good practice guidelines as well for making sure your code is laid out neatly and how you can add comments to your code to explain what's going on once we've dealt with the absolute Basics we'll move on and show you couple of practical things that you can do with writing VBA so I'll teach you how to write an instruction in VBA to tell Excel to do something we'll go through the very basic grammar of a VBA sentence and then we'll show you how you can change the values of cells and how you can format cells as well after we've done all that it would be a bit unfair not to show you how to run the code you've written so I'll have a short section at the end which shows you how you can save a file which contains your code how you can then run a sub routine that you've written and finally quite importantly how you can reopen a file that contains macros and how you deal with basic security settings in Excel VBA so let's get started before we start writing our first program in VBA we need an example to aim for so what I'd like our program to do is to create a brand new worksheet in the current workbook and then to label the top leftand corner of that worksheet with something similar to what we have here who the worksheet was created by what data it was created on and what version number it is so to start with we need to get into the Visual Basic editor and hopefully you've watched our previous video on how you work in and and modify the Visual Basic editor the quickest and easiest way to do it whichever version of excel you're in is to hold down the ALT key and press f11 on your keyboard and that should take you directly to the VB editor now that we're in the VB editor we need to create a module which we're going to use to hold the code whe WR so to do that hopefully again you remember from the previous video on the Visual Basic editor you can either right click in the project Explorer and choose insert module alternatively you can simply head to the insert menu and choose module from there as well once the module has been created you can select it and I always think it's worthwhile renaming modules to explain what's held in them so once you've selected the module in the project Explorer head to the properties window and type in a new name in place of module one I'm going to call this one VBA Basics remember you can't use spaces in your module names hit enter and there we go now that we have our module we can start writing our first program so to do that click anywhere inside the module you're working in and then begin your program with a word Sub sub is short for the word sub routine which is one of the basic units of code in VBA so sub routine is is the same as a macro or a or a procedure or a program you'll hear them refer to as all sorts of different words after the word sub you need a space and then then you need to think of a sensible name for your program so mine's going to be called create and label new sheet um it's a nice descriptive name it's quite longwinded but it is descriptive it tells anybody looking at this what this program will do you'll also noticed that I did not use any spaces in the subroutine name so subroutines cannot contain spaces once you finished typing in the name all you need to do is press enter and you'll see a couple of things happen for you automatically first of all the words end sub appear below your cursor so any any sub routine that begins has to end at some point so all of our code is going to be written in between the sub and then sublines you might notice as well that the um the parentheses the round brackets appear at the end of the sub routine name just accept that those have to be there for the time being in later videos we'll show you what these parentheses are for and how you can use them you might also notice that the the word sub got a capital letter I typed it in with a lowercase s but VBA has cap capitalized it because it's a recognized keyword so what we need to do now is start writing the code that will perform our instructions so I could just start writing my programming instructions exactly where the flashing text cursor is what I end up with is one very big lump of text that would be quite difficult to read later on so I I'm going to encourage you to do is after you begin a sub routine give yourself a blank line and then press the tab key on your keyboard to indent your code one space just to show you the difference that this can make in a slightly longer program and don't worry if you don't understand what this program does but I want to show you the difference between writing code with indenting and without so with indenting hopefully you can see that the code is much more neatly laid out we've got blank lines to separate different parts and indenting indicating the different structures within your code we have sub and N Sub we have something called a for next Loop we've got an if and an end if structure there as well and within each of those structures all of our code is indented now compare that to the one below which hasn't got any indenting at all it does exactly the same job in exactly the same way but it's much more difficult to read so I'm going to encourage you to go with this style of programming and hopefully you'll agree with me that that's the right thing to do as well as laying out your code neatly another thing that will help you or even your users to understand what your code is doing is adding comments to your code so you can begin a comment in one of two ways you can either write a single quote or an apostrophe and then type in your your comment or if you're feeling a bit more old school you can type in the word REM which is short for remark I don't know why you'd want to type in three characters when you can type in just one so I'm going to stick to using apostrophes for my comments once you've begun a comment you can write out pretty much whatever you want after this anything that describes what your program or the next line of code will do so the first line of code we're going to write is going to create a new worksheet so my comment is going to say create a new worksheet it's hopefully going to be fairly obvious anyway it's a little bit of an unnecessary comment I wouldn't recommend adding a comment to every single line of code you write I think that's a bit of Overkill but um every sort of maybe four or five lines or so or one an important new technique that you're using crops up comments are really handy for that once you finish writing the comments anyway you don't need to close the quotes you can simply press enter at the end of the line and hopefully you'll see that the comment will turn a different color by default the color is green and if you remember from our previous video on setting up the Visual Basic editor you can always head to the tools menu choose options and in the editor format tab you can modify the colors of any type of text so you can select the comment text and choose a different color if if green isn't particularly visible to you but I'm going to cancel out to that for now and stick with my green color okay so now we need to write the instruction that will create create a new worksheet so to do that you need to know a little bit about the basic sentence structure in VBA I'm going to write a quick comment here which I'll delete in a moment basic VBA sentences in layman's terms the way a basic VBA sentence or instruction works is you begin a sentence with a reference to a thing so that would be the thing you want to perform an action on then you follow that with a full stop and then you say what action you would like to perform so that's the way sentence always are built in VBA it's always the thing first followed by a full stop and then what you want to do to that thing in VBA terms the thing is referred to as an object and the action is referred to as a method sorry not method method so our basic sentence to create a new worksheet is going to look like this the object we want to refer to is something called called worksheets worksheets refers to a collection of all the worksheets in the workbook if I type in a full stop I'm actually going to see a list of all of the things that I can do to that particular object so the list that appears is referred to as intellisense which is a horrible name for the feature but it's actually really really useful you can scroll through this list using the cursor keys or using The Mouse and the scroll bar you can even begin typing in the the the words if you know the ones that you want to use so I could start typing in for instance the letter M and that would take me to the first item that begins with the letter M but that's not what I want to do here the thing that I want to do to my worksheets object is actually the very first item in this list it's the method called add I can tell that this is a method because it has a little green flying brick symbol next to it I have no idea what these is meant to to represent they always look like little green flying bricks to me but anytime you see one of these little green flying brick symbols you can tell that you're looking at a method in VBA terms so with the word add highlighted I can either double click on it with the mouse or even more simply I can press the tab key on the keyboard to type in the rest of the word at that point if I press enter that's my first VBA instruction written now the next thing that I'd like to do in my code is start adding some values to my cells so I just want to start labeling the cells so I'm going to add a blank line and I'm going to add another comment um add titles to cells now the way that we're going to do that is in a slightly different sentence structure than the one we used previously we're not going to use a method of ourselves we're still going to start by referring to an object so just a quick comment to describe how this will work going to refer to an object first and then rather than try to perform an action we're going to change something called a property so a property is like an attribute of an object it's something you can look at and investigate and in many cases it's something you can also change so to change a property you assign a value to it using an equals operator so object. property equals whatever value you want to assign so so for us the objects in this case aren't going to be worksheets the object we're going to refer to is a specific cell and in VBA there are several ways to refer to cells in Excel the method we're going to use today is using the range keyword so start with the word range open some parentheses or round brackets and then some double quotes and then type in the cell reference of the cell you want to refer to close the double quotes and close the round brackets and that's the reference to the object now we need to specify which property we want to modify and the way that we can do that is in the same way as with the worksheets do addline we type in a full stop and that will display the Intellis sense list showing me all of the methods again you can see the little green flying bricks are the methods and the properties which are represented by these little fingers pointing at Bits of Paper I suppose all choice of symbols anyway the property that we want to refer to to modify the information stored in a cell is called value so if I start typing in the word value I start by V and then a that taks to the word validation value is one row down from there so if I press the down arrow key on my keyboard I can then press the Tab Key to type in the rest of that word what I can do then is Type in an equal sign the space spes aren't actually important to type in here I could get away without typing in the spaces by the way those will be added in for me actually automatically and then after you've typed in the equal sign you have to say what value you want to assign so for me this is going to be a piece of a literal text or a string and all strings in VBA are enclosed in a set of double quotes So in a set of double quotes I want to type in the words created by and then close the double quote and at that point I can hit enter and that's my first label added to my cell now the next couple of lines are going to be very similar to what I've written here to modify the value of cell A1 I want to add titles to cell A2 and A3 as well there's no real point in typing out this full line of code time after time when all I really need to modify the cell references and the text that I'm assigning so there is no such thing as cheap in when it comes to writing VBA code trust me on that one copy and paste if it speeds up what you're doing please do copy and paste lines of code you can do this using the right click menu as I've just done there you can also do it using the keyboard shortcuts contrl C to copy contrl V to paste and then all you need to do is tweak the little bits that you need to modify to update the instruction so I can do that one more time and then change cell A3 and the value of cell A3 should be version that was the title I wanted to add so that's nice and quick and easy way to assign values to multiple cells copying and pasting lines of code so now that we've filled in the titles for ourselves I want to start filling in the users details if I quickly switch back to Excel for a moment by holding down alt and then pressing f11 on the keyboard you can see the sorts of things I'd like to fill in so the username essentially of the person who created the worksheet then the datea it was created on and then whichever version number they wanted to assign so for the username rather than always typing in the same name I'd like that to read the person who's using the macro at the time who's running the program so whoever is logged into Windows I want that username to appear in cell B1 likewise for the dates I don't want to have to type in an exact date I want Excel to read whatever today's date is and use that value instead version number will will set to a value of one always so uh so that one will be sort of absolutely filled in so if I press alt and f11 to switch back to the VB editor again what I can now do is start filling in those values with some more lines of code so I think it's worth a quick extra comment here that says add user values to cells and I'm also going to do a quick bit of tidying up as well because I I I think this bit up here is a little bit messy so let me get rid of those few lines there and that makes things a little bit neater and then at that point I can simply paste in the line that I copied earlier on that modified the value of cell A1 I can change that now to cell or range b1. value equals and then instead of using an explicit piece of text a literal string what I'm going to do instead is use a function that will calculate the username of the person logged into windows so if I type in the function name which is called Environ it for environment and it actually lets you pick up on a variety of Windows environment variables to specify the particular bit of information you want open a set of round brackets and then for this particular function a set of double quotes and then the value of the parameter you want to retrieve so I want to retrieve the user name close the double quotes close round brackets and that is another way to set the value of a cell so rather than setting to be an explicit value this is going to be calculated by evaluating this function we can do a similar thing to calculate the date that will go into cell B2 so again if I paste in the same line that I copied earlier and modify the cell reference this time to B2 and instead of this literal string I'm going to replace that with a call to the function that calculates the date and it's really easy to remember in VBA the function is simply called date if I hit enter there's one more value to fill in I'll paste in the line one more time change the cell reference to B3 and this is going to fill in the the version number of the worksheet so we're always going to set that to one by the first time we create each sheet so if I remove this letter or string I can simply type in the number one in VBA numbers don't need to be enclosed in any kind of characters like text enclosing double quotes numbers can be can just be typed in as they are the final thing that we're going to get our sub routin to do is to format the titles that we added to cells A1 2 and three so let's have a new blank line and another new comment this is going to say format titles now the lines of code that I'm going to write here are going to going to be slightly different to what we were doing before we're not going to modify the value property there are other properties of cells that we need to modify to format them so rather than paste in the line I'm going to type this one out from scratch so I need to start with the word range again I need to refer to a range of cells rather than type out the whole word it's worth while knowing that you can force the Intellis sense list to appear by holding down the control key and pressing the space bar if you do that at the beginning of a line it will populate a list consing of all the main keywords you can refer to in this context the word I want to use is a word called range so if I look for the word range I can type in the letter R it happens to be the second one below or the second item that begins with the letter R so I use the arrow keys to select the word range and then press the Tab Key to type it in again I need to open a set of round brackets and then double quotes and this time rather than refer to individual cell references such as A1 A2 A3 Etc what I'm going to do is refer to the entire block with A1 colon A3 so it's very similar to the style of cell reference you see when you're using the sum function for instance in Excel it's not the only way to refer to a block of cells there are many many more ways to do this but this one will suffice for now so we close the double quotes after A3 close the round brackets and then I can type in a full stop and see the list of properties I've got to act on to format these cells so the first thing we'll try to do is modify the font color of the cells we've referred to so to do that I need to first of all refer to the font property of the cells and you can see it's a property because it has this little finger pointing at a piece of paper symbol but font itself has a bunch of other subproperties as well so after the word font if I type in another full stop that gives me another intellisense list with a subset of properties which apply to this font property so we like this is like an extension of the line we wrote up here object. property equals value we're going to have objectproperty do property equals value so the property we want in this case is called color now watch out UK users it's the American spelling of color there's no u in the word color in VBA regardless of which language settings you're using on your computer as well by the way this is always always the American spelling but we can make color equal to something so do font do color equals and then there's a really quick simple example I'm going to make the font color blue and the easiest way to do that in VBA is to type in VB blue hit enter at the end of the line and there we go now the next thing I'd like to do is modify the background color of the cell and it's another similar principle I need to start by referring to the range of cells I want to to modify so I'm going to press control in the space bar to pop up with the Intellis sense List look for the word range first of all by typing in the letter R press the down arrow key then I can press tab to type in the rest of the word then I can open my D my round brackets and double quotes and refer to A1 to A3 and yes I know I probably should have just copied and pasted this part but I wanted to show you going through that process again and then this time when I type in a full stop the first that I'm looking for is called interior by typ in the three letters i n t that show me the word interior and just like with the font property of a cell the interior property has more subproperties so if I press tab to type in that word and then press the full stop key that gives me another list of properties and the one I want hopefully again you can see is the same as before it's color so I'm going to make color equal to something else now previously we used something really simil symol we used VB blue in our previous example there's eight of these Visual Basic colors there's VB blue VB red VB green VB yellow VB white VB black and then the two slightly odd ones VB cyan and VB magenta now we know that there are many more than just eight colors available in VBA in fact you've got a full range of 16.7 million colors that you can use to pick from a slightly uh smaller subset of 16.7 million there's a set of what what I referred to as the RGB colors so if I press control and space on my keyboard at this point to display the intell sense list and if I type in the three says RGB that will take me to the list of RGB colors so it's a much wider selection that you can choose from here um with some lovely flowery fancy names so I I haven't the faintest idea what half of these actually really are so you could choose one at random and and sort of try try to try to see what it looks like when you've run your code and if you don't like it you could go back and and modify it later maybe we'll go with RGB light cyan for the time being let's see let's see what that gives us so if I press tab with that word highlighted I can finally press enter and I think at this stage I'm happy with what my program is trying to do so the next step is to save it and then run it now I always think it's worthwhile saving any code you've written before for you attempt to run it and the main reason for that is that you can't undo what a macro does so once you've run your code you cannot undo its changes other than by closing down your workbook and reopening it to get it back to a previous version so save it first and you can do this in in a couple of different ways any code you've written you can save either by clicking the save button here in the Visual Basic editor but you could also just switch back to excel this time I'll click the Excel button here or press alt and f11 on the keyboard and you can also save your your work from within Excel itself so if I clicked the save button here that would also save any code that I've written now the very first time you do this in a blank workbook if you choose to save a file and I'm going to try to put this I'll simply plunk it onto my uh maybe into my my C drive perhaps and I'll call it something along the lines of let's see basic VBA can't spell basic dare me basic VBA now if I try to save this as a normal Excel workbook when I hit the save button I'm going to get a warning message saying that I'm not allowed to save a Visual Basic project in a macro free workbook so what I need to do at this stage is click the no button I do not want to continue saving without my macros so I choose no what I need to do instead is change the type of workbook from a standard Excel workbook with xlsx to a Excel macro enabled workbook which is the standard choice you could also if you want to choose the binary workbook this allows macros to be saved but also the uh the old Legacy version of excel 97 to 2003 notice that if you're already using Excel 2003 you won't have to make this choice this is only for Excel 2007 and later so what I'm going to do is choose the macro enabled workbook type it's basic BBA is the name of the workbook and if I choose save my macros are now safely saved okay so nearly time for the Moment of Truth we need to run our macro to check that it actually works before I do that I'm actually going to delete my my little test sheet here the one that I set up to demonstrate my examples so I'm going to right click sheet one and choose to delete it there we go and now when I run my macro I'll know for sure that it has achieved the results that I wanted it to cuz all the other sheets are blank so what I can do is go back to the Visual Basic editor I'm going to press alt and f11 to do that then I need to make sure I've selected any line I need to make sure my cursor is anywhere between sub and N Sub for the program that I'd like to run then I've got several choices I could head to the Run menu and click on the option that says run sub I could also press the F5 ke on my keyboard or I could also just click the little green triangle button on my toolbar again I get a little hint about pressing F5 I'm going to press the F5 key on my keyboard and after hopefully a brief flash of the egg timer mouse cursor you might notice already that something has definitely happened because my project Explorer is showing me that I now have a new sheet four so if I switch back into Excel there we go there are my cells filled in on a brand new worksheet I'm not quite sure about my light cyan color Choice by the way I might go away and change that but I should see that all the values have been filled in I've got my username in cell B1 I have today's date in cell B2 and there we go success I think just because I'm not happy with my color Choice I'm going to delete that worksheet and I'm going to go back to my code by pressing alt and f11 and I'm going to modify the colors that I've chosen here so I'm going to select this color and delete it I'm going to press control and space on my keyboard type in RGB which I know will get me back to the list of red green blue colors and I'm going to look for another color maybe something may there's a color that starts with the word pale perhaps so there all the light colors pale pale turquoise perhaps let's go with that one I'll go with pale turquoise so I've made a change to my code I'm going to save it before I run it always a good policy then I'm going to going to click the little green triangle making sure that I've clicked anywhere inside this sub routine I'm going to click the green triangle and I now have a new sheet five if I switch back into Excel that's a much better choice pale turquoise so there we go success with running our macro so we've seen how to run your Macros from within the Visual Basic editor but you can also choose to run your code from within the Excel environment and there's many ways that you can do this in a later video we're going to show you some nice fancy techniques where you can create buttons on the spreadsheet or attach macros to to images and clip art and even create your own ribbon tabs and toolbar buttons to run your Macros today we're just going to focus on the Absolut basic way of doing things so if you're in Excel 2007 or later you can head to the developer tab on the ribbon and find the macros button here and click on it or you can use the keyboard shortcut which appears which is alt and f8 when you select this option it displays a dialog box which lists out all of the macros that you've written or recorded in all of the open workbooks you can limit this list to just a specific workbook if you prefer all you need to do then is select the work sorry select the macro that you want to run and simply click the Run button and that should do exactly the same actions as we saw earlier on one other thing you quickly need to know is how to do the same thing in Excel 2003 because the obviously the developer tab is not available in the ribbon so in earlier versions of excel what you would do instead is head to the tools menu choose macro and then choose macros and again you could use the same keyboard shortcut but that will display the same list and you can select the macro and click run to run it the final thing we're going to cover in this video is how you go about reopening a file which contains VBA code and that might sound like it should be an easy thing to do but you have to be aware of the default settings in Excel which try to disable macros when you open files which contain them and it sounds quite annoying that that happens but it's a it's a it's done for sensible reasons it's a security feature if you were opening up a file which contained code that you yourself hadn't written how could you guarantee that it was safe so although it's a little bit annoying it's a good idea in general now the way that you choose to enable macros when you open up a file is slightly different depending on which version of Xcel you happen to be using and also whether or not you have the Visual Basic editor open at the time as well so so let's start with Excel 2010 and the same technique Works in Excel 2013 as well so I'm going to close down my file which contains macros and at this point I don't have the VB editor open when I choose to go to the file menu and choose to reopen that file what I'll see at the top of the the screen to begin with is a little uh warning message in the uh in the message bar it tells me that my macers have been disabled very very simple in this version of Excel 2010 and 2013 all you have to do is choose to enable content and now your Macros will work again okay so now let's do the same thing but with the Visual Basic editor open so I'm going to press alt and f11 to reopen the VB editor then I'm going to switch back into Excel close down my file and I'm going to reopen it again so I choose file and then choose the the same file to open this time with the VB editor open I don't I don't get the message bar at the top of the screen I get a much more obvious message that I need to choose to enable my macros so very simp in this case just hit the enable macr button and once again you'll be allowed to run your code so that's how it works in Excel 2010 and 2013 let's have a look at Excel 2007 at this point so I'm going to close down my file in Excel 2010 and head over to Excel 2007 and again if I choose to open the file without the Visual Basic editor open when I open my file I get another security warning in the message bar at the top of the screen this time though it's not as simple a choice as just clicking enable content I have to click options which displays another dialog box then choose to enable this content and then click okay so it's a slightly more streamlined process in Excel 2010 the same thing would happen as Excel 2010 if I had the VB editor open so if I open up the VB editor head back into Excel close down the file choose to reopen it and again I get the dialog box which appears in which I simply click enable macros now the process for opening files with macros in Excel 2003 or earlier is somewhat different so if I switch into that application I'm going to head to the file menu and I'm going to choose to open up a different version of the same file so it's a it's a it's a legacy version of of the basic VBA file we've been working on when I choose to do that I'll be presented with a slightly more complex dialog box with a much longer message but the important part here is it's saying that macros are disabled because the security level is set to high so actually XL 2003 and earlier had a higher level of security than the newer versions of xld if I want my macros to run what I have to do is change the macro security level then close and reopen this file so to do that I first of all need to click okay on the message which has appeared and then I need to head to the security settings dialog box and I can do that in a couple of different ways if I have the Visual Basic toolbar displayed there's a quick way to get to security setting from the the button called security alternatively I can head to the tools menu choose macro and then choose security from this list as well so you can see there that when I display the dialog box that the default setting of security in Excel 2003 is high the sensible Choice the one that kind of replicates what we're doing in Excel 2007 and 2010 is the choice called medium this is one that will always ask you every time you open up a file would you like to enable macros so if I choose okay and then close down the file that I'm working on I don't need to save any changes because I haven't changed the file itself I've changed it a setting in Excel the application rather than in a specific workbook but if I go back to the file menu now choose to reopen that file this time I'll be asked would I like to enable or disable macros and this is true in Excel 2003 this is true whether you have the VB editor open or not you will always see the message presented to you in this way so if I choose enable macros I can now reuse my code as I've just mentioned how to change the security settings in Excel 2003 I'm going to very quickly show you how you can do the same thing in later versions of excel as well so if I head into let's say Excel 2010 to do this I can I can choose one of two options I can either go to the developer tab in the ribbon and choose the macro security option there and the same is true for Excel 2007 alternatively I can head to the file menu and choose options or in Excel 2007 I would choose the office button and then choose Excel options on the dialog box which appears I need to head to the trust center it's very all welli in sounding place but in the trust Center you can then head to trust Center settings finally in there you can head to the macro settings tab and this gives you the four levels of macro security that described completely differently in these versions of excels they're no longer low medium high Etc they have much more descriptive uh settings so so the default setting for for newer versions of excel is to say disable all macros with notification which means that when you open up the file macers are disabled but it tells you that they are disabled and you get the choice to enable them the other levels you can switch between I will I think again this is the most sensible one to go for the disable all macers with notification so if you find that somebody had changed your settings in your particular version of excel this is how you can get back to and change them to bat their default if you've enjoyed this training video you can find many more online training resources at www.y l.c.