User Defined Function (UDF) Syntax

Quick reference

• 00:04 So far, everything we've done in this course has all been about sub routines.
• 00:08 So, basically long blocks of code that allow us to script actions
• 00:12 to be repeated on demand.
• 00:14 But there's a whole nother dynamic to working with VBA, and
• 00:18 that's around working with functions.
• 00:21 Now, what a function does is it actually allows us to take a certain amount
• 00:26 of inputs from zero to however many we want, pass them into the function and
• 00:31 operate on it, and return something back to either a worksheet or
• 00:36 to another VBA routine, how do they actually look?
• 00:40 Well, they're very, very similar, actually, to a sub routine, as a matter of
• 00:44 fact, they're so similar that, at first glance, you might even confuse the two.
• 00:49 One big difference you'll notice, that when you're actually looking at the syntax
• 00:54 here, where it used to say sub, it now says Function, so, it's a minor thing.
• 00:59 The biggest difference, though, is that at the end of your function,
• 01:04 you also have the clause As DataType,
• 01:07 you need to define your function to return some kind of a DataType.
• 01:11 And that's unusual, as far as the sub routine goes,
• 01:14 because the sub routine, even though both take potential parameters,
• 01:18 the sub routine's job was to basically run a bunch of executed code.
• 01:22 In the case of the function, it actually takes that code, processes something, and
• 01:26 then kicks something back out so that it can be used elsewhere, so
• 01:29 this is why a data type needs to be defined.
• 01:30 Is it operating and
• 01:31 returning a text string, is it operating and returning a number, or
• 01:35 a Boolean, true/false value, or, is it returning a worksheet or a workbook?
• 01:39 You could do that as well, like in the subroutine,
• 01:43 as soon as you open up your function, you will have a certain block of code.
• 01:49 One big piece is, before you end your function,
• 01:52 you really want to set the function name equal to something.
• 01:56 Because that is what's gonna get passed back through
• 02:00 the data type to whatever the calling parent was, okay?
• 02:05 Once again, like working with a sub routine, these can be public,
• 02:10 which is default, or private.
• 02:12 If you want to return information to a worksheet, you will need it to be public.
• 02:17 If you want to keep it reserve so that that function can only be used within
• 02:21 a specific code module, then you're gonna wanna actually mark it private.
• 02:25 For the most part, you'll probably work with different functions,
• 02:29 just like working with a sub routine, the name cannot start with a number.
• 02:34 You can't use special characters, except an underscore, of course,
• 02:38 that one is a permissable one, and again, you can't have any spaces,
• 02:42 use an underscore instead, just basically exactly the same as the sub routine.
• 02:47 So the big question here then comes down to, well, okay, so
• 02:50 if everything is relatively similar, again,
• 02:52 it comes down to this data type at the end, how do I know which data type to use?
• 02:57 And again, we come back to this now famous list of data types that looks like this,
• 03:02 so for true/false values, we're gonna reach and declare our stuff as a Boolean,
• 03:07 if we only need a smaller number, we'll go with Integer.
• 03:10 For the most part, you're probably going to end up generally returning a Boolean,
• 03:16 a long, a string, or an object of some kind.
• 03:20 Now, when you're actually dealing with objects,
• 03:23 you can return it as object, you can also return a data type as worksheet,
• 03:28 as range, as workbook or something like that.
• 03:31 It won't be until you get into more advanced coding that you actually start
• 03:35 working with variants at the end, but that is a possibility as well.
• 03:38 So all of these, again, are useful possibilities for
• 03:42 returning from a function, and I will show you in the next couple of videos
• 03:47 how that actually all comes together.

Lesson notes are only available for subscribers.