VBA Objects & the Object Model

Quick reference

• 00:05 In order to become a very good VBA programmer, one of the things that we need
• 00:09 to understand is the object model behind Excel.
• 00:12 The reason for
• 00:12 this is because VBA is what we call an object oriented programming language.
• 00:17 We actually connect to,
• 00:18 manipulate, different objects in the context of working with the object model.
• 00:22 Now when you first start doing this,
• 00:24 this is going to feel like an uphill climb you're trying to move mountains.
• 00:27 And that's not the intent of this.
• 00:29 The real key here that we need to understand is how this stuff all works.
• 00:34 There are a few different elements inside the object model.
• 00:37 There are obviously objects.
• 00:40 These can be individual objects or collections of objects.
• 00:44 Every object has one or more properties.
• 00:47 So these might dictate certain things about them.
• 00:49 Heights and weights, or things like that.
• 00:52 Objects often, most of the time, have methods.
• 00:54 This is an action word that allows you to do something to your object.
• 00:57 And they also have events.
• 01:00 Events are things that are kicked off when something happens to the object.
• 01:03 Now this is outside the scope of this course.
• 01:05 More advanced programming.
• 01:06 But they are something that you will end up seeing.
• 01:10 If we actually go and build an object model for something,
• 01:14 that's not Excel related first.
• 01:17 Let's start with something, an object, we'll call it a dog.
• 01:20 This dog, you'll see in the gray box, is the object.
• 01:24 And the white boxes give us certain properties that the dog exhibits.
• 01:27 It may have name, it could be male or female,
• 01:30 it may have a certain height or weight, well it should.
• 01:32 It may have a volume, I mean some dogs are obviously louder than others.
• 01:36 It also has certain methods, the things that the dog might do, bark, eat, or
• 01:41 sleep, for example.
• 01:43 When we actually start programming, and writing something along this.
• 01:47 What we do is, we actually link these things together by using a period, okay.
• 01:51 So in this case here, if I wanted to talk about the dog's properties, I would go and
• 01:56 say, let me see if I'm gonna link to just the dog I would say Dog.
• 02:01 If I'm gonna link to the dog's height I would say Dog.Height and
• 02:04 then I could potentially set it equal to something.
• 02:07 Dog.Height equals 170 centimeters.
• 02:11 If I wanted to invoke a method for that dog I would say something like Dog.Eat.
• 02:15 But in this particular case I may also have the ability to tell it what I
• 02:20 want to eat, so in this case between the brackets I would throw in a parameter
• 02:23 called "Pork Chops," that's what I would tell it, Dog.Eat ("Pork Chops.").
• 02:27 Now, this is gonna make no sense right now, but
• 02:29 it's on the slide in case you look back at it in a year when you
• 02:32 really understand what's going on, but the referencing that I'm showing you above
• 02:37 assumes that this object is contained in a variable and is not part of a collection.
• 02:40 If it were, we would actually look at it slightly differently.
• 02:46 One thing I want to make really clear on this when I was originally
• 02:49 learning how to program myself, I ended up exchanging e-mails with a fellow, and
• 02:53 I asked him how are you so good at this?
• 02:55 And he says, you know what?
• 02:55 He says, one day I just woke up and I understood the object model.
• 02:58 And I thought, man I can't wait until that day happens for me.
• 03:01 And it does.
• 03:01 Just one day, you wake up and you go, that's what it is.
• 03:05 I worked for two years programming without understanding this.
• 03:08 So if you don't get this today, it's not the end of the world.
• 03:10 It's just background to be aware of.
• 03:13 When you do get it, it's gonna really open up some doors for you.
• 03:16 There's another piece of the object model that I wanna talk about, and
• 03:19 that's the fact that there's a parent/child relationship
• 03:22 all the way down the object model.
• 03:25 Now, in an object model, every parent can have multiple child objects.
• 03:31 Which makes sense.
• 03:33 What's different between this and
• 03:35 real life, though, is that every child can have only one parent.
• 03:39 It's not allowed to have two parents.
• 03:40 It's only allowed to have one.
• 03:42 Okay?
• 03:43 So, if we were to go and say let's expand our dog's object model.
• 03:47 And we're gonna add a puppy to it, a child object, the object model would end up
• 03:52 showing up somewhat like this, so we got our dog, and we got regular properties and
• 03:57 underneath that dog there is another object called the puppy and
• 04:00 there is a collection potentially of different puppies there.
• 04:03 Each of these puppies has its own properties, its own methods and
• 04:07 potentially even its own events.
• 04:09 If I were going to use code to return a specific Information for my puppy.
• 04:16 What I would do is I would need to say, well, my dog can have multiple puppies, so
• 04:20 I need to find a way to identify which puppy I'm talking to.
• 04:23 So that's gonna be puppy, and then in brackets, which one.
• 04:27 That could be an index number, puppy 1, puppy 2, puppy 3.
• 04:30 Or it could be another way of referring to it like by the name.
• 04:32 So Puppy ("Name").Height.
• 04:34 If I wanted to set that to 105 centimeters, I would say,
• 04:37 Puppy ("Name").Height equals 105 centimeters.
• 04:40 Or if it was kept in inches, equals, whatever, 35 inches,
• 04:43 whatever the height is.
• 04:46 If I wanted to do something even more bound,
• 04:50 I would do what's called a fully qualified reference where I would actually say
• 04:54 I want to talk to the parent first, and then talk to the child.
• 04:57 In that case, we could say, it's Dog.Name, so
• 05:00 Fido.Puppy whatever the puppy's name is, Fido Junior.Height.
• 05:05 This makes sure that I am actually getting to the exact puppy for
• 05:09 that specific dog, okay?
• 05:10 So this is how the object model actually works together.
• 05:13 We chain everything together with periods,
• 05:16 from the highest level object all the way down to the child, through the children,
• 05:20 to get to the individual properties that we're actually looking for.

Lesson notes are only available for subscribers.