1
00:00:00,920 --> 00:00:05,930
In this video we're going to be talking about the workbook objects you can interact with and excels

2
00:00:05,930 --> 00:00:07,090
VBA.

3
00:00:07,100 --> 00:00:10,330
There are a few different ways that we can reference workbooks in the code.

4
00:00:10,340 --> 00:00:13,250
The first method is to use the workbook number.

5
00:00:13,430 --> 00:00:18,050
This is mainly applicable when you have multiple workbooks open and you want to read new code across

6
00:00:18,140 --> 00:00:19,010
all of them.

7
00:00:19,010 --> 00:00:24,080
Or if you want to get the names of all of the open workbooks it isn't as accurate as calling the workbook

8
00:00:24,080 --> 00:00:29,210
out by its specific name but it is a good way to address situations where you may not initially know

9
00:00:29,210 --> 00:00:31,430
all of the workbook names.

10
00:00:31,430 --> 00:00:35,720
The second method is to reference the workbook by its name directly which is the most common method

11
00:00:35,720 --> 00:00:36,800
you'll use in VBA.

12
00:00:36,800 --> 00:00:39,860
So this is the main one that you're gonna want to pay attention to.

13
00:00:39,920 --> 00:00:45,320
You can also use this workbook or active workbook this workbook will reference the workbook that your

14
00:00:45,320 --> 00:00:50,450
macro code lives in which can be a good way to toggle back and forth if you're using your macro enabled

15
00:00:50,450 --> 00:00:57,030
workbook as a central dashboard file active workbook simply refers to the active workbook.

16
00:00:57,040 --> 00:01:02,900
So if you just want to be sure that whatever code follows will be definitely run on the active workbook.

17
00:01:02,900 --> 00:01:04,260
This would be a good option.

18
00:01:04,340 --> 00:01:09,140
Again nine times out of ten you're going to use the workbook name method and there's a lot of different

19
00:01:09,140 --> 00:01:14,090
things that you do work with workbooks you can see the actions you can perform on workbooks by going

20
00:01:14,090 --> 00:01:19,220
to the full object library and highlighting the workbooks class or looking it up on Microsoft's help

21
00:01:19,220 --> 00:01:25,310
site but I've outlined some of the key actions you might might use here and this list will pretty much

22
00:01:25,310 --> 00:01:28,550
cover everything that you'll be doing with workbooks in VBA.

23
00:01:28,580 --> 00:01:30,710
So first we can activate a particular workbook.

24
00:01:30,710 --> 00:01:35,760
So this is helpful for bouncing between several different open workbooks to perform actions.

25
00:01:35,810 --> 00:01:40,400
If you have a lot of workbooks open you can use count to find out exactly how many of the workbook objects

26
00:01:40,400 --> 00:01:42,060
VBA can identify.

27
00:01:42,060 --> 00:01:46,940
You can also protect or protect your workbooks directly from VBA including applying a password to them

28
00:01:46,940 --> 00:01:48,760
which can be very helpful.

29
00:01:48,770 --> 00:01:54,370
We can also do general actions like adding a new workbook saving it opening it closing it.

30
00:01:54,980 --> 00:01:59,100
So there's lots of things that we can do with workbooks from Visual Basic.

31
00:01:59,450 --> 00:02:04,390
Now objects also have properties assigned to them and those are properties that we can read from VBA

32
00:02:04,400 --> 00:02:06,180
and we can use them in our code.

33
00:02:06,230 --> 00:02:13,010
So for workbooks for example we might be interested in the workbooks file name or in the file path of

34
00:02:13,070 --> 00:02:15,370
the particular workbook that we have open.

35
00:02:15,380 --> 00:02:18,260
We might want to check things like if the workbook is read only.

36
00:02:18,350 --> 00:02:22,850
So again we can find all of those properties on Microsoft's help site but typically you're only ever

37
00:02:22,850 --> 00:02:26,090
going to use the name or file path related properties.

38
00:02:26,090 --> 00:02:30,380
So now we can go ahead and open up VBA and put some of this into practice.

39
00:02:30,380 --> 00:02:35,870
So I'm going to go ahead and go over to my developer tab if you click on Visual Basic to open up my

40
00:02:35,870 --> 00:02:42,960
visual basic window and we can see I've already inserted a module I've inserted a whole bunch of code

41
00:02:42,960 --> 00:02:44,310
here for us to walk through.

42
00:02:44,340 --> 00:02:50,620
So I'll give you a second to pause the video if you want to type out this code and follow along.

43
00:02:50,820 --> 00:02:57,750
Otherwise as I go through this I'll I'll talk through what all of these inputs are and what they mean

44
00:02:57,750 --> 00:02:59,280
in terms of VBA.

45
00:02:59,340 --> 00:03:04,390
So I'll give you like to pause and that should have an enough time to pause and copy the code over if

46
00:03:04,390 --> 00:03:05,180
you want to.

47
00:03:05,200 --> 00:03:07,420
Otherwise we can just follow along line by line.

48
00:03:07,450 --> 00:03:12,670
So all I've done here is I've created a public sub called Test codes that I can run this from anywhere

49
00:03:12,670 --> 00:03:18,280
in my workbook and now I've used the first method of referencing a workbook which is referencing it

50
00:03:18,280 --> 00:03:19,220
by the number.

51
00:03:19,270 --> 00:03:24,400
And again this is a little bit risky if we're trying to reference it by number if we have multiple workbooks

52
00:03:24,400 --> 00:03:27,160
open at the same times could cause a problem for us.

53
00:03:27,160 --> 00:03:33,550
We can also note that we've talked about following the object hierarchy before we can see here I haven't

54
00:03:33,550 --> 00:03:34,730
typed application.

55
00:03:35,020 --> 00:03:39,540
So it's a little bit risky if I were to open access or something in the middle of this code.

56
00:03:39,850 --> 00:03:44,830
But right now I'm going to let VBA assume that I'm working in the Excel application.

57
00:03:44,830 --> 00:03:49,300
So I've typed workbooks one which is going to reference this example workbook since it's the only workbook

58
00:03:49,330 --> 00:03:50,430
that I have opened right now.

59
00:03:50,950 --> 00:03:53,070
And then I've typed dot protect.

60
00:03:53,110 --> 00:03:59,930
So again this little period right here tells VBA This is my object I'm moving further into my heart

61
00:03:59,990 --> 00:04:05,020
right now so I'm about to tell you what I want to do with this object and I want to protect it.

62
00:04:05,020 --> 00:04:05,880
Now when we use the.

63
00:04:05,920 --> 00:04:11,250
Protect the protect action on a workbook you can see these are the inputs.

64
00:04:11,260 --> 00:04:15,400
You have a password to input a structure and put any windows input.

65
00:04:15,400 --> 00:04:19,900
The password is just what password you want to use to protect her and protect the file.

66
00:04:19,900 --> 00:04:24,670
So I've just used the word password here and we can see we've surrounded it in quotes because it's a

67
00:04:24,670 --> 00:04:25,690
string of text.

68
00:04:25,720 --> 00:04:31,030
So this denotes two VBA that password is a string of text it's not an action or anything I'm trying

69
00:04:31,030 --> 00:04:31,880
to do.

70
00:04:32,200 --> 00:04:37,330
And then the structure and when those inputs are both true or false inputs and it just tells the system

71
00:04:37,330 --> 00:04:41,560
whether or not we want to protect the structure of the file in the windows of the file.

72
00:04:41,590 --> 00:04:48,050
Nine times out of ten you'll probably use true for both of those so this line all in all should password

73
00:04:48,050 --> 00:04:50,570
protect this file that we have open right now.

74
00:04:50,570 --> 00:04:57,310
This example file the next line saves the example file that we have open and we can see I've used the

75
00:04:57,310 --> 00:05:03,120
same reference to workbook one this third line is gonna be a little bit trickier.

76
00:05:03,400 --> 00:05:07,730
So I'm going to save this workbook that I have opened this example workbook.

77
00:05:07,750 --> 00:05:13,760
We can see I've referenced it by name now instead of by number and I'm going to save this as a new file.

78
00:05:13,900 --> 00:05:18,760
So it's important to note that when we say things as a new file it's important that we include the file

79
00:05:18,760 --> 00:05:21,160
path all the way out to the file name.

80
00:05:21,190 --> 00:05:26,260
So in this case the file name is going to be new example and everything that comes before that is the

81
00:05:26,710 --> 00:05:33,490
file path and the the easiest way to get the file path is to just open up wherever folder you want to

82
00:05:33,490 --> 00:05:39,430
keep this information in right click on the folder up at the top of the screen and click copy.

83
00:05:39,430 --> 00:05:41,250
Address as text.

84
00:05:41,470 --> 00:05:46,880
And when we come back in here we can paste that address and see it show up as text.

85
00:05:46,960 --> 00:05:53,590
You'll notice the only difference between this and this is that we've had to add this extra backslash

86
00:05:53,620 --> 00:05:59,170
into the code to designate that we've entered the example folder here and now we're naming the file

87
00:06:01,150 --> 00:06:07,450
so file name when we deal with VBA is often referring to the full file path and the file name.

88
00:06:07,540 --> 00:06:13,760
So here I have my full file path in my file name and this is being given to the file name input.

89
00:06:13,900 --> 00:06:17,740
The next thing we have is the file format input and we can see this.

90
00:06:17,740 --> 00:06:23,710
This bit of code here this Excel open exe email workbook macro enabled.

91
00:06:23,710 --> 00:06:27,250
It's a really long string of code and this represents something in VBA.

92
00:06:27,250 --> 00:06:35,020
In this case it represents the code that are saved as action needs to save this file as a workbook that

93
00:06:35,020 --> 00:06:39,820
is macro enabled which is a little bit different than a workbook that's just a general work but that

94
00:06:39,820 --> 00:06:41,170
doesn't have any code in it.

95
00:06:41,350 --> 00:06:48,040
And so you can find this full list of file formatting codes on Microsoft's Web site or in the object

96
00:06:48,040 --> 00:06:48,980
library.

97
00:06:49,180 --> 00:06:54,460
But the key ones that you'll usually use are back row enabled and default workbook which I'll show you

98
00:06:54,460 --> 00:06:54,810
later.

99
00:06:56,080 --> 00:07:01,180
Now we can see there's a couple of different password inputs down here and I'd love both of those blank

100
00:07:01,240 --> 00:07:06,310
but we'll talk through them so password would be if you're just password protecting your file so the

101
00:07:06,310 --> 00:07:11,550
user won't be able to access the file at all unless they know the password right Red's password will

102
00:07:11,560 --> 00:07:17,230
let the user access the file as it read only file if they don't know the password but you'll have to

103
00:07:17,230 --> 00:07:22,630
know the password to get into the file and make any edits and then this this input that I've given as

104
00:07:22,630 --> 00:07:24,950
true is for read only recommended.

105
00:07:25,060 --> 00:07:29,890
What that means is that when the user opens the file VBA is going to recommend or Excel is going to

106
00:07:29,890 --> 00:07:34,890
recommend that they open the file as a read only file and the user can choose not to do that.

107
00:07:34,900 --> 00:07:37,810
They can choose to go into file and edit the file instead.

108
00:07:37,990 --> 00:07:43,540
And if they do so it'll let them into the file without entering a password at all.

109
00:07:43,600 --> 00:07:47,630
So there's a bunch of other inputs we can we could do here we can create a backup file.

110
00:07:47,890 --> 00:07:52,690
We can change things about the way that the file is going to look or behave but usually you're only

111
00:07:52,690 --> 00:08:00,350
going to use these front couple of inputs here so all in all this line should save this example file

112
00:08:00,740 --> 00:08:05,660
as a new workbook called new example should be macro enabled and it should recommend to us that we open

113
00:08:05,810 --> 00:08:14,280
as read only so we move into this next set of lines here so all these lines that represent here are.

114
00:08:14,290 --> 00:08:20,290
I've created an integer variable C and I told the system for C I just want you to count all of the workbooks

115
00:08:20,290 --> 00:08:25,630
that I have open right now and we suspect that this should probably be one because I should only have

116
00:08:25,630 --> 00:08:30,280
my example workbook open when I run this code we'll show this in a message box later so we'll go ahead

117
00:08:30,280 --> 00:08:31,590
and move on.

118
00:08:34,900 --> 00:08:41,680
So I mentioned before that it can be a bit tedious to keep typing out the object hierarchy all the time

119
00:08:41,710 --> 00:08:47,410
so typing out application workbooks parentheses example and then dot and whatever we want to do with

120
00:08:47,410 --> 00:08:48,210
our workbook.

121
00:08:48,340 --> 00:08:55,060
So instead one kind of shortcut we can take to dodge having to type all of that out every time is to

122
00:08:55,060 --> 00:08:57,250
create a workbook variable.

123
00:08:57,250 --> 00:09:04,180
So this line here creates a variable I've called it WB 1 as a workbook and so a workbook is a specific

124
00:09:04,180 --> 00:09:09,620
type of variable that we can use that will essentially represent everything up until this period here.

125
00:09:09,700 --> 00:09:14,890
So everything that comes before this and we can save that as or input that variable however we want

126
00:09:14,900 --> 00:09:15,850
to input that variable.

127
00:09:16,390 --> 00:09:22,540
So in this case I want to set this variable equal to my new example workbook which is what I will have

128
00:09:22,540 --> 00:09:24,510
just saved this as.

129
00:09:24,520 --> 00:09:30,370
So it's important to note that with workbook variables or worksheet variables any type of object variable

130
00:09:30,370 --> 00:09:31,490
that you're creating.

131
00:09:31,630 --> 00:09:40,330
You don't just type WB 1 equals and then whatever you want to set it equal to use the set command here

132
00:09:40,750 --> 00:09:44,280
set WB 1 equal to workbooks new example.

133
00:09:44,290 --> 00:09:47,950
So that's going to be anytime you use object to that sort of thing you're gonna want to use set instead

134
00:09:47,950 --> 00:09:49,390
of just the equal command.