With Blocks

Quick reference

• 00:05 In this video, we're gonna start to looking at coding constructs.
• 00:08 And this is ways of coding that make it easier to both read and
• 00:11 write code that's actually produced.
• 00:13 It also helps makes things a little bit tighter and
• 00:15 little run a little more efficiently as well.
• 00:17 First thing I want to cover off though is,
• 00:19 I've made some changes to the continuity schedule.
• 00:21 And you'll notice that right now the date is now pointing to another worksheet.
• 00:25 control panel B3.
• 00:27 And you'll see that over on the control panel worksheet,
• 00:29 I have the current month end,
• 00:30 and I already have the formula set up that I should read the next month end as well.
• 00:33 So I can just read these into my code now as well.
• 00:36 Now what I'm gonna take a look at here is if I jump over, you can see that I've had
• 00:40 to make some modifications to the way the VBA code runs as well.
• 00:44 We still have our schedule set up.
• 00:45 And we still have our control panel worksheets that are created.
• 00:47 And they're set using the set statement to set those variables up.
• 00:51 And I've also commented out the code a little bit more to make it a little
• 00:54 easier to read.
• 00:55 We can see that we still have the rolling forward of the working paper,
• 00:58 where we copy the ending balance and
• 01:00 we paste it over the opening balance, and we clear the contents.
• 01:04 But then you'll notice in this line, we actually have a different line of code.
• 01:08 Rather than doing a whole bunch of stuff to copy and paste, and
• 01:11 then clear contents,
• 01:12 all we do is we say I'd like to go take a look at the control panel range B3 value.
• 01:17 And set it equal to the control panel's range B4 value.
• 01:21 And then we go back and reactivate the schedule worksheet and select cell C6.
• 01:25 So if I go back and take a look at that, what it's gonna do is it's going to
• 01:29 set the value of this cell equal to the value of this cell.
• 01:31 Which happens to be a formula that's driven from this cell so
• 01:34 it will update automatically.
• 01:36 And then once done, it activates the schedule worksheet and selects cell C6.
• 01:41 So some minor modifications to the macro just to make it a little bit cleaner and
• 01:44 a little bit easier to work with.
• 01:46 But there's something else we can do with this thing as well.
• 01:49 If you were typing this by hand right now, you would have to type
• 01:52 out wsSchedule.Range all the way through all this stuff, and
• 01:55 then we're gonna change out to something different.
• 01:58 This is when VBA interprets this, it's kind of like,
• 02:01 let's say that you were trying to actually figure out what color your house was and
• 02:05 how much it cost and what not.
• 02:07 Normally if you're talking to someone, you'd say well this is my house.
• 02:10 The color is brown and the cost is x.
• 02:13 The way that we're talking about things right now here is what we call fully
• 02:16 qualified.
• 02:17 So the equivalent would be saying, well my house is in Canada in the city
• 02:22 of Nanaimo in this particular street on this particular address.
• 02:26 The color is this.
• 02:28 And then you'd go back again and start all over again and say,
• 02:30 my house is in Canada in the city of Nanaimo in order to drill down and
• 02:33 to actually get all the way down to what the cost is.
• 02:35 And that doesn't make any sense.
• 02:36 It's a very, very long winded way of doing things.
• 02:39 So wouldn't it be nice if we could actually take something where we already
• 02:42 have a variable set up that we've set to wsSchedule, and
• 02:45 we could refer to it a little bit more efficiently.
• 02:47 It's already doing better than most of the train that we have to
• 02:50 go through here because we've actually got our house bound to one specific object,
• 02:55 so it shortens a lot.
• 02:56 But we can actually get even a little bit tighter by using the with keyword.
• 03:00 So what I'm gonna do is I'm gonna type in with, and right after schedule,
• 03:03 I'm gonna enter and tab it in.
• 03:05 And at the end of this little block, I'm gonna put in end with.
• 03:10 What happens here is what's actually interpreted by the VBA compiler when
• 03:15 it runs now is it is says with wsSchedule.
• 03:18 And then any line that has nothing preceding a dot, it says well that must be
• 03:22 wsSchedule.Range, wsSchedule.Range, wsSchedule.Range.
• 03:25 So that's kinda cool.
• 03:26 We can also do the same thing here.
• 03:28 You'll notice wsCP shows up in two places.
• 03:31 So I could say with WSCP.
• 03:33 I could go down and put end with statement at the end.
• 03:37 And then what I could say is I could say well let's get rid of this.
• 03:41 And also get rid of this, because we don't need a tab there really.
• 03:46 Because what ends up happening here is when this line is actually interpreted,
• 03:50 it'll say wsCP.Range.B3.Value and Equals.
• 03:55 Again, this is implied WSCP because that's what the with statement is above it.
• 03:59 So I can actually start writing code that looks a little bit nicer and
• 04:02 that blocks it out a little bit more and it's a tiny bit more efficient as well.
• 04:06 The other thing about these guys is that you actually can
• 04:10 nest multiple with statements together as well.
• 04:13 So if I wanted to, and I don't have a reason to do
• 04:16 that in this particular block of code here, but I could if I wanted to.
• 04:19 For example, do something like this.
• 04:21 I could say well lets go with Range(*E6:E10*).Copy and end with.
• 04:30 Key things, your with always needs to be ended out with an end with because that's
• 04:34 when it tells it to stop, actually putting these things in place.
• 04:38 And notice that when we get into copy here, this says well it's Range(*E6:E10*)
• 04:42 .copy in that range because its .Range says it's wsSchedule.Range.
• 04:47 So this thing cascades all the way down.
• 04:49 Now, in this case, it doesn't make sense.
• 04:51 We've only got one line.
• 04:53 But when we're working with multiple lines together,
• 04:56 it does make sense to actually go back and actually pull these
• 04:58 things into nice little blocks that can be easily read and easily debugged.
• 05:03 One of the big things that I like to do is always make sure that my code is indented
• 05:06 within those blocks so that I can actually see where it all lines out.
• 05:09 The key thing here that I want you to recognize,
• 05:11 once this is all done, we can come back.
• 05:14 And the code, if we roll it forward, will run just fine, nice and fast.
• 05:18 Everything is good there.
• 05:19 It actually runs a teeny bit faster even than going back and
• 05:21 doing the fully qualified references as well.

Lesson notes are only available for subscribers.