Through we best get started you won't get very far with Excel VBA unless you understand how to select or refer to cells and this video is going to teach you several different ways to do that all of our examples are based on this list of the top ten highest grossing films of 2012 and we're going to be writing subroutines which will add new films to the list so to start with we need to get into the VBA editor and the quickest easiest way to do that is to hold down the Alt key on your keyboard and press f11 when you get into the VBA.
Editor we'll need to insert a new module and we can do that by right clicking in the project Explorer choosing insert and choosing module and then finally we'll just quickly rename the module and I'm going to go mine mod selecting cells so that we're ready to go we'll start with a quick look at how you can select single individual cells based on that absolute position on the worksheet so let's have a quick subletting in which we can write the code that will do that serve select single cells by position enter a couple.
Of times and tap once so what I need to start doing now is selecting single individual cells if I quickly switch back into Excel itself I'll turn Dec 11 on your keyboard the cells I need to select are a 13 B 13 and then C 13 and I need to fill in the cells with the relevant values so to do that I'm going to show you first of all the most common way to select an individual cell that's using a keyword called range range is a type of object in Excel VBA and is how Excel VBA describes any cell or block of.
Cells is referred to as a range object you can use this keyword to refer to a single individual cell by typing in a set of round brackets or parentheses and then double quotes and then the cell reference of the cell that you want to refer to close the double quotes and close around brackets and then what I would like to do to this object is I'd like to select it if I type in a full stop that should display the intellisense list the method that I'm.
You can always hold down the ctrl key on your keyboard and press the spacebar to attempt to force the intellisense list to appear and that will allow you to pick out certain long words and means you don't have to type them in so the word I want is called active cell and what I'd like to do to the active cell is changes value so type in a full stop after the word active cell and look for the value property and there it is it's the second one after the letter V so what I'd like to do is change the value to be equal to the number 11 which is.
The next number that I want to add in column a so there we go two lines that will first of all select a single cell and then change the value of the cell that has just been selected the next technique will look up for selecting the single cell allow you to refer to a row number and the column number rather than a cell reference so what we'll do although I could write range B 13 dot select what I'm going to do instead is use the cells keyword cells if you have.
An a set of parentheses after it allows you to specify a row number and the column number for a cell that you want to refer to so the row number here will be cut will be row number 13 follow that with a comma the column number will be column number 2 which is column B close the parentheses and then what I'd like to do is select that cell if I type in a full stop you can see hopefully one disadvantage of using cells compared to range is that it doesn't display the intellisense and I can't press ctrl + space to force the intellisense list to appear either all you have to be aware.
Of really is that when you use the cells keyword it returns a reference to a range object so the things that you can do to that range object are exactly the same as what you can do had you used the range keyword instead so I can say cells a 13 comma 2 dot select hit enter and then what I'd like to do is change the value of the cell that has just been selected so again if I press ctrl + space at the start of the next line look.
For the word active cell dots value equals and then apparently the 11th highest grossing film of year 2012 was a film called The Lorax I can spell up properly it doesn't matter a bit of an odd made-up word anyway and any literal text that you want to use in a Cell you must enclose in a set of double quotes so unlike numbers which can be just written directly into your code you must enclose literal text in double quotes so there we go those are two values for our film filled.
Posts Related:
In so there's one final way I wanted to
Show you for selecting the single cell it's a fairly unusual technique you're unlikely to see this used frequently in the real world but there's a shortcut way to refer to a cell based on its cell reference without having to use the key word range or the key word cells you can actually in a set of square brackets type in the cell reference of the cell you want to refer to and again if you type in a full stop after you've done this you don't see any intellisense but.This is just a quick shorthand way to refer to a range object and you can again use exactly the same list of properties and methods had you actually used the word of range so c-13 dot select then hit enter and I want to change the value of the newly active cell so controlling space to bring up the intellisense list look for active cell time 2 . look for the value property and I want to make this equal to a release date now according to.
Wikipedia of the lorax was released on the 2nd of march 2012 when you want to enter dates in VBA you need to enclose days in a set of hash marks and UK users will need to be very very careful about typing in their dates in there in the UK date format if I typed in to 4/3 for size 2012 all those who were - a person from the UK that looks like the 2nd of March 2012 that's actually February 3rd 2012 so yes unfortunately dates are in.
U.s. format in VBA regardless of what the language settings of your computer are so there are a couple of different ways around this you could just I suppose remember that it's month first and day second or if you prefer you can type in your dates like so you can type in your days as 2 followed by a short three-letter code for the month followed by the year when you hit enter at the end of the line you will see that this date format changes but what you can guarantee is that the month goes into.
The correct position and the day goes into the correct position so essentially you don't have to worry about which bit is where so there we go three different ways to refer to select and change the values of cells so all that remains for this symbol routine is to execute the code to make sure the values go in the right place and I think it's probably worthwhile stepping through this procedure to make sure that we can see each line happening as we expect it to so I'm going to resize my screen slightly so that I can see excel in the.
Background okay so I can see the cells aren't going to be a be referring to and then I click anywhere inside the subroutine I want to to execute and then plus the f8 key once so each subsequent time I now press the f8 key you'll execute the line that is currently highlighted so press f8 to reach a range a 13 dot selects when I execute this line we ought to be able to see in the background that cell becomes selected so f8 and there it is so the value then becomes 11 for the active cell and then we move on to the cells 13 comma 2 and also that cell b13.
And so on and then c-13 a slightly unusual method which also works and then that changes the value to err to the 2nd of March notice again UK uses that when you transfer the value into a cell in the worksheet which is a date then that takes onboard the date formatting of your computer so so the front end of Excel will use the regional settings of your computer whereas there is just the VBA code itself which doesn't so this is a promise this is the 2nd of March 2012.
Not February 3rd so I press f8 1 violence I'm just end the subroutine there we go there selecting single cells based on absolute position one thing that's very important to realize about selecting or referring to range objects is that it's implied that you want to do that on whichever worksheet is active when you execute the code so I can demonstrate what I mean by that by manually selecting a different worksheet in my workbook and then return to the VB editor and execute this.
Subroutine again I'm going to do this by using the Run button or pressing f5 on my keyboard this time so it should just happen as quickly as possible and hopefully you can see that this has happened in whichever worksheet I have active so it was in g2 this time that could mean that when you do this sort of thing in the real world you'll want to write a line of code which first of all selects the relevant worksheet so if I add a line to the top of my subroutine which will which will do that for us I'm going to refer to worksheets sheet 1 I'm.
Going to say dot activate now there are many many different ways to select or activate worksheets this is just one
Simple technique that you'll find commonly use we'll have another video which explains the various different ways of referring to in selecting worksheets later on but this will make sure that when I execute my code whichever watch it does on to begin with I change from that one into what you want and then change the values of the appropriate cells so I use f8 to step.Through this subroutine now you can hopefully see at the bottom I'm still on sheet 2 if I use f8 to step through this routine when I reach this one worksheet sheet won't activate I first of all move to sheet 1 and then subsequently select and change the values of all of those cells game you can even extend this technique of referring to a specific worksheet first and by referring to a specific workbook as well if I pop back into Excel briefly and create a quick new workbook I can do that by pulling on the ctrl key and pressing n so have a new workbook which.
Is called book 2 as I haven't saved it yet it doesn't have a file name extension if I go back to the VB editor now in my project Explorer I should see a new project listed for that new workbook if I extend my screen down again you can see VBA project book 2 so what I could do in my select single cells by position subroutine is before I refer to a specific worksheet I could also refer to a specific workbook I believe that by saying workbooks open parentheses in double quotes book two which is his name closer double quotes closer parentheses.
And then say dot activate so it's very similar to how you activate a worksheet one thing to bear in mind is if you have saved the workbook you'll need to provide the filename extension here as well so for example if I was referring to my top movies 2012 I'd have to extend dot XLS M to the name but what I can do now is if I quickly switch back into Excel and I'm going to make sure I'm in the top movies workbook first of all I'm going to make sure I'm on sheet 1 if I pop back into the VBA.
Editor again with Alt + f11 and if I step through the first few lines of this subroutine using the f8 key I'll find that the very first thing that happens in fats you'll see this a bit more clearly if you can see the title bars of the Excel window I'm in top movies 2012 when I hit f8 here it now moves into book two first and then it make sure it's on a specific worksheet and then so on and so on and so on it will fill in the values in the relevant cells so there you go that's how you can get from.
Any workbook into any other open workbook any worksheet in that workbook and then any single cell on that worksheet so far we've approached the problem of changing cell values in the same way you would have to do it in the real world if you were doing it manually we've selected a cell and then change the value of the cell we've just selected but in VBA is possible to change the value of a cell without having to select.
It first so when I demonstrate that with a quick new subroutine which adds a new film to the next row which we row number 14 so sub change cell values without selecting them I should pick shorter shorter subroutines goodnight train cell values without selecting got that right in a moment there we go so to begin with you need to pick one of the methods you like to use to refer to yourselves you neither use range cells or the shorthand way I'm going to refer to using range mainly because I get the.
Intellisense available with a range keyword again I can use my intellisense at the start of the other line I can press ctrl in space and look for the word range in the list and then open it out of round brackets and double quotes and this time the first seller once referred to is a 14 closer double quotes closer parentheses and then hit the full stop so previously I applied the select method to the range object I've referred to what I'm going to do now is I'm going to just directly refer to the value.
Property so it's Aven letter V you'll see that I can directly refer to the value property of a range whether or not it's selected so range a $14 u equals and then the next number in a list with the number 12 and then hit enter and I can carry on in that same vein I can say range B 14 sorry 14 dot value dots of value try again equals and then the next filmin list according to her to a comedians wreckit-ralph that was the twelfth highest-grossing film of 2012 I've not seen it I don't know if it's.
Any good or not and then finally I can say range C 14 dot value equals and the release date for wreckit-ralph was the second of November so I'm going to type that is to not 2012 close the hash mark and when I hit enter the end of the line I'll know that the number for the month and the number of the day go into the correct place so if I resize the VP editor window so we can see Excel in the background and just scroll down so I can see the subroutine I've just written we can step through.
This one and show you that you can clearly see that I haven't got any of these cells selected but I use f8 to step through this routine you'll see us by magic almost the valleys appear in the cells without having to have them selected this is a much more efficient technique than selecting a cell and then changing its value you can indirectly change the value of the cell using a technique like this again hopefully you'll notice it's implied the cells you want to change are on the currently active worksheet when you execute the.
Code but you can extend lines like this to refer to cells on a different worksheet entirely so at the start of each of these lines if I refer to let's see worksheets sheet 2 dot range a 14 etc and if I copy and paste that part to the beginning of each of these lines then what this will do is change the value of a specific cell on the specific worksheet without having to have either of those two things selected so if I step through this routine just prove that I'm running it and then what I.
Would have to do then is manually go away in check sheet 2 to ensure those valleys are there and indeed there they are and guess what of course you can extend this yet even further by referring to a specific workbook first so if I create another brand new workbook while I'm here that's going to be book 3 this time back to the VB editor and at the start of each of these lines I could refer to workbooks book 3 dot worksheets g2 and then this specific.
Range object that I want to refer to there again I'll copy this to the to the beginning of each of the other lines back in Excel just quickly make sure that I've gone back to my original top movies 2012 workbook then in the VB editor make sure we can see the screen at the top you won't see this change we're not going to move away from the top movies workbook now but when I step through this routine each of those three lines have been executed on a completely.
Different workbook if I switch into that book three now and head on to sheet 2 I'll see that those relative those specific cells have been filled in from a completely different workbook so you hopefully see that this sort of technique can make your go much more efficient you don't have to perform all these extra selections or activations of workbooks and worksheets and cells you can indirectly modify the values of cells using a line of code such as this.
One now that we've seen how to select single cells let's move on and look at how you can select multiple cells at the same time so for example we might want to format the titles at the top of our table we'll write a simple routine that will select cells a1 to c1 and maybe change their background color so back to the VB editor first and we'll need another new subroutine I'm going to call it sub selects multiple cells and I'm going to give myself a bit more space.
Below this one just push up the caret so you can see it a little bit more clearly there we go just as with single cells there are several ways to select multiple cells let's start by using range so range open brackets and double quotes the first cell reference I want to refer to as a1 and the end of the block of cells I want to refer to as c1 so if you separate those with a colon like so close the quote in brackets dot select that's how you refer to multiple cells in a very straightforward way if I wanted to do something to the cells that I had just.
Selected I can't use the word active cell to do so active cell only have refers to one cell so when you have multiple cells selected and you want to refer to them you use the word selection instead so selection dot and unfortunately you don't provide you're not provided with any until he sends with the selection keyword that's what I'm going to do is choose to modify the interior property I'm gonna modify the color of the interior again UK users watch out for the American spelling of color there's no u in color in VBA and I'm going to change it to add to a background color.
I'll display my until he sends lifts with ctrl + space and for one of my RGB colors so I'm going to dark blue I think sounds good there we go so there's one simple way to select multiple cells now just as with single cells is not necessary to select multiple cells before you attempt to change one of their properties so let's imagine I wanted to change the font color of the same block of cells I'll use the range technique again so range a1 to c1 with a.
Colon separating them and rather than selecting the cells I can just refer directly to the property that I want to modify so this time is going to be the font property and hopefully it clear the advantage of this is that you don't lose the intellisense as you do when you use the word selection so font color equals and let's go with RGB white I think RGB white there we go I can also use my shorthand way to refer to multiple cells so if you remember from the previous examples we were looking at putting a.
Cell reference inside a set of square brackets so if I once again to refer to the same block of cells a1 to c1 I can simply do do this you want to see one in the set of square brackets and let's see let's let's change the font size this time so again unfortunately we don't use the intellisense we haven't got access to the intellisense list so you have to know what properties you can change so I'm going to make the font size equal to say 14 and there we go I think that's probably enough formatting for the first row so let's move on to that two row number two which.
I would just quickly switch back into excel contains the column headings so let's look at a couple of other ways to refer to that block of cells back into the VB editor I'm going to show you a slightly different syntax of the the range technique so if I go for the word range again and open some brackets and to begin with I'm going to refer to cell a2 in its own set of double quotes I don't know if you make out the tooltip that's appearing on-screen here the range property technically has two separate arguments which allow you to refer to effectively the top left-hand corner and the bottom right-hand corner.
Of the range you want to select or refer to so after I've referred to cell a2 I can type in a comma and then in its own set of double quotes again I can type in c2 so effectively refer to the corners of the block of cells that I want to select I'm not actually going to select them all I'm going to do is change the interior color again and I'm going to go for a CR a light blue this time I think do we have light blue there it is perfect so the syntax is slightly more.
Awkward there are a few more characters to type in compared to the original technique we looked at with using range but we are going to see this one use a lot more frequently later on it becomes much more useful when we start looking at relative selection methods so bear this one in mind with knees this one against several more times in this video this syntax would also be the only way we could incorporate cells to refer to multiple cells unfortunately you can't use cells by itself to refer to a block of cells on multiple range objects if we.
Wanted to use the cells technique what we would have to do is enclose it within this syntax of the range properties so what we would have to do is say range open parenthesis and then cells open parenthesis again we just move the mouse cursor the first cell I want to refer to is cell a what is our a - so that's row 2 column 1 close ones other parenthesis then another comma and cells in this case the row number will be 2 again and the column would be 3 so c2 closer.
Parenthesis 4 cells close another set for range and then I can say dot fun starts color perhaps equals I don't know let's give our RGB dark blue it's very incredibly awkward to do I think it's fairly unusual that you would want to use cells to refer to multiple range objects so you'd almost exclusively use range when you want to refer to multiple cells so all we should need to do now is give this quick routine a test to make sure that it does the drop of this meant to do let me - like the screen down so.
We can see the cells we're going to be affecting and then we'll step through the sponger so you can see that all the things we expect a band actively are happening so range a want to see once select so press f8 and there we go we see those cell selected and then that's going to change the interior color of the selected cells and that works the next line should work whether or not we have those cells selected in the first place let me just quickly click on a different cell altogether switch back to the VB editor and press f8 again and we'll see the font change to white regards of whether it was selected or.
Not and FAS again just to prove that the the other method works that increases our font size and then range a 2 to c2 we're going to modify the ya the interior color of those and then finally we going to change the font color of there of those cells to dark blue f8 one more time on and serve to make sure that we are that we finish running our subroutine and are all the techniques most of the basic techniques are least for selecting multiple cells if you've been using Excel for a while you might have encountered a feature called range names a range name is simply a label that.
Refers to a single cell or a block of cells so it's like a plain English word that refers to cells so we can create range names in several different ways I don't want to spend too long doing this what I want to focus more on how you do this in VBA but let's for instance create a range name which refers to our our list of column headings once you've selected some cells you can click into this area of the screen the name box and if you click into this you can type in a new name for that block of cells make sure you don't use spaces when you type in the name you want to use and make sure you press Enter when you finish.