Logic Tests: If Then Else

Quick reference

• 00:05 The next coding construct I'd like to look at is the if then else statements.
• 00:09 We're gonna get into conditional logic inside VDA.
• 00:12 One of the things that I'd like to do with my continuity schedule, it's great that it
• 00:15 rolls forward automatically and gets into the next month all ready to go.
• 00:19 So when I do that what I'd really like to do is save it under the new month's
• 00:23 file name.
• 00:24 And as it happens on the control panel page I actually have the file name for
• 00:28 next month already set up here, but
• 00:30 what I'm going to do is I'm going to base it off of a different file name.
• 00:34 We're gonna go with the current month file name.
• 00:36 So the way I want to do this is I want to roll forward the macro so that it
• 00:39 sets up the schedule and then I'll save it under the current month file name here.
• 00:43 So, in order to do that, what I'm going to start with is I'm going to go to my
• 00:47 developer tab and I'm just going to record a macro.
• 00:50 I'm going to call this guy here something like save test,
• 00:55 because I need to figure out what the syntax is for this.
• 00:58 So, say okay.
• 00:59 And then we'll go File and we'll say Save As, and
• 01:03 we'll go into this particular folder.
• 01:05 And I'm just gonna save it right now, over top of itself.
• 01:09 Go and replace it, yes.
• 01:11 And we'll stop recording and we'll jump into the Visual Basic editor and
• 01:15 you'll notice that we now have a new module two.
• 01:17 So I'm gonna double click on that and
• 01:19 you'll see that here is the code that it actually generated.
• 01:22 So we've got an active workbook.save as file name and then it's got an underscore,
• 01:27 this underscore actually is a lined continuation character, so
• 01:30 in actual fact we can get rid of that but
• 01:32 It's then got the full file path all the way to the end of the file here.
• 01:36 Different folders and all the rest of it, and
• 01:38 then another underscore to continue and it gives me the file format and whatnot.
• 01:41 Now, that's how you get the syntax.
• 01:43 And then you can start tinkering with it.
• 01:45 So if we go and
• 01:47 look over here you'll notice that in my original macro I actually have
• 01:51 a separate routine that I've setup down the bottom here called save file.
• 01:55 And you'll notice that I've already done some modifications to it in order to get
• 01:58 to the place that I need to be.
• 01:59 So first we do a little syntax save, or
• 02:01 figure out what the syntax is that we're looking for.
• 02:04 The next thing that we can deal with here is we can set up the proper variables
• 02:07 whatnot, you noticed I set up a file path string a file name is a string a full file
• 02:12 path is string and the reason being is that when I look at this I'm setting my
• 02:16 Worksheet for Control Panel to the Control Panel worksheet.
• 02:19 I'm recording the This Workbook path, okay, this is one of the methods that we
• 02:23 have, or properties rather, that we have for a workbook is the path.
• 02:27 I'm also pulling out the value from cell B8, that's gonna be the file name.
• 02:31 And then I'm joining the two together with a backslash in between so
• 02:35 that I actually get the file path backslash file name.
• 02:39 I've then subbed that into the code instead of that hard-coded file path.
• 02:42 So I've got ThisWorkbook.SaveAs.
• 02:44 Again, this underscore is actually a line continuation character.
• 02:47 I don't really need it, so I could delete this and say.
• 02:51 Here we go, we got a nice single line.
• 02:53 Now, this is cool and everything, except that I want it to work like this.
• 03:00 We're gonna go and we're gonna run the original roll forward macro.
• 03:03 And that's gonna roll the worksheet forward to February 28th.
• 03:07 So that all looks good.
• 03:08 We know have the information that we're looking for for February 28th.
• 03:11 So we're gonna pull the value from here in order to run our macro to save this.
• 03:16 I'm gonna save this under a February 28th file name.
• 03:18 The challenge is, as I start going through and I run this macro, it comes up and
• 03:24 it tells me that hey there is already a file here that exists in this location,
• 03:27 do you wanna replace it?
• 03:28 I go no, I don't know what that is, so in this case I'm gonna say no and
• 03:31 this can drop me into the debug window and
• 03:34 I know have to figure out what I want t do with this.
• 03:36 So, this is where I want to actually go through and
• 03:40 run a little test to say, If the workbook exists, or
• 03:45 let's just call it if the target exists, delete it.
• 03:51 Then save.
• 03:52 Okay?
• 03:53 So how do I find that out?
• 03:55 Well, there's this little command called DIR.
• 03:58 I'll show you here, we'll say DIR.
• 04:00 And we'll go with SFU and now, at this point here what I'm gonna do is I'm
• 04:05 gonna actually hold down my control key and press spacebar and that will actually,
• 04:09 because SFU the only place that actually happens is in these sFullFilepath,
• 04:16 control space bar will let me auto complete and if I say dirsfullfilepath and
• 04:20 hit enter, you'll notice it gives me the name of the file.
• 04:23 So at this point I'm gonna put in a little statement here
• 04:29 that says if dirsfullfilepath
• 04:34 equals sfilename because the file name is what's actually being returned by this,
• 04:41 then, I'm going to do something different.
• 04:43 I'm going to Kill sFullFilePath,
• 04:46 and Kill is the statement to actually delete a file.
• 04:48 And then we'll say Else, what do we want to do if it doesn't match?
• 04:55 Well I'm gonna say right now do nothing and I always like to do this
• 04:58 you can actually end your separate your coding branches out this way and
• 05:01 put different code in here if you like also show just for
• 05:04 reference if I take S full file path and I change this variable name here to quote
• 05:09 a quote, so the full file path obviously is not gonna resolve to anything.
• 05:13 If I try this now you'll notice that it comes back with a blank string, so
• 05:17 its obviously gonna come back with something different, okay?
• 05:20 Now I'm just gonna hit stop and at this point here is what we're gonna do,
• 05:25 is we're gonna go and we're gonna say, all right well let's run the code now,
• 05:28 remember that file was there, it prompted us before.
• 05:30 So now I can run it It looks fine, what happened?
• 05:35 It actually ran through the process and it said, okay that's cool.
• 05:37 What we're gonna do is we're gonna set the worksheet,
• 05:39 we're gonna create our file path and our file name and we're gonna bind those
• 05:42 together into the full file path and we're gonna say, does the Drr(sFullFilePath)
• 05:47 here which if I mouse over you can see it gives me the full file path here.
• 05:51 If that equals the sFileName, and it does, then it's gonna kill the file path.
• 05:58 And it says at this point, permission denied,
• 06:00 the reason being is because I'm actually in this file, but previously,
• 06:04 it did it, so at that point, then we would save the workbook.
• 06:09 And at this point it's gonna tell me it exists because I didn't kill it,
• 06:11 but that's only because I'm running in debugging mode, and
• 06:13 I'm in the same workbook,
• 06:15 I'm already in the February 28th one, which is why it wouldn't let me delete it.
• 06:18 But, if I had started this from the January perspective,
• 06:20 it would have actually killed the February file, and rolled it forward just fine.
• 06:24 And at this point in time now, if I were to go back and say, all right,
• 06:27 cool, let's go and roll this forward again, this would get us March.
• 06:34 Now, let's roll it forward and save it, and
• 06:36 you'll notice that it saved under the March 31st file name.
• 06:39 If we go back, we can see that that's exactly what's happened.

Lesson notes are only available for subscribers.