In this video we'll be looking at the worksheet object and how we can use it in our code.
Like with workbooks there are a few different ways we can reference worksheets in Excel and versus by
referencing the number of the worksheet which is helpful for looping through all of the sheets or if
we don't know the sheet names but generally speaking it's best practice to reference the worksheet directly
using its name which is the second reference method.
The third method is referencing the currently active sheet similar to how we can use active workbook.
The last reference method which is plain sheets can be used to refer to any sheet type in Excel not
just worksheets worksheets or what Excel defaults to when you add new sheets is what you're used to
seeing these sheets with lots of cells that we can enter data into a chart sheet is the second most
common sheet type and it contains a chart but none of the data it's different than embedding a chart
onto your worksheet like most people do.
You can also have dialog and macro sheets but these are almost never used.
You can see these options by right clicking on your sheet and selecting insert and we can see here.
We have a couple of different options worksheet chart macro dialog.
If we insert a chart sheet just to see we can see this is a completely blank sheet.
There are no there's no data there's no cells or anything it is just this chart.
So we'll go ahead and delete this and go back to our worksheet here.
Now we can activate and count worksheets just like we can with workbooks.
We can also protect or protect individual sheets like we could with workbooks.
And that's a super helpful tool to use with VBA.
You can add sheets you can delete sheets you can copy and paste them all of which is again useful to
do in VBA.
And again you can see the full list of interactions you can use with worksheets.
If you go to Microsoft's help site or look up the worksheet object in the object library.
These are the most common properties that you'll probably interact with using the worksheet object.
First is the sheet name which is pretty obvious it gets you the name of the sheet.
The second is the list objects property and this will let you reference objects such as tables that
might appear on the sheet.
The row property you can use to delete insert or account rows on the sheet in the columns property lets
you delete insert or count the columns on the sheet all of which is really helpful to do so now that
we understand a little bit about how the worksheet object works.
We can actually go and apply some of these actions and interactions in VBA.
So I'll go to my developer tab click Visual Basic to open up my visual basic window and you can see
here I've already inserted a module in this case it's module 2 because I had an existing module already
and I've gone ahead and I've written some code so I'll give you a second to pause and copy this code
if you want to and that should have been enough time so now I'm going to walk through this line by line.
So the first thing I did was create an integer variable C and I'm going to use C to store the count
of my worksheet.
So I would expect the system to count however many worksheets I have in my active workbook which should
be this example workbook it's the only workbook I have open and it should return that C as a message
box so I would expect this to read in this case 3 because I only have three sheets in my workbook.
Now I'm going to use the same trick I used in the workbook video.
We can actually create a worksheet variable type which I've called W us and census and object type variable
we need to use set when we decide what this variable stands for.
And in this case I've gone ahead and I've specified my workbook so we know even if I have other workbooks
open my w s variable here is always going to represent this workbooks and then in this worksheet so
in this case I've told it to look at the worksheet objects.
So if I go into my file here we could see worksheet objects is the name of this particular sheet so
if I come back into my VBA we can see that matches up with the name that I've inserted here.
So every time I type w us that's the sheet it's going to be referring to.
So the first thing I did was protect this W.S. sheet and I inserted a password here and if I hit space
we can take a look at this really quick.
So the password I've given it is just quote password it's a simple texturing there's nothing fancy about
it.
All of these other inputs here they are all true and false inputs and they're all pretty straightforward.
For example if we put false for allow the leading rows the user wouldn't be able to delete rows when
this sheet is password protected.
But if you put true then the user would be allowed to delete rows even if they weren't allowed to delete
the columns or to format the columns or anything like that.
One interesting property here is the user interface input and what this means is that if we put true
for this then it applies only to the user interface meaning it doesn't apply to our VBA code.
So for our macro essentially the workbook would still or the worksheet would still be unprotected.
But for the user it would be a protected worksheet.
That's a particularly useful property that you might want to pay attention to.
In this case I'm going to let all those default whatever they are usually in Excel whatever my Excel
usually defaults them to.
And I'm just going to apply a password to it which I've just typed.
Password for now I'm going to create a second worksheet and so I've gone ahead and I've created a second
worksheet variable which I'm calling W.S. 2.
And then I've set that worksheet variable equal to again the same workbook that I want to be in.
This only workbook that I have open and so I've used dot sheets dot add.
Now when you add sheet store workbook you should use these sheets type reference instead of the worksheets
reference.
And that's because we can actually specify if we wanted to add a charge sheet or work cheat sheet or
a macro sheet or a dialog sheet or whatever type of sheet we want to add.
If we don't specify at all it's just going to default to a worksheet which is fine in the scenario.
So we'll let it default.
Now I put a W S activate so again when we add this sheet the sheet that we've just added will automatically
activate.
So with W.S. activate which we should see our screen bounced back to this original worksheet that we
were working in this worksheet objects sheet and then us to activate.
So we should see everything bounce back into our W.S. to sheet which is that new sheet now just to give
a quick example.
I went ahead and created two values that should show up on our new sheet here.
The first is in row two column two.
And it should read test.
The next is in row three column three and it should read test two.
Now I created those values those cell values on the sheet so that we can see the effects of deleting
a row and deleting a column.
So this line will delete row two off of our worksheet to here which is the one we added.
And this will delete column three.
So we should see both of these values ultimately disappear as this row and this column are deleted.
The last thing I'm going to do down here I'm going to create a name variable which I'm going to pull
as the name property of the worksheet and then I'm going to display that name in a message box.
So we'll know exactly what this sheet is called.
The very last thing I'll do is copy the worksheet and if I hit space here we can see what these inputs
are.
So after you copy a worksheet you can decide to tell the system where to paste that worksheet so we
can either put it before something or we can put it after something.
In this case I've told it to go ahead and put this before my original worksheet which was this worksheet
objects sheet file here so we're gonna go ahead and delete this sheet that we had originally added it'll
probably be named something like she or sheet too.
And it's this original sheet that we added up here but we're going to leave this copy sheet which will
be like sheet one copy or sheet to copy and we're going to leave that in our file.
So we should still see the copy chief but we shouldn't see the original sheet that we had added when
we're done.
So now we're gonna go ahead and run this code and test it out make sure it's behaving properly and we're
gonna do this line by line so we can see what each of these individual statements does in VBA.
We're going to do this by using f eight on our keyboard or if you go up to debug you can you step into
right here and we can see FHA is the associated keyboard shortcut.
So I'll go in and click on this to start the macro and now I'll step through this with the FHA.
So the first thing that this line does is count our worksheets and then provide that in a message box.
So when I hit that date I should see a message box which I do.
And it tells me I have three worksheets which is what we expect we have three worksheets down here.
So we know that our worksheet count function is literally just counting the number of worksheets we
have in this particular workbook.
So I'll click OK.
Now we're gonna go ahead and set up this little shortcut that we have all referencing our worksheet
objects of sheet that is in our workbook and we're gonna go ahead and password protect that sheet.
Now when I come back to this original the worksheet object sheet later I should have to on password
protected to change anything.
The next thing we're going to do is add a sheet.
And at this point I will make these half windows so we can see the direct impact of this VBA code.
So make sure you click back into your VBA code if you just have your windows to make sure that when
you hit at 8 it is responding in VBA.
So we'll make sure we're clicked into this and hit it way and we can see we have actually added a new
sheet here in this case it's called sheet 1.
Now when I hit F eight you'll notice that right now my sheet 1 which is the sheet I just added my W.S.
to is what's active right now.
So when I hit F eight off of this line we should see my original sheet that I identified up here activate
instead which it does.
We can see we've jumped back to our worksheet objects which is what we would expect when I hit update
again.
We should jump back to that sheet we just added so that's the difference between activating your worksheets
that's the same as when we were activating workbooks before.
Same as when we manually click around on the sheets down here.
It just changes what sheet is active right now.
So I've gone ahead and have added some this these two test strings right here so we can see the effect
of the deleting of rows and columns so we'll go ahead and hit edit here and we see our test value shows
up and Selby to update again test two shows up and cell C 3 which is what we expect.
So now we're going to go ahead and delete Row 2 which means this entire row that contains this test
right here should disappear that's about how we expected.
And now we're going to delete all of column three which is this column see right here.
So this test too should disappear.
So now we know that the deleting of columns and rows behaves how we expected.
Now we're gonna go ahead and identify the name of the sheet which we can see down here should be sheet
1.
So I'll hit a date and then update again to see what shows up in this message box and it is Sheet 1.
So we know this is how the Name property of our sheet behaves it's behaving as expected.
So click OK.
Now this line should copy the sheet one that we added in here originally.
So this sheet that I have active right now.
And it should make a copy of this before my NWS which was this original worksheet.
This original worksheet objects here.
So we should see a second sheet show up like right here in our tabs.
And it should be called sheet one copy or something like that so we'll hit at 8:00 and we can see we
have Sheet 1 2 which is essentially the same thing as sheet one copy.
This is just a copy of the sheet one file.
So now when I hit at 8:00 one more time we should see this original sheet one that we have here deleted.
Now we did talk a little bit before in a previous video about how we can actually turn off these dialog
boxes.
So if you're doing a lot of deleting sheets or saving or things that might trigger these dialog boxes
you can actually turn them off and we went over that in our dialogue video a while back.
But for now we'll just go ahead and approve this request say delete and we can see it.
It did actually delete that sheet when we had done there.
And now this is the last step here I think one more time and we've completed our subroutine so now that
we have a general understanding of the worksheet object we kind of went through how to apply all of
these different properties and different interactions that we covered here and now we have a pretty
good understanding of what the worksheet object is and how to use it in VBA.