Excel VBA Introduction Part 12 - With Statements

welcome to this YSL tutorial in this fairly short video we're going to look at how to use with statements in Excel VBA so we'll begin the video with a quick look at how to write a very simple with statement and give you a couple of reasons why that might be useful well then we want to show you how to create slightly more complex references in your with statements and finish the video with a look at how you can reference other objects within a with statement so there's not really all that much to do but let's get started in VBA a width statement is a great way to avoid having to refer to the same object multiple times a very common example of where they're used is when you're trying to apply lots of different formatting options to the same range of cells so for this example what we're going to do is apply lots of different formatting options travel list of film release dates so everything from cell c3 to see 15 so to get started with that I'm going to head back to the visual basic editor and I've begun a new subroutine called format film release dates and the first thing I'd like to do maybe is change the background color of the cells so I'd have to start by referring to range C 3 2 C 15 assuming that I'm on the correct worksheet of course followed by a full stop and then I said I wanted to change the background color that's the interior color I'm going to make it equal to C RGB aquamarine for the sake of argument and that's the first property that I've changed now if I wanted to change another formatting property of that range of cells I'd have to start the next instruction with the same reference where in C 3 2 C 15 4 by another . and then move on to the next property now the width statement allows us to avoid having to enter this reference at the start of each instruction so here's how it works if I just remove that reference I'm going to break this line in two I'm going to put the dot interior color on a separate line then in front of the reference to range C 3 2 C 15 I'm going to enter the word with down below the property that I've changed I'm going to enter the statement end with because every with a set end at some point 2 so you must have an end with statement to go along with the width statement then just for a nice layout feature I'm gonna indent this dot interior color line within the with segment now the brilliant thing from this point is that in between with and end with should I want to do anything to Range c3 to see 15 all I need to do is begin my instruction with a full stop and I see the full list of properties and methods of that range so I can go for things like I say font color perhaps and make that equal to I know RGB red maybe and I could say dots font size equals 12 I could say all sorts of different things so I can just carry on changing all the formatting properties of this block of cells let's change the number formats as well I'll change the number format so the date is formatted slightly differently say forties couple more ds3 MS and four wise okay so if we can just resize our screen so we can see at least some of the dates in the background and then I step through this subroutine using the FH key line by line we'll see that each time I execute a line it happens to that block of cells so not very surprising there's a nice convenient way to shorten your code just realize I haven't made my column quite wide enough to display the dates there we go that's what that formats comes out as so the four DS gives us the full name of the day of the week the two DS give us the number of the day of the month the three M's give us a short name for the month and find the four wise give us the full year so there's the basics of using with statement now I think if statements become more useful the more complex this original references so in our first example we set a fixed range of cells c3 to see 15 but what if we weren't sure exactly how long this list was going to be we'd know where it starts in cell c3 but wouldn't know where it would end that would require a slightly more complex reference to the range of cells and if I go back to the VB editor here's how well here's one way in which I could do that so I can say range C 3 comma range C 2 dot end Excel down okay that's a slightly more complex reference to a range of self-education from cell c3 down to whichever cell is at the end of the list from cell c2 in in timeless direction um two reasons this is the width segment is really useful because obviously entering that at the start of each phrase each instruction will be really really tedious lots of potential for spelling mistakes and typos missing commas and double quotes and so on and also if Excel does not have to evaluate that condition every single time we try to change the formatting property that range of cells the code will be more efficient as well so this this statement is evaluated just once when the width seaman begins so it does exactly the same job as before obviously if I just quickly switch back into Excel may be clear the format's from that block let's use a clear formats button oops I got rid of the date format as well actually that doesn't matter because my my code will format my dates in the appropriate way anyway so back to the VBA editor and if I step through the routine again using f8 line by line we'll see that all the same things happen to the full list of films and this time it wouldn't matter how long the list was we can take this process even further than that so we can extend this reference as far as we like so let's imagine for example that we didn't necessarily know that we were going to be on sheet 1 when we formatted our films release dates we can make that work by extending this reference to include the reference to the worksheet that our cells are on so we can say at the start of this sentence worksheets sheet 1 dot R in C 3 and also need to include that in fact and I just copy this cheating no I need to include that in front of the reference to range c2 as well so the full statement the full width statement will reference a range of cells that will potentially be on a completely different worksheet to the one in which we're working can't quite squeeze the full phrase on screen just about there we go so from reign c3 on worksheet one to range c2 down to the end of the list on which you want and again the advantage is not having to enter this reference at the start of every single instruction makes could have much neater and more efficient as well so just to reassure you that it does actually work let me quickly go back and clear the formats again and then let's make sure we go to a different worksheet sheet 3 this time when I'm just going to run this every team from start to finish using the room button or press the f5 key on the keyboard and then going back to you one will find in the same range of cells been formatted again so you can quite really really complex extended references with a press statement that'll save you an enormous amount of time when you're writing your code just as one final technical point about with statements although they're designed to allow you to work with the same object on each instruction there actually isn't anything to stop you from referring to other objects within the width statement so let's say just arbitrarily for the sake of argument that after we change the color of our cells that we refer to on sheet 1 we wanted to change the background color of all of the cells on worksheet 3 to match the same color and it's a bit of a silly example but just to demonstrate the principle that if I wanted to do something to another object within my with statement I can still fully qualify a reference to that object and so I could say worksheets sheet 3 so notice there's no full stop start of this instruction so heg 3 dots cells dot interior color equals and I could say RGB aquamarine of course just as I've done in the previous line alternatively I could say dots interior color which is quite interesting so that allows me to write to remember that the with statement to read the interior color property of that range of cells and set the interior color profit of cells on a completely different worksheet so I just execute that subroutine by clicking play and switch back into Excel I switched on to sheet 3 there we go all the cells are now aquamarine so it's a bit of a silly example but just that's a technical point to reassure you can refer to other objects within a with statement as long as you fully qualify them so that's the end of this very short video hope you found it useful thanks for watching if you've enjoyed this training video you can find many more online training resources at ww-why Zelko UK

Channel: WiseOwlTutorials

Views: 97,589

Rating: 4.9391174 out of 5

Keywords: Microsoft Excel (Software), Visual Basic For Applications, with, end with, wise owl, with statement

Id: rmTN-nFaeOM

Length: 8min 49sec (529 seconds)

Published: Thu Jan 30 2014