Excel's VBA Object Model

Quick reference

• 00:05 Taking what we've seen with the dog and the puppies object model,
• 00:08 what I wanna show you now is a small, small part of Excel's VBA object model.
• 00:14 And this is a very, very small piece.
• 00:17 You can see that we start off at the very top with an application.
• 00:20 That application has a name, and
• 00:22 the application you're actually looking at on screen right now, the application's
• 00:26 name would be PowerPoint, if I were to use VBA to query that.
• 00:30 When we get to Excel, obviously would be Excel.
• 00:33 The version number would return 16 for Excel 2016,
• 00:36 if you go back to Excel 97 it would return version 9.
• 00:39 If we were to execute a line of code called application.close,
• 00:43 it would actually close the application.
• 00:45 So there you go, this is how we actually start talking to these things, right?
• 00:49 Every application has one or more workbooks that are available in it, so
• 00:53 those form part of a collection of objects and that workbook will have a name,
• 00:57 that's the file name that it is saved under, it'll have a path,
• 01:00 where is it saved, what's the workbook size, how many kilobytes is it.
• 01:04 It'll have a method to open a new workbook, to save a workbook that you have
• 01:07 there, and within each workbook, there could be one or more worksheets.
• 01:11 Within that worksheet, which has a name, Sheet 1, Sheet 2,
• 01:15 or whatever you've called it, it may have another property.
• 01:17 Is it visible?
• 01:18 Is it hidden?
• 01:20 Within that worksheet, there are a whole bunch of ranges.
• 01:24 Now ranges could be individual cells,
• 01:27 there are actually cells underneath the range.
• 01:30 There are also ranges underneath the ranges because ranges can have subsets of
• 01:33 ranges within them as well.
• 01:35 So this is where things start to get a little bit confusing.
• 01:38 The big key is that we can actually refer to this, right,
• 01:40 the way from the very top, I would like
• 01:43 the Excel.application.workbooks1.worksheets1.-
• 01:49 rangeA5toB6.clearcontents and we can actually clear things out.
• 01:55 So this is how we start talking through this particular object model.
• 01:59 Now, where do you actually find the components that make this up?
• 02:02 How can you actually figure out where these things are,
• 02:05 what you can actually talk to?
• 02:07 There's a few different ways that we can do this.
• 02:09 The first one is the object browser.
• 02:11 Well, while you're in the Visual Basic Editor, if you press F2, it'll actually
• 02:15 take you into the object browser, which gives you the ability to drill in and
• 02:19 find out what properties and methods and events are available for any given object.
• 02:24 Okay, so this is one thing, and I'll give you a quick demo of this in a second.
• 02:27 Another way that you can actually figure this out is when you're actually coding,
• 02:31 you start typing something, workbooks 1, or active workbook.
• 02:34 and it will give you IntelliSense.
• 02:36 It will pop up a list automatically for you when you're coding, but
• 02:39 you can actually go through and pick things off that are available.
• 02:43 One caveat here,
• 02:44 this only works if the code that you're working with is actually valid.
• 02:47 If you are starting to form syntax errors and things in there,
• 02:50 then it's not gonna work.
• 02:51 But then the line wouldn't work anyways, so that may or may not be a big issue.
• 02:55 It's hard to see though in this piece how you can get
• 02:59 through three objects to get to what you want.
• 03:02 So if you're trying to figure out,
• 03:03 I'd like to figure out how to change the cell coloring and you're starting at
• 03:06 worksheets, it's very difficult to figure out how to actually make that chain.
• 03:10 The other way that you an actually figure these out is by using the Locals window
• 03:13 when you're coding.
• 03:14 And this is something that we'll actually cover when we get into Advanced Debugging.
• 03:17 But right now I wanna show you how to kick off the object browser and
• 03:20 how to actually browse through that.
• 03:21 So let's jump over to Excel.
• 03:24 So we're inside the visual basic,
• 03:25 and I can launch the object browse through one of two ways, so
• 03:29 I can come over here under this tool bar and just click the Object Browser button
• 03:33 or I can press F2 and that will open me into the object browser as well.
• 03:37 You will notice that when I do this, I am selected in All Libraries,
• 03:42 I can narrow this down if I wanted to just look at Excel, so I'm gonna do that.
• 03:46 And this is a search box that allows me to actually go through and
• 03:50 figure out some different things.
• 03:51 So I'm gonna type worksheet in here because I want to take a look at
• 03:55 worksheets, for example, and
• 03:56 you'll notice the first thing that comes up is it says Class Worksheet.
• 04:00 Then I've got this range and member worksheet and a bunch of other stuff but
• 04:04 what I'm looking for is that the object I'm looking for is actually the class.
• 04:07 So, I'm going to double click on that guy and
• 04:10 you'll notice that it's actually opened up the worksheet container in my classes and
• 04:14 it's telling me all of the VBA object members that are part of worksheet.
• 04:17 We've got something lightning bolts here, like activate that's an event, so
• 04:21 every time the worksheet activate is kicked off or
• 04:24 every time a worksheet is activated, it will kick off that event.
• 04:27 We have an activate method, same name, but if I were to type in worksheets1.activate,
• 04:33 this would actually activate that particular worksheet.
• 04:35 You can see that I've got some other things, AutoFilterMode, this will tell
• 04:38 me it's a boolean, which means true or false, so I could tell whether active or
• 04:42 AutoFilter is actually active on the worksheet or not.
• 04:45 You'll notice that I have a calculate event that would kick off if somebody
• 04:49 triggered it to calculate.
• 04:51 I can also have a method here to say I'd like to calculate this specific worksheet.
• 04:54 So it's something that I have available.
• 04:56 There is a whole bunch of different things that are on here but
• 04:59 what I wanna drill down to right now is actually one that
• 05:02 we know that worksheets have a bunch of ranges on them.
• 05:06 So let's go see if we can find, here we go, worksheet,
• 05:10 worksheet.range allows me, a property is range and it gives me cells,
• 05:15 I can click on range here and it will take me over to the range class.
• 05:19 So now I can actually look at going down these objects.
• 05:22 So, I started with the worksheet object, now I'm into the range object and
• 05:26 I can talk to that by going worksheets one.range, and
• 05:29 I would need to give it some kind of parameter to look at things.
• 05:32 So in here I could find range.address which will actually allow me to go
• 05:36 into a specific address for a cell.
• 05:38 So this is one way we can start drilling down through the object model to find
• 05:41 individual components.
• 05:42 And there will be others as we go through this course as well.

Lesson notes are only available for subscribers.