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.
Going to say dot activate now there are many many different ways to select or activate worksheets this is just one
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.
