1
00:00:01,130 --> 00:00:06,830
In this video, we're going to talk about what objects are in VBA and the corresponding object hierarchy

2
00:00:06,860 --> 00:00:10,640
that we should always pay attention to while we're coding in VBA.

3
00:00:10,670 --> 00:00:15,740
We often reference things called objects, objects, or essentially the different things you might interact

4
00:00:15,740 --> 00:00:18,680
with from your code like worksheets or tables.

5
00:00:19,010 --> 00:00:22,280
And they live in an object hierarchy that we have to pay attention to.

6
00:00:22,790 --> 00:00:27,890
The object hierarchy starts with Excel itself, which is the application object.

7
00:00:28,340 --> 00:00:33,260
The application object in this case, Excel contains other objects that we can interact with.

8
00:00:33,590 --> 00:00:37,100
For example, workbooks would be the next object in our hierarchy.

9
00:00:37,370 --> 00:00:39,890
And workbooks are your actual Excel files.

10
00:00:40,190 --> 00:00:42,980
So if you have several Excel files open at once.

11
00:00:43,040 --> 00:00:48,190
We can use VBA to interact with all of them, even if they don't have embedded macros because they're

12
00:00:48,200 --> 00:00:49,550
objects that we can reference.

13
00:00:50,270 --> 00:00:54,380
After workbooks comes worksheets, which are obviously the individual sheets that you would have in

14
00:00:54,380 --> 00:00:58,970
your workbooks and sheets, have a lot of different object types that we can interact with, ranges,

15
00:00:58,970 --> 00:01:02,420
sells tables, and all of those have different things that we can do.

16
00:01:02,440 --> 00:01:04,880
So this object hierarchy continues downwards.

17
00:01:05,550 --> 00:01:09,320
So you can see here I've written how we would reference each of these objects in VBA.

18
00:01:10,040 --> 00:01:11,360
Application is pretty simple.

19
00:01:11,360 --> 00:01:13,970
It's just application for workbooks.

20
00:01:14,000 --> 00:01:17,780
We would enter the name of the workbook that we want to look at for worksheets.

21
00:01:17,870 --> 00:01:21,710
You can either use the name of the worksheet or the number of the worksheet if your sheets are always

22
00:01:21,710 --> 00:01:25,790
in the same order and so on and so forth, all the way down through the object hierarchy.

23
00:01:26,000 --> 00:01:30,650
And there will be videos that dive into each of these object types a little deeper and how to use them

24
00:01:30,650 --> 00:01:31,430
in VBA.

25
00:01:32,240 --> 00:01:36,440
Now, the other thing to note is that there are different things we can do with each of these objects.

26
00:01:36,740 --> 00:01:42,530
The way we interact with the Excel application object, for example, might be to tell a system to stop

27
00:01:42,580 --> 00:01:47,450
Diplock displaying alert messages or stop trying to update the screen graphics.

28
00:01:47,690 --> 00:01:52,190
But when we interact with the workbook object, our actions might be to save or open a new file.

29
00:01:52,670 --> 00:01:57,950
So the important thing to note about the hierarchy is that we have to walk VBA through the hierarchy

30
00:01:57,950 --> 00:02:01,190
in the correct order in order to get what we want out of it.

31
00:02:01,700 --> 00:02:06,950
For example, if we wanted to retrieve data from a specific cell in our Excel file, let's say this

32
00:02:06,950 --> 00:02:10,910
highlighted cell over here, we would need to tell VBA first.

33
00:02:11,240 --> 00:02:13,130
We are in the Excel application.

34
00:02:13,580 --> 00:02:16,970
Second, I want to look at this workbook called example.

35
00:02:17,510 --> 00:02:22,350
Third, I want to look at the object hierarchy sheets specifically and last.

36
00:02:22,410 --> 00:02:26,360
I am interested in cell two eight, which means row two.

37
00:02:26,420 --> 00:02:27,200
Column eight.

38
00:02:27,350 --> 00:02:27,950
In this case.

39
00:02:28,010 --> 00:02:28,980
So each two.

40
00:02:29,910 --> 00:02:34,430
And so you can see what this would look like if we spit it out into a full Excel statement.

41
00:02:34,880 --> 00:02:39,860
Now, that period that goes after each object, it tells VBA that we are about to move another step

42
00:02:39,860 --> 00:02:41,270
further into the hierarchy.

43
00:02:41,570 --> 00:02:46,490
So we start with the application and the period tells VBA we're about to move into the second layer

44
00:02:46,490 --> 00:02:47,340
of the hierarchy.

45
00:02:47,360 --> 00:02:48,860
In this case, the workbooks.

46
00:02:49,090 --> 00:02:51,890
And we reference this workbook that we're in now, which is example.

47
00:02:52,370 --> 00:02:55,630
So this is what the full statement would look like in VBA.

48
00:02:55,640 --> 00:02:59,690
And this is what we would type if we wanted to reference this particular cell we have highlighted in

49
00:02:59,690 --> 00:03:00,110
yellow.

50
00:03:00,530 --> 00:03:03,740
We can't move any of these objects to different places in the hierarchy.

51
00:03:03,800 --> 00:03:06,830
They have to fall out from top to bottom like we have here.

52
00:03:07,160 --> 00:03:11,930
So that means we can't start by telling Excel we want to see what's in cell two eight and then start

53
00:03:11,930 --> 00:03:14,510
backpedaling to say and it's in the example workbook.

54
00:03:14,540 --> 00:03:18,020
And I want to use Excel and I want to use the sheet in particular.

55
00:03:18,350 --> 00:03:20,300
We have to follow this object hierarchy.

56
00:03:20,870 --> 00:03:24,050
And this can get a bit tedious when we're doing a lot of heavy data manipulation.

57
00:03:24,350 --> 00:03:26,600
So there are ways that we can skip some of these steps.

58
00:03:26,660 --> 00:03:31,220
And if we cut out the earlier steps in the process, let's say we get rid of the application and the

59
00:03:31,220 --> 00:03:35,360
workbook steps and we start with our worksheet and then specify the cell.

60
00:03:35,780 --> 00:03:41,240
VBA will assume the application and workbook or whatever is active at that moment in time.

61
00:03:41,690 --> 00:03:47,000
And what this means is that VBA will assume we're using Excel since we have Excel Open and we'll assume

62
00:03:47,000 --> 00:03:52,250
that we're using the example workbook because that's what I have open right now that I'm clicked into.

63
00:03:52,790 --> 00:03:57,800
If I open another Excel file and put my cursor in that second Excel file, then the example workbook

64
00:03:57,800 --> 00:04:01,490
here would be inactive, but we would still be in the Excel application.

65
00:04:01,970 --> 00:04:07,040
If I open something like an access database, then we wouldn't even be in the Excel application anymore.

66
00:04:07,550 --> 00:04:12,140
So you can see here I have sheets down at the bottom and this is a pretty good example of what is active

67
00:04:12,140 --> 00:04:12,860
and what is not.

68
00:04:13,220 --> 00:04:14,630
I have three sheets down here.

69
00:04:15,470 --> 00:04:18,950
Object hierarchy, workbook objects and worksheet objects.

70
00:04:19,250 --> 00:04:23,540
So if I click on Object Hierarchy like I am now, we can see that the sheet is highlighted.

71
00:04:23,660 --> 00:04:25,480
It's clear that this is the sheet that I'm on.

72
00:04:25,490 --> 00:04:26,780
This is my active sheet.

73
00:04:27,260 --> 00:04:31,340
If I click onto one of these sheets, you can see this becomes highlighted and this is my active sheet

74
00:04:31,730 --> 00:04:36,490
and I can toggle back and forth between these sheets to make one active and the other two inactive.

75
00:04:37,190 --> 00:04:39,920
So we can take shortcuts when we're working with our object hierarchies.

76
00:04:40,160 --> 00:04:44,120
But it's important that we understand how those shortcuts are impacting our code.

77
00:04:44,420 --> 00:04:49,040
And there are some tic tips and tricks that we can use to make it easier to reference our full object

78
00:04:49,040 --> 00:04:49,610
hierarchy.

79
00:04:49,940 --> 00:04:53,090
If we're worried about introducing risk or error into our code.

80
00:04:53,360 --> 00:04:56,780
So we'll talk through all of that as we talk about these objects in VBA.

81
00:04:57,440 --> 00:05:00,050
So in this video, we looked at an overview of the object.

82
00:05:00,570 --> 00:05:01,950
And how it impacts our code.

83
00:05:02,190 --> 00:05:05,370
So now we're ready to take a closer look at each of the object types.