In this video we're going to be talking about the workbook objects you can interact with and excels
VBA.
There are a few different ways that we can reference workbooks in the code.
The first method is to use the workbook number.
This is mainly applicable when you have multiple workbooks open and you want to read new code across
all of them.
Or if you want to get the names of all of the open workbooks it isn't as accurate as calling the workbook
out by its specific name but it is a good way to address situations where you may not initially know
all of the workbook names.
The second method is to reference the workbook by its name directly which is the most common method
you'll use in VBA.
So this is the main one that you're gonna want to pay attention to.
You can also use this workbook or active workbook this workbook will reference the workbook that your
macro code lives in which can be a good way to toggle back and forth if you're using your macro enabled
workbook as a central dashboard file active workbook simply refers to the active workbook.
So if you just want to be sure that whatever code follows will be definitely run on the active workbook.
This would be a good option.
Again nine times out of ten you're going to use the workbook name method and there's a lot of different
things that you do work with workbooks you can see the actions you can perform on workbooks by going
to the full object library and highlighting the workbooks class or looking it up on Microsoft's help
site but I've outlined some of the key actions you might might use here and this list will pretty much
cover everything that you'll be doing with workbooks in VBA.
So first we can activate a particular workbook.
So this is helpful for bouncing between several different open workbooks to perform actions.
If you have a lot of workbooks open you can use count to find out exactly how many of the workbook objects
VBA can identify.
You can also protect or protect your workbooks directly from VBA including applying a password to them
which can be very helpful.
We can also do general actions like adding a new workbook saving it opening it closing it.
So there's lots of things that we can do with workbooks from Visual Basic.
Now objects also have properties assigned to them and those are properties that we can read from VBA
and we can use them in our code.
So for workbooks for example we might be interested in the workbooks file name or in the file path of
the particular workbook that we have open.
We might want to check things like if the workbook is read only.
So again we can find all of those properties on Microsoft's help site but typically you're only ever
going to use the name or file path related properties.
So now we can go ahead and open up VBA and put some of this into practice.
So I'm going to go ahead and go over to my developer tab if you click on Visual Basic to open up my
visual basic window and we can see I've already inserted a module I've inserted a whole bunch of code
here for us to walk through.
So I'll give you a second to pause the video if you want to type out this code and follow along.
Otherwise as I go through this I'll I'll talk through what all of these inputs are and what they mean
in terms of VBA.
So I'll give you like to pause and that should have an enough time to pause and copy the code over if
you want to.
Otherwise we can just follow along line by line.
So all I've done here is I've created a public sub called Test codes that I can run this from anywhere
in my workbook and now I've used the first method of referencing a workbook which is referencing it
by the number.
And again this is a little bit risky if we're trying to reference it by number if we have multiple workbooks
open at the same times could cause a problem for us.
We can also note that we've talked about following the object hierarchy before we can see here I haven't
typed application.
So it's a little bit risky if I were to open access or something in the middle of this code.
But right now I'm going to let VBA assume that I'm working in the Excel application.
So I've typed workbooks one which is going to reference this example workbook since it's the only workbook
that I have opened right now.
And then I've typed dot protect.
So again this little period right here tells VBA This is my object I'm moving further into my heart
right now so I'm about to tell you what I want to do with this object and I want to protect it.
Now when we use the.
Protect the protect action on a workbook you can see these are the inputs.
You have a password to input a structure and put any windows input.
The password is just what password you want to use to protect her and protect the file.
So I've just used the word password here and we can see we've surrounded it in quotes because it's a
string of text.
So this denotes two VBA that password is a string of text it's not an action or anything I'm trying
to do.
And then the structure and when those inputs are both true or false inputs and it just tells the system
whether or not we want to protect the structure of the file in the windows of the file.
Nine times out of ten you'll probably use true for both of those so this line all in all should password
protect this file that we have open right now.
This example file the next line saves the example file that we have open and we can see I've used the
same reference to workbook one this third line is gonna be a little bit trickier.
So I'm going to save this workbook that I have opened this example workbook.
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.
So it's important to note that when we say things as a new file it's important that we include the file
path all the way out to the file name.
So in this case the file name is going to be new example and everything that comes before that is the
file path and the the easiest way to get the file path is to just open up wherever folder you want to
keep this information in right click on the folder up at the top of the screen and click copy.
Address as text.
And when we come back in here we can paste that address and see it show up as text.
You'll notice the only difference between this and this is that we've had to add this extra backslash
into the code to designate that we've entered the example folder here and now we're naming the file
so file name when we deal with VBA is often referring to the full file path and the file name.
So here I have my full file path in my file name and this is being given to the file name input.
The next thing we have is the file format input and we can see this.
This bit of code here this Excel open exe email workbook macro enabled.
It's a really long string of code and this represents something in VBA.
In this case it represents the code that are saved as action needs to save this file as a workbook that
is macro enabled which is a little bit different than a workbook that's just a general work but that
doesn't have any code in it.
And so you can find this full list of file formatting codes on Microsoft's Web site or in the object
library.
But the key ones that you'll usually use are back row enabled and default workbook which I'll show you
later.
Now we can see there's a couple of different password inputs down here and I'd love both of those blank
but we'll talk through them so password would be if you're just password protecting your file so the
user won't be able to access the file at all unless they know the password right Red's password will
let the user access the file as it read only file if they don't know the password but you'll have to
know the password to get into the file and make any edits and then this this input that I've given as
true is for read only recommended.
What that means is that when the user opens the file VBA is going to recommend or Excel is going to
recommend that they open the file as a read only file and the user can choose not to do that.
They can choose to go into file and edit the file instead.
And if they do so it'll let them into the file without entering a password at all.
So there's a bunch of other inputs we can we could do here we can create a backup file.
We can change things about the way that the file is going to look or behave but usually you're only
going to use these front couple of inputs here so all in all this line should save this example file
as a new workbook called new example should be macro enabled and it should recommend to us that we open
as read only so we move into this next set of lines here so all these lines that represent here are.
I've created an integer variable C and I told the system for C I just want you to count all of the workbooks
that I have open right now and we suspect that this should probably be one because I should only have
my example workbook open when I run this code we'll show this in a message box later so we'll go ahead
and move on.
So I mentioned before that it can be a bit tedious to keep typing out the object hierarchy all the time
so typing out application workbooks parentheses example and then dot and whatever we want to do with
our workbook.
So instead one kind of shortcut we can take to dodge having to type all of that out every time is to
create a workbook variable.
So this line here creates a variable I've called it WB 1 as a workbook and so a workbook is a specific
type of variable that we can use that will essentially represent everything up until this period here.
So everything that comes before this and we can save that as or input that variable however we want
to input that variable.
So in this case I want to set this variable equal to my new example workbook which is what I will have
just saved this as.
So it's important to note that with workbook variables or worksheet variables any type of object variable
that you're creating.
You don't just type WB 1 equals and then whatever you want to set it equal to use the set command here
set WB 1 equal to workbooks new example.
So that's going to be anytime you use object to that sort of thing you're gonna want to use set instead
of just the equal command.