Calling a User Defined Function (UDF)

Quick reference

• 00:05 Now let's look at how to use UDF in the real world cuz let's face it you're not
• 00:09 gonna want to come back and
• 00:10 inquiry through the immediate window all the time that would be just silly.
• 00:14 The cool thing about UDFs is that depending on our structure we can actually
• 00:18 use them in one or two places immediately once you've created them.
• 00:22 In the case of the first two UDFs we've created here,
• 00:25 both of these return a string.
• 00:27 So a single piece of text and they take different inputs.
• 00:31 Obviously user doesn't take any but our WS name takes a range.
• 00:34 Where can we find lots of ranges?
• 00:36 Guess what?
• 00:37 On an Excel spreadsheet, right?
• 00:38 So, would it be cool if we could actually use the WS name function in a spreadsheet?
• 00:43 If we just typed in something like WS, holy cow, check that out it's even showing
• 00:47 up in TeleSense, I'll hit Tab, then I'll arrow over to the left here.
• 00:50 And close my parenthesis and there we go.
• 00:54 I got sheet one.
• 00:55 What about my user?
• 00:56 =USER() and there we go.
• 01:00 We've got my name, cool.
• 01:02 Now how robust is this?
• 01:04 Let's go and just change this to something else.
• 01:08 Notice that sheet one does not update and
• 01:11 if I press F9, it doesn't seem to do anything.
• 01:14 That's not really ideal.
• 01:16 There is a setting in order to actually make this stuff
• 01:18 re-calculate with the calculation chain.
• 01:21 That is called Application.Volatile.
• 01:23 You should not use this all the time.
• 01:25 You should only use it where UDFs do need to use it,
• 01:28 because it does actually add overhead.
• 01:30 But once you've done that, and we go in and we rerun our function once, just to
• 01:35 make sure that that particular line of code gets executed, now if I come back and
• 01:40 rename this again to Sheet1, you'll notice that it does update right away,
• 01:45 so it becomes part of the calc chain if anything is part of it, which is useful.
• 01:51 Now, that's pretty cool.
• 01:53 That's how we can actually use these things from a spreadsheet self.
• 01:58 But what about something like the worksheets exist function?
• 02:01 Well actually, you know what?
• 02:02 You could probably even do that, too.
• 02:04 We could actually say does =WorksheetsExist.
• 02:09 Let's go and take the information from Sheet1.
• 02:12 And it says that's true.
• 02:14 That's pretty cool.
• 02:15 If we tried does =WorksheetsExist for
• 02:20 the Ken Puls worksheet, it tells me it's false.
• 02:25 So that's recalculating correctly as well so that's kinda neat,
• 02:28 we can actually use that as well,
• 02:29 again because it returns a single point which is a Boolean of true or false.
• 02:32 If this returned a range that might be a different case or
• 02:35 if it returned an object it would be call it from the Excel worksheet.
• 02:38 Where can we use something like this?
• 02:43 Well this is where we actually get down into a routine that we want to use to
• 02:47 actually do something.
• 02:49 So in this particular case, you'll notice that we have set up
• 02:52 a Worksheet name as String, we've got a Worksheet variable that we've set up.
• 02:56 And the Worksheet String that I'm looking for
• 02:58 is Control Panel, which you can see plainly does not exist here.
• 03:01 So what I can do is I can actually now use the WorksheetExists function and
• 03:06 say if worksheets exist for Control Panel = False then I can actually create
• 03:13 a new worksheet and set it, otherwise I can just activate the worksheet.
• 03:16 So let's take a look here, and we'll go and
• 03:18 we'll set a couple of breakpoints on this, and we'll run this guy through.
• 03:22 So we see that we've come up, we're testing to see whether or
• 03:25 not this work sheet actually equals false.
• 03:27 And what you'll notice is that when we do that it steps right into
• 03:31 the WorksheetsExist piece and it says, hey look I'm looking for Control Panel.
• 03:36 We are going to try to set the Control Panel name to our sName variable.
• 03:40 Of course, worksheet Control Panel doesn't exist, so it will error,
• 03:44 at which point it returns WorksheetExists = False.
• 03:47 So once we go through and we step back out, it'll bring us over.
• 03:51 And it'll say, hey, I'm gonna add my new worksheet.
• 03:54 So that's fine.
• 03:56 It'll add the new worksheet and it'll set the name to Control Panel.
• 03:59 We can run the way through here and we'll get to with worksheet it now will.
• 04:04 Activate it and set our range to A5, and at this point it is going
• 04:09 to kick us back into the WS name function because this is volatile,
• 04:13 these guys are gonna run, even though it's not on this particular worksheet.
• 04:19 And this is why I would say you wanna be a little bit careful with
• 04:21 these particular guys.
• 04:22 So we'll run this the rest of the way.
• 04:23 But you'll notice that when we go back to that original code,
• 04:27 it's actually worked quite nicely.
• 04:29 It's gone through, and it's actually tested,
• 04:31 using our UDF to actually check whether or not something actually exists.
• 04:35 If it doesn't then it runs through.
• 04:37 Now let's go back and take a look at this again,
• 04:40 what we'll do here is we'll go again and we'll run and
• 04:44 it says all right let's go and check, does the WorksheetExist?
• 04:48 So now of course control panel does exist so
• 04:52 let's go take a look and apparently it does.
• 04:56 So that's cool.
• 04:57 So now when we step out, you'll notice that it says all right.
• 05:00 Well I don't need to go and create the worksheet.
• 05:02 So now I'm just gonna go and set this particular worksheet and again at this
• 05:05 point here I can run it the rest of the way out and it will add pieces in there.
• 05:09 So this is how we can actually use a UDF inside VBA to test something and
• 05:13 react accordingly as well.

Lesson notes are only available for subscribers.