Creating User Defined Functions (UDFs)

Quick reference

• 00:05 In this module, we're going to start creating user defined functions or
• 00:10 UDFs as we know them in the programming world.
• 00:13 And just to be fair, UDF is the cool way that we refer to any function that's
• 00:17 written by a user which is basically every function we're going to create.
• 00:21 So call them UDFs, you sound like a pro right off the bat.
• 00:24 Now, what I'd like to do is I'd like to write a specific function right now to
• 00:29 take a range on a worksheet and
• 00:31 figure out what the name of the worksheet is that it resides on.
• 00:35 It will be kind of an interesting one that we don't have on Excel by standard.
• 00:39 So you'll see the signature line that we talked about earlier,
• 00:41 it starts off with function.
• 00:43 I provided the name of a function, wsName, and then within the parameters I've said,
• 00:47 well, I know that I need to pass this function, a range, and you'll
• 00:51 notice that I have declared the data type for the parameter that I'm I'm passing in.
• 00:56 That's optional but I highly recommend to do that as it make
• 00:59 sure that you know a little bit more about your code when you're building out.
• 01:02 Finally, we have to declare the data type of the output.
• 01:06 So you'll notice in this case here as strength So, this is going to return text.
• 01:10 Now, what I need to do before I leave, is I need to set ws name equal to something,
• 01:15 because that's what's going to be returned when I actually work with this.
• 01:20 And, in this case, I'm going to say, well, I know that it's going to based on
• 01:24 the range, and I wanna show you something really cool here.
• 01:27 If I type in PARE, you'll notice that I can actually get to the range's parent.
• 01:33 Remember, we talked about the parent child relationship way back in the beginning?
• 01:36 Well, we don't have the ability to look just down the object model,
• 01:41 we can look back up so range.parent.
• 01:43 Dot where the rangers live?
• 01:45 They live on worksheets, so
• 01:47 if I look at the worksheets name that should work for me quite nicely.
• 01:51 Now, let's go and check this out.
• 01:54 We're gonna go down here.
• 01:55 We're gonna put in a question mark and we're gonna say, let's go with ws name.
• 02:01 Open our parenthesis and we're gonna type in selection because this is a cell that's
• 02:05 selected on a worksheet, and we're gonna hit enter, and
• 02:07 you'll notice that it tells me that it's on Sheet1, which actually does make sense,
• 02:12 because there's only one worksheet in this workbook, okay?
• 02:15 So that's kind of cool, if I created a second one, and I went back.
• 02:21 Here we go, and we try it again, you'll notice that we're on sheet two, okay?
• 02:25 So that's kind of cool.
• 02:28 What if I wanted to have a function to return the username?
• 02:32 This would also be another interesting one but
• 02:33 where would I actually find the pieces of code that I need to do that?
• 02:37 Well, let's actually look in the object browser.
• 02:39 And I say all right, well let's go and see what can we find for username.
• 02:43 And it says hey look, username is a member of the Application Class.
• 02:48 All right.
• 02:49 So, if we close the object browser,
• 02:55 let's try setting user = Application.
• 03:01 Username there you go.
• 03:04 Notice there's no parameters in here I don't need them but I do want to feed this
• 03:09 back as a string since setting user to the application dot user name so
• 03:12 let's go and say question mark user, open close parenthesis because we need those,
• 03:17 and it says that the user is Ken Puls.
• 03:19 So that's pretty cool.
• 03:22 Now, the last one I wanna look at is worksheet exists.
• 03:25 Does a worksheet actually exist?
• 03:27 And you'll notice that this starts to get a little bit more complicated replicated
• 03:30 because we're now taking components from other areas and
• 03:32 actually putting them all together.
• 03:34 We have an if statement that's checking something out we have an on air resume
• 03:38 next and go to zero.
• 03:39 We've got an error test in the middle of this roaring all this stuff up together.
• 03:43 Now, what's gonna end up happening with this.
• 03:46 I'm gonna go through and I'm gonna say you know what let's try this.
• 03:49 Let's go with there's worksheets.
• 03:52 There's worksheet exist you'll notice it shows up in the entire set.
• 03:54 And I'm gonna go a sheet one.
• 04:00 I'm gonna deal as set break point and now I'm gonna hit enter on this.
• 04:03 And you'll notice it logs in right there for me.
• 04:06 So it's gonna run on resume next and
• 04:09 then what it's gonna do is it's going to try and
• 04:13 set the last name here, to be the worksheet snames.name.
• 04:19 So in this particular case, this looks a little bit confusing.
• 04:22 But the sName that I have passed in in Sheet1, this is the variable decoration,
• 04:26 by the way I don't need to redeem this, okay.
• 04:29 So sName Sheet1 I wanna set.
• 04:31 Sheet1 = Worksheets Sheet1's name, which you would think would be Sheet1.
• 04:38 Why is this important?
• 04:39 Well, you know what?
• 04:39 In this case, no problem, it's gonna work.
• 04:41 There's not gonna be an error, so it says no error, therefore, WorksheetExists.
• 04:46 Yes, it does, that's true.
• 04:47 That's a boolean type.
• 04:49 So at this point, I should get something back.
• 04:51 Just say true.
• 04:54 What if I said let's check worksheets exist,
• 04:58 type in Ken, do we have a Ken worksheet in this file?
• 05:03 And we'll hit Enter, and it says okay, what I'd like
• 05:08 to do is I'd like to set SName = Worksheets(Ken).Name.
• 05:14 Well, if there was a Worksheets(Ken), the name would be Ken but unfortunately,
• 05:18 there isn't one so in this point it's going to trigger an error and
• 05:22 tell me that it does not exist.
• 05:23 This point we step out and the worksheet does not exist, okay.
• 05:27 So this is how we can actually use this thing to pass pieces in and out and
• 05:31 get answers back in a user defined function.

Lesson notes are only available for subscribers.