Now that you've had some introductory experience making some simple subs in VBA, I wanted to delve deeper into the object hierarchy structure in VBA, we're going to talk about properties and methods and events in this screencast. Just want to show you an example of object hierarchy in VBA. It starts with the application which is Excel, and Excel has a bunch of objects associated with it. For example, you can have an add-in, like the Solver add-in or the the Data Analysis ToolPak, or maybe you can make your own add-in. There's also windows that pop up, workbooks, worksheet functions. Each of these objects has sub objects. For example, inside of a workbook, you might have charts. The workbook has a name. You can have Visual Basic projects. Those are like when you bring up the editor, you're working in a project that's associated with the workbook. The workbook can have windows. The workbook obviously has worksheets and if we zoom in on the worksheet object, it's got sub objects, so it's got a comment. There are hyperlinks. You can have names to a worksheet. Obviously it has to be named the Page Setup and one of the most common is the range object, so we're going to talk a lot about the range object. You can also have object collections like workbooks. Instead of just workbook you can have with an S there that corresponds to all currently open workbook objects. You can have charts, you can have a collection of all the worksheets. For example, to referring to a worksheet called main that's how you would refer to the ("Main") worksheet. Maybe you have a chart that's named ("XY"). That's how you can refer to that of the collection charts. The complete object references really include everything from the application down to the specific thing that you're referring to. For example, Application.Workbooks, the Excel application. You're talking about "project6". In the workbooks of "project6", you're referring to the main worksheet and then Range ("B16") on the main worksheet. A lot of times the application can be dropped. If you've only got one project open, you can drop the workbook name, and you can just use worksheets dot main. If you only have one worksheet or if you want to, you can drop that and just refer to a range. But if maybe your workbook has multiple worksheets, and if that's the case, you need to make sure that you're properly activating the different worksheets as you go back and forth between them. In that case, you'd use Worksheets.Range to refer to those. Let's talk about properties, methods and events. Properties are attributes of objects. Methods are actions to be taken on objects and events are happenings that objects respond to. Just some simple examples and in a subsequent screencast, I'm going to show you some more examples of these. An example of a property would be ("RangeA1''), that's cell ("A1").Width. That's the width of cell ("A1"). A method, it's an action that's taken on an object. Range("B2").Clear is clearing everything in Range("B2"). An event, for example, when a workbook is opened, maybe when a worksheet is activated or when a workbook is closed, those are events. There's lots of these, there's literally thousands of these. I want to show you can find more information about these. If you're more interested, you can go into the editor, and let me start with this close because this is the initial view that you might have. You go up here to this object browser or you can click "F2". This brings up all the different associations between the different objects and properties, methods and so on. For example, let's look at range. Range is one of the most common ones. You can click on "Range" here and then this tells you all of the different things that apply to range. For example, there's a activate here and you have this little symbol here, which denotes that it's a method. You can also have a range address. That's a property, so this icon is for properties. You can clear, that's a method. You can copy, that's a method. Some other things, if you had a cell maybe like the interior is another property. You can have the color index, you can create a pattern, and so on. This is how you can get more information on all of the different associations between the objects, properties, methods, and events. Let's take a closer look at the range object properties. The range object can have a value. Obviously that's what's stored in the cell. It can have text and over here you see if you can read/write it, or just read-only. Value of the one shown here is the only thing that you can change. You can also change things like the color and I'll show you how we can do that. Count, you can count the number of cells in a range. If the range is a single cell, you can get the column index and the row index. You can also get the address of a particular range and there's others. For example, if I've got some data over here and I just do message box Range (" A1") just a little test, when I run this, it's going to spit out four. By the way, if you leave off a dot something on range, the default property is value. You could, if you want to just type in value or you can just leave it off because value is the most common by far of all the properties of range. If you wanted to maybe look at the color, maybe we go over here and we make this orange and I wanted to, I can type in interior.colorindex, and we're going to message box that when I run this so the orange has a color index of 44. Color index is something that you can write to. I can swap this around to write to that property so the color index, I could say it's equal to 50 and when I run this, it's going to change that to a green which corresponds to the color index of 50. In subsequent screencasts and through different examples in this course, you're going to get experienced using a lot of these other properties. Select, you can select the cells in a range of cells. You can copy and paste. If you did Range("C1").Copy, this second position here is where you're going to paste it. You can also erase using.Clear and you can delete. You can delete and that shifts other cells into place. Let me just show you a couple examples here. This first one, Selection.Clear is a method on our selection so this is our selection. When I run this using F5, it's going to clear the selection so that's a method. Maybe for this second one, this is also a method we're going to copy. We have Range("A1") to ("A4"). Copy. Then this second position here is where we're going to paste it. I don't need to start with a selection because the object is the range and when I run this, it's just going to copy that and paste it over there. This is just an introduction to objects, properties, methods, and events. We'll talk more about this in the next screencast.
