This screencast is perhaps going to be one of the most important screencasts as far as teaching you the basics of interfacing between Excel and VBA. I'm going to introduce you to objects properties and methods, the most common ones that we'll be working with in this course. So, the range object, the selection property, the active cell which is a property, the cells, offset, rows, columns, count, these are all properties that are tremendously useful. Then, we have two methods which are the select and activate. Let's start out with the range object. If you just want to refer to cell D22, you can use Range(''D22'') in quotations. If you want to refer to a range of cells, you can put A1:B5 for example. If you have a named cell, some you might know that in Excel you can click on a cell and then up here in the name box, you can type in a name for that cell. So now, it's named pressure. You can refer to it in terms of Range(''Pressure''). Range(''D:D'') refers to the entire column D. You can also select multiple columns. Range(''3:3'') is the entire row three. You can also use rows. There are other ways to identify range objects. One of the most common is the cells property. Cells by itself refers to all the cells on the spreadsheet. So, Cells(4,5). Remember, it's always rows then columns. That also corresponds to cell D5. There's an offset property. Got a couple of examples of this in a few minutes. If we had a named cell named temperature, then range temperature.offset one, one. It uses Temp as a base cell and move over one column and down one row. So, let me show you a couple examples. We can use the select, we can select Range (A2:C6). So, if I run this Range(A2:A6).Select, you notice that it selects that region. This by the way is almost the same as Range(''A2:C6'').Activate). I'm going to show you the main difference between select and activate. Ranges can also have a range property. This, we are going to use Range(''A2:A6''). But then, we're taking Range(''B2'') of that range. So, let me show you this. I'm just going to put that in a message box. So, Range(''A2:C6'') would be this region. Range (''B2'') means Range(''B2'') of that range. So, you can imagine the region I have selected here is its own new spreadsheet and Range(''B2'') then of A2 to C6 would be that cell. So, I can run this and it outputs negative two. The selection properties also really useful. So, we can refer to Selection.Cells(3,2). Recall that cells refers to all the cells in the spreadsheet. So, we can do a subproperty of a property, which means that we are referring to cells row three column two of our selection. So, in order to run this, I need to start with the selections and then I'm going to do F8. We're going to message box Selection.Cells(3,2). So, of my selection, the third row in the second column. By the way, we don't have to use cells, you can always just use Selection(3,2). In that case, if this is my selection, it's going to message box the third row and the second column. Next, we can always have a range property of the selection. So, if I choose this as my selection, and just to reiterate, a range is a fixed range, but a selection can change every time you run the sub. So, if I have a selection here and I run this, it's going to message box Range(''B2''). So, this is Range(''B2'') of my selection. Which again, you could imagine the selection being its own little spreadsheets and we're looking at B2 of that selection so that's five. Next, we can use the method select. So, what this is going to do is it's going to take Range(''B2:C3'') of the selection, right? So, this is like a subproperty of a property and it's going to select that. So, let me show you what this does. Now, when I do this whenever you have a method, it's not output, is just you don't have an output to it. So, I'm going to delete the message box there, and let's run this. So, it's going to look at Range(''B2:C3'') of the selection. So, that would be cell C4 to D5, went around that, it selects that region. So, let me just real quick show you the difference between activate and select. So, let me run the select first. So, I've got my selection, Selection.Cells(2,3 ) would be cells row two column three. So, that's this five. Then, I'm going to select that. So, look what happens when I run this, it selects that. So, that's the new selection. But when I replace it with activate, starting with the same selection, but now when I run this, I'm going to activate that. So, you see how I didn't change the selection like I did when this was a select. But instead, I changed the active cell to C3, which is Cells(2,3) of the selection. Active cell is that single active cell that's highlighted on the spreadsheet. A lot of times we use this with the offset property, and this it's an output, so you need either to message box it or to assign it to a variable. If this is my active cell, this is going to message box whatever cell is one row. So, plus one row and plus one column, and that'll be ten. So, when you run this, it should display 10, which it does. I like to use the offset select. So, what this does, and this is a method, so we're not outputting anything. What this does is it uses our active cell as a base, it offsets minus four rows. If I start with a minus six, this is going to select the cell that's offset minus four rows of one, two, three, four and two columns rows. So, it's going to go one, two. So, we should give the nine, and it's going to select that. So, we run that, and it selects the nine. We can also use the count property. We can count the number of cells in A1 to C3. This doesn't tell you how many cells have numbers in them, it just counts the number of cells. We can use the rows property along with the count properties. We could also do the same for columns. So here, I've got it set up. I've dimmed two integers, dimmed number of rows as integer, and nc number of columns as integer. R, then is your Range.Rows.Count. So, how many rows are in that range? Number of columns will give us the number of columns in Range(''A1:C5''). Because it's not a selection, I can have my cursor anywhere, and we're going to go ahead and run this. I haven't message box anything, so we'll just look down here in the locals window to make sure that those are working, and I'll bring this up. So, number of rows is five, and then I execute the next line and we've counted the number of columns. Similarly, I can just change the range object to the selection property. This will depend upon your selection before running the sub every time. So, let's just do an example here. I'm going to step through. Again, I don't have a message box, so we can look down here in the locals window. It counted four columns and four rows. These because they're not methods, these are properties, they always outputs something. So, you either need a message box or they need to be on the right side of inequality. You can't just write Selection.Count on its own, it'll give you an error because this is returning a value. I wanted to show you something quite useful. If you don't know going into the sub what the row and the column are going to be, so the row number and the column letter, you can always obtain that from the user. So, in this example, I'm dimming rowindex as an integer, but then col as a string because that's going to be a letter. Let's just put in row three, click Okay. It's going to ask us then for the letter. So, let's just do C, and then that should be C3 should be five, and it message boxes that value. So, that's how you can obtain from the user the row and column, and do something with that.
