So we've spent some time in the past talking about the object hierarchy and how it's important that
we follow and pay attention to the object hierarchy.
But it can be a little bit frustrating and repetitive to have to continually type out the entire object
hierarchy anytime you want to reference something simple, like a font property of a cell.
And so there's a little bit of a way around that that can help you cut some corners and make sure that
you get the stability in your code without having to retype the entire line.
So to revisit the object hierarchy really quick, we covered this in a previous video and we learned
about how VBA actually expects you to step through each one of these levels of the object hierarchy
every time you reference something in your file.
So the value of a particular cell, the font color of a particular.
So you should be stepping through all of these steps.
And we know we can skip some of these steps and excel.
We'll make some assumptions that will give its best guess to fill in that information that we skipped.
And this always introduces some level of risk to our code.
And we've talked before about how dropping off something like the application is probably less risky
than leaving something off like the worksheet.
So the further down this object hierarchy we get, the more risky it becomes to leave objects off of
the object hierarchy.
But with all of that being said, it's really tedious to have to retype the entire object hierarchy.
Anytime I want to reference anything.
So this is what the four object hierarchy line would look like.
Starts with application, specifies what workbook I'm looking at.
It specifies which sheet I'm looking at.
And then it tells me this is a cell that I want to look at and I want to get the value out of that.
So so this would be really tedious to have to type if you had to work with, say, 30 cells or if you
wanted to do you wanted to pull the value out of that.
So but you also wanted to know it's font properties.
And this is where with and with comes in with and with kind of helps you step through the entire object
hierarchy to ensure that your code is still stable and still accurate.
But you don't have to type out this whole line every time.
Essentially lets you cut in at any of these points and continue to work after that point without having
to retype everything that came before it.
So looking at this example, everything between this with an end, with all of this text and read here
is going to apply to this text in green.
Now, what that means is I can specify anything in that text in green up to a certain point in my object
hierarchy, and then I don't need to retype that information anymore.
So in this particular example, I know that I want to work with my worksheets called my worksheet.
I want to look at the cell in row one, column two of that worksheet.
And specifically, I want to look at the font properties of that so that I just specified.
But I don't want to have to retype this every time I reference that font property.
So instead, I put this in with and with all of this red text applies directly to the end of this statement.
So what this really means is this dot bold equals true is actually representative of worksheets.
My worksheet sells in row one column to dot font dot bold equals true and my color really means worksheets,
cels, font property dot color equals V.V. Blue.
So this is essentially cut in at the end of my font property here.
And instead of having to retype this, this text in green, every time I want to reference a thought
property, I can actually just tag on that farm property to the end of this pre-existing statement that
I've determined with my with and with statement.
So in this video we talked about, with and with which help us narrow down our obiter hierarchy without
having to retype it every single time.
So this provides stability.
It provides accuracy in our code.
And it saves us some time and effort and having to continually type out the full object hierarchy.
So this is a nice little shortcut to use anytime you want to make sure you're referencing the correct
cell and the correct properties, but you don't want to have to retype the full hierarchy every single
time.
So this is a really strong tool to use.
And Microsoft, VBA.
And next, we'll take a look at an example of how we might apply this.