1 00:00:01,260 --> 00:00:06,840 So we've spent some time in the past talking about the object hierarchy and how it's important that 2 00:00:06,840 --> 00:00:09,540 we follow and pay attention to the object hierarchy. 3 00:00:09,930 --> 00:00:15,090 But it can be a little bit frustrating and repetitive to have to continually type out the entire object 4 00:00:15,090 --> 00:00:20,160 hierarchy anytime you want to reference something simple, like a font property of a cell. 5 00:00:20,620 --> 00:00:24,660 And so there's a little bit of a way around that that can help you cut some corners and make sure that 6 00:00:24,660 --> 00:00:29,190 you get the stability in your code without having to retype the entire line. 7 00:00:29,700 --> 00:00:35,280 So to revisit the object hierarchy really quick, we covered this in a previous video and we learned 8 00:00:35,280 --> 00:00:40,830 about how VBA actually expects you to step through each one of these levels of the object hierarchy 9 00:00:41,250 --> 00:00:43,580 every time you reference something in your file. 10 00:00:43,590 --> 00:00:47,310 So the value of a particular cell, the font color of a particular. 11 00:00:47,310 --> 00:00:50,580 So you should be stepping through all of these steps. 12 00:00:51,010 --> 00:00:53,310 And we know we can skip some of these steps and excel. 13 00:00:53,310 --> 00:00:57,480 We'll make some assumptions that will give its best guess to fill in that information that we skipped. 14 00:00:57,930 --> 00:01:01,200 And this always introduces some level of risk to our code. 15 00:01:01,660 --> 00:01:07,230 And we've talked before about how dropping off something like the application is probably less risky 16 00:01:07,230 --> 00:01:09,450 than leaving something off like the worksheet. 17 00:01:09,780 --> 00:01:15,540 So the further down this object hierarchy we get, the more risky it becomes to leave objects off of 18 00:01:15,540 --> 00:01:16,470 the object hierarchy. 19 00:01:17,190 --> 00:01:22,770 But with all of that being said, it's really tedious to have to retype the entire object hierarchy. 20 00:01:22,840 --> 00:01:24,870 Anytime I want to reference anything. 21 00:01:25,320 --> 00:01:28,500 So this is what the four object hierarchy line would look like. 22 00:01:28,560 --> 00:01:32,880 Starts with application, specifies what workbook I'm looking at. 23 00:01:33,030 --> 00:01:35,250 It specifies which sheet I'm looking at. 24 00:01:35,700 --> 00:01:40,090 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. 25 00:01:40,150 --> 00:01:46,140 So so this would be really tedious to have to type if you had to work with, say, 30 cells or if you 26 00:01:46,140 --> 00:01:49,300 wanted to do you wanted to pull the value out of that. 27 00:01:49,320 --> 00:01:51,810 So but you also wanted to know it's font properties. 28 00:01:52,170 --> 00:01:57,810 And this is where with and with comes in with and with kind of helps you step through the entire object 29 00:01:57,810 --> 00:02:01,170 hierarchy to ensure that your code is still stable and still accurate. 30 00:02:01,490 --> 00:02:04,560 But you don't have to type out this whole line every time. 31 00:02:05,130 --> 00:02:10,890 Essentially lets you cut in at any of these points and continue to work after that point without having 32 00:02:10,890 --> 00:02:12,690 to retype everything that came before it. 33 00:02:13,380 --> 00:02:20,310 So looking at this example, everything between this with an end, with all of this text and read here 34 00:02:20,730 --> 00:02:24,210 is going to apply to this text in green. 35 00:02:24,750 --> 00:02:30,000 Now, what that means is I can specify anything in that text in green up to a certain point in my object 36 00:02:30,000 --> 00:02:33,010 hierarchy, and then I don't need to retype that information anymore. 37 00:02:33,510 --> 00:02:39,480 So in this particular example, I know that I want to work with my worksheets called my worksheet. 38 00:02:39,870 --> 00:02:44,910 I want to look at the cell in row one, column two of that worksheet. 39 00:02:45,330 --> 00:02:50,220 And specifically, I want to look at the font properties of that so that I just specified. 40 00:02:50,770 --> 00:02:54,720 But I don't want to have to retype this every time I reference that font property. 41 00:02:55,050 --> 00:03:01,830 So instead, I put this in with and with all of this red text applies directly to the end of this statement. 42 00:03:02,430 --> 00:03:08,630 So what this really means is this dot bold equals true is actually representative of worksheets. 43 00:03:08,760 --> 00:03:18,180 My worksheet sells in row one column to dot font dot bold equals true and my color really means worksheets, 44 00:03:18,240 --> 00:03:22,110 cels, font property dot color equals V.V. Blue. 45 00:03:22,560 --> 00:03:25,890 So this is essentially cut in at the end of my font property here. 46 00:03:26,250 --> 00:03:31,050 And instead of having to retype this, this text in green, every time I want to reference a thought 47 00:03:31,050 --> 00:03:37,230 property, I can actually just tag on that farm property to the end of this pre-existing statement that 48 00:03:37,230 --> 00:03:39,810 I've determined with my with and with statement. 49 00:03:41,220 --> 00:03:47,970 So in this video we talked about, with and with which help us narrow down our obiter hierarchy without 50 00:03:47,970 --> 00:03:50,490 having to retype it every single time. 51 00:03:50,520 --> 00:03:52,230 So this provides stability. 52 00:03:52,260 --> 00:03:54,210 It provides accuracy in our code. 53 00:03:54,570 --> 00:04:00,420 And it saves us some time and effort and having to continually type out the full object hierarchy. 54 00:04:00,450 --> 00:04:05,490 So this is a nice little shortcut to use anytime you want to make sure you're referencing the correct 55 00:04:05,490 --> 00:04:10,470 cell and the correct properties, but you don't want to have to retype the full hierarchy every single 56 00:04:10,470 --> 00:04:10,830 time. 57 00:04:11,430 --> 00:04:13,660 So this is a really strong tool to use. 58 00:04:13,680 --> 00:04:15,090 And Microsoft, VBA. 59 00:04:15,120 --> 00:04:19,200 And next, we'll take a look at an example of how we might apply this.