Excel VBA Basics #1 - Getting started with Range object, running and debugging

welcome youtubers my name is Daniel strong and welcome to excel vba is fun youtube channel this channel is actually dedicated and in honor of our very own YouTube guru Mike Gervin whose channel is actually excel is fun in fact if you've not been to his channel I want you to click pause and go to his channel right away I I owe my career to him and this guy is awesome he will he's got almost 2,000 videos now that will teach you excel inside and out one of the topics that he really doesn't cover too awful much is the visual basic aspect of Excel and so I thought in honor of him and kind of just to give back I would like to do some tutorials on that so let's get started we're going the first thing you need in order to use visual basic code is the Developer tab in the ribbon here by default it is not available to the average user what you do is you click on file I'm in Excel 2010 you go to file and click on options and in the customize ribbon button here you'll see the developer is deselected this checks box we are going to select it and then we're going to hit OK and you'll see immediately we have the Developer tab we're going to be using this a lot inside here you've got form controls such as buttons checkboxes things like this you may want to use on a worksheet however we also have the availability to record a macro that is when you want to investigate some procedure that you do and we'll review that more what what would like to show you today is the visual basic ribbon and when I will show you the visual basic editor that's important there's a button here and you'll see highlighted is Alt + f11 so rather than clicking this button here I typically will just hold my thumb on the Alt key and hit F like this now will bring up your Visual Basic Microsoft Visual Basic for applications that's a VBA and in Excel it can be a very very powerful and effective tool here you see we have these objects we have sheet1 sheet2 and sheet3 right now if I double click on them there's no code in here we don't have any code in either these sheets and we don't have any code into this workbook object so what I want to show you today is when you're in the Visual Basic when you're in the Visual Basic ribbon you can create what's called procedures similar to when you record a macro and you'll when you go look at them you'll see this drop-down here I'm like what I'm going to do is create a module and a module is basically just a project that stores codes so other than just storing a code inside a worksheet we're going to click on the module one here and what we're going to do is create our first procedure by doing that you just type sub you hit a space and you give it a name and you don't want to have any spaces in this name so we're going to call this sub test1 and when I hit enter you could you could do Brunt your parentheses like this I just hit enter and it nicely puts the in sub to close it up and it puts the parentheses for you so I'm going to create a little space here so we can work what our objective to do when our objective is to use the range and the cells we're going to use those two different types of codes and I'm going to show you that now every cell technically on your workbook is a range and a group of cells like this right now we have a 1 through c6 selected that is a range and as you know if you've been watching Excel as fun Mike Garvin's videos that can be notated as you know a 1 colon a 6 and that would be the range so that's how we type that in the visual basic and in order to denote that what we would do it was we would type range open parentheses and we can use the cells we're not going to use the cells right now I'm going to show you with the quotation mark we're going to type a 1 : a 6 and you put another quotation and you close it up and that is the range the range a 1 through a 6 and now we're going to tell it that we want that to equal the value of 1 so what I could way you see it nicely capitalizes the range and it spaces everything out correctly what we're going to do is we're going to run our macro what we're telling is what we're telling Excel is that we want the range of a 1 3 6 to equal 1 so every value in here is going to immediately become 1 at the same time so what we can do is we can either hit this play button by hitting f5 or we can click play what I'm going to do I'm going to do that now let's run our macro I'm going to click play so excuse me ah that would have been a 1 through C 6 let's do that let's do that a 1 through C 6 let's run it again you'll see that everything a 1 through C 6 is now become the value of 1 and if we wanted the value to be the text word blah bleh you just put that in quotes because text needs quotes and numbers do not need quotes now let's run it again let's see what happens ok so immediately the values of a1 through c6 have become the word block okay the next thing I want to show you is that we do not have to have Excel go through the whole range of commands at once rather than hitting this or clicking the f5 button to run this macro you can singularly run one line at a time once again I'm just going to show you if I hit f5 it does that but I'm what I wanted to show you now is that you could run through several lines of code for example if I put range B B for equals the word tacos okay totally random but what this tells Excel to do is of course do the blah thing right here and then it's going to take B for only and and put tacos in it if I hit f5 you'll see that all that is done immediately it did all this first and then it would took before and put tacos in there but I'm going to erase that I want to show you we're going to do is called debugging debugging is simply going one line at a time so what we can do is there is in fact I don't even see it on this menu I've been using shortcuts for so long I'm going to hit the f8 key on the very top of my keyboard and that this is highlighted this row so that's where we start is at the very beginning of the procedure and if I hit f8 of course it goes to the next one it has not run this line it is select this line this is the next one to be completed when I hit f8 it has now done the first command and when I hit f8 it will now do this come in right and you see what I hover my mouse over range before it says currently range b4 equals the word blah you see that pops up it says currently range b4 is blah if I f8 to execute the command that I wanted to be tacos will see when we hover over that range b4 equals tacos and sure enough we can see behind the screen that it has become tacos so that's it for this first youtube video i guess we did not get to the cells command we have reviewed a range and we can turn that into numbers we can turn it into text by surrounding it with quotes and we can use the f5 button to run a command immediately when we're in the visual basic menu we have five it does that we have learned that you can debug by clicking inside anywhere inside your sub procedure or your macro and hitting f8 and you can do one command at a time instead of doing it all once in any of you that I've ever recorded a macro or have edited a recorded macro but ever made one from scratch you may have you may know that you can access your macros if you click the macro button here or alt f8 to see the macro menu and you see that the one that we named test1 is available so oops I'm going to cancel that what I'd like to do now is run it that way really quick and then we'll in the video clicking that let's go ahead and click run we want to run the one called test one it's currently hidden but if I run it sure enough it did the commands that I wanted it to so thank you so much for viewing this excel vba is fun video hopefully the first of many and I hope that course might kerbin take this as a compliment and not as some kind of copy camp thing god bless you all and thanks for to

Channel: ExcelVbaIsFun

Views: 740,475

Rating: 4.8992352 out of 5

Keywords: ExcelVbaIsFun, Dan Strong, excel vba macros, excel vba, learn excel from beginner to advanced

Id: AIhKNNXzZLM

Length: 9min 57sec (597 seconds)

Published: Thu Feb 21 2013