Excel VBA Introduction Part 18 - Creating Functions

welcome to this YSL tutorial in this video we're going to cover how to create functions in Excel VBA so the video is all about how you get procedures to return values we'll start with the basics of writing the simple function and show you all the things you need to change between writing subroutines then we'll talk about how you can call a function including how to test it using the immediate window we'll explain how you can create parameters to extend the flexibility of your function and also how you can include optional parameters to make life a little bit easier for the user finally we'll have a quick little chat about how you can rewrite code that you've already written to use functions which should make it a little bit neater and hopefully an awful lot shorter so let's get started a function is simply a procedure which returns a value and they come in handy whenever you find yourself writing out the same calculation steps again and again to demonstrate the basics of how they work we're going to write a simple function which returns a nicely formatted version of today's date to get started it's similar to the way you begin a subroutine except instead of writing the word sub you write the word function instead then you think of a sentinel name for your function I'm going to call mine custom date and finally we should say what type of data this function will return my function is going to take today's date and it's going to return it as a formatted string of text so at the end of the declaration for my function I'm going to say as string similar to how you declare variables and set date fights for variables in VBA when I hit enter the rest of the function declaration is filled in for me so I get end function and I get a set of parentheses after the after the function name as well what we need to do now is write out the code that will calculate the result of the function now this function is going to be very simple in fact there's only going to be a single line of code in it and it's the line of code which tells the function which value to return and whenever you want to tell the function to return the value you state its name so in this case custom late and then you assign a value to it so to make this work we're going to format today's date using a built in VBA function called format the expression that I want to format is the function called date and finally the format that I want to apply is let's see I'm going to go for a nice long date format which shows me the full name of the day of the week the double-digit day of the month the full month name and finally a four digit year that's what that code will do now obviously this could be a much more complex calculation the expression that we assign to the return value of the function but just to demonstrate the principle this will do for now so there's our function written all we have to do now is call it from somewhere to see what value it presents one simple tool that you can use to test if your function is working is the immediate window if I head to the View menu at the top of the screen and choose immediate window I can then ask the immediate window to show me the result of my function I can do that by typing in a question mark and then the name of the function custom date and finally hitting Enter and that tells you what value the function returns so now that I've established that the function is working what I can do is use it in an actual procedure so another writer subroutine let's say we want to imagine we're going to insert a new worksheet into my workbook and every time we insert a new worksheet we want it to be stamped with today's date I'm going to write so called create new sheet and then the first line of code in there will be to create a new worksheet but when I say worksheets dots add following that I'd like to make range a one's value equal to a phrase which says created on followed by whatever today's date is so gonna concatenate it up created on using ampersand and now I want to join on the value of my custom day function and to do that I simply refers to my function name again you'll find your you should be able to see your ear your function names in the intellisense list if I press ctrl + space on the keyboard and look for custom date it should be there so I'm gonna press the tab to type the rest of it in and that is how our function will be called I'm going to use the f8 key to step through so you can see that after I added a new worksheet when this function is called you can see that the code will step through all of the instructions in your sungeum so custom data currently set to an empty string until we assign a value to it in which case we assign a formatted version of today's date and then the end result if I switch back to excel quickly here's a new worksheet with that phrase stored in it so there you go your first simple example of using basic functions in VBA at the moment our function is quite simple and that it always returns a formatted version of today's date but what if I wanted to be able to tell my function exactly which data format each time I call it I can do that by adding a parameter to the function declaration if you want to add parameters you list them in the parentheses after the functions name think of a sensible name for your parameter I'm going to call mine data to format and then say what beta type the parameter should have I'm going to say the mines as a date one more thing I need to change in order to make sure that my function returns the formatted version of the value that I pass in I'm going to change the code that says format date to format base to format so now the function will form up whatever date I pass in via that parameter once you've added a parameter to a function the way you call it is slightly different as well so for instance if I try to run my create a new sheet subroutine again I'll find that I can't because I haven't provided a value to my data for my parameter I haven't given it an argument so if I click OK and just reset my subroutine if I did want to pass a value into this function I can open a set of parentheses after the custom date function name and it shows me the tooltip showing me the list of parameters that I've declared then I simply have to say what dates I want to pass in so just for the sake of convenience I'm going to pass in today's date again using the date function and I find that now I've done that I can run the subroutine again quite happily and it will create another new worksheet that will show me today's format a date but the way that my function works I can change this to be any date whatsoever and I should get a formatted version of that date let's say I was my brother's birthday this week he's his birthday's on the 19th of Feb 2014 in Mother's birthday and if we ruin this everything we should find we get a new worksheet with the formatted version of that dates instead so there we go there's have to declare parameters for the function to make them that bit more flexible you can add more than one parameter to the same function so I'm going to add another parameter which allows us to choose whether or not we include the time in the format that we returned so after the first parameter I can type in a comma and then specify the name of the next parameter I'm going to call this include time and I'm going to say that that is as boolean I'm gonna drag my screen across a little bit so you can see the whole thing so there we go we are going I've included a second parameter called include time what I'd then have to do is make sure that I use that value in some way in the functions code so I'm going to write the simple if statement and say if include time then and then I'm gonna do a quick little cheat here and copy this line and paste it in I'm going to add a little bit of extra code to my date format I'm gonna run in the HH : mm : SS that will format the time as well to the hour minute and second and then else custom date is just the original version of my date format don't get the ENDIF which i often do and just by the way using boolean values the simplest way to test if this value is true it's a simplistic is to simply say if include time is perfectly acceptable to say if include time equals true if that's your preference but just know that it's absolutely not necessary if include time is the exact equivalent of if include time equals true so it's a nice sure way to test if a boolean value also true so now that I've declared that extra parameter if I try to run this subroutine without having passed the value into it I'll be told that there's an an argument is not optional so I know that I haven't passed in enough values passing enough arguments into my custom date function so what I'm going to do is in fact let me take away all the brackets so you can see the tooltip pop up if I open the parentheses you'll see that I get my two parameters listed date to format comment include time it shows me the one that I'm currently on using the bold text so the data I'd like to form on this time I'm going to go for the date again and I'm going to say the include time parameter let's let's say true so I will include the time in this version if I use that fake to be in stepping through that just so you can see that the if statement works properly so I've said include time equals true which means I get the foot the the format's which includes the time and the function and the subroutine switch back to Excel quickly and I get the time as well of course I've didn't pass in a value that actually included a time so let me just quickly go back to the VBA editor and I'm going to change the date function to now instead and if I run that this time switch back to excel I get the time as well just sure that it does work if I say false if I run this everything again I'll get the date without the time so there we go making the function that a little bit more flexible again with the inclusion of another parameter now obviously the more parameters you declare for your function the more work you have to do when you call that function so sometimes it's nice to be able to make your promises optional and you'd actually do that to any parameter at all any parameter can be optional the only rule is that all the optional parameters must come after any of the compulsory ones so what I'd like to do is I'd like to make my include time parameter optional and it's so simple to do all I need to do is in front of the parameter name typing the word optional that's as simple as it gets I couldn't have put the dates to format parameter after this one as I say all the optional parameters must come after all of the compulsory ones I can also provide a default value for this optional parameter as well if I wanted to make sure that if a user did not specify include time it was set to false for example I can make that the case by typing equals false after the data type so there we go there's how to declare an optional parameter for a function at very very very simple so what I can do now again it might take away the the parentheses and just display the tool to begin after my custom date parameter you'll see that the include time parameter is enclosed in a set of square brackets which indicates that it's optional and it also tells me what the default value of that parameter is so if I say give me the given performance version of now I'm not going to specify the include time parameter but I can serve them in the subroutine because the predominate are now is optional if I look at the the the results I get today's date formatted without the time back to the VB editor if I wanted to include the time I would have to type in a comma after the first parameter and then specify that that is true but more time if I just really want small just to prove that it still does work that's why Excel and now get the formatted version of the date with the time included there we go optional parameters are as simple as you like just remember the optional parameters must come after all the compulsory ones so far we've only been calling our functions from within the VB editor but it's interesting to note that you can also call your custom functions from within Excel itself if I switch back to the workbook and pick a blank cell I can start typing equals custom date it's interesting to note that in this version of Excel M in Excel 2010 if you're calling your function in the same workbook that the function is declared in you'll see the name appears in the intellisense list with a little function symbol next to it if I press tab to type in the rest of that word custom date unfortunately I don't get a tooltip which shows me which parameters I can pass in but fortunately I know that I've liked them I can pass in a date or time I'm gonna pass in the now function and I call I can also specify whether it whether or not to include the time going to put in the word true to include the time closing parentheses hit enter and I'll get a formatted version of today's date it's worthwhile mentioning that I can also see my functions from the function wizard if I hit the FX button in another blank cell I can look for my custom functions in the custom function categorical the user-defined category as it's called that shows you a list of all of the functions that I've declared and it shows me a tooltip showing me what the parameters are tells me I've got no help available of course but if I click OK I'll get the function arguments dialog so I can say dates format equals now again and the include time function parameter I can set to under set to false this time I know that's optional but I'm going to do it anyway I click OK I get another version of the results of my function and just as previously one thing to note about doing this is that this is not the most efficient way to generate this result every time your spreadsheet gets recalculated and I can do that manually by pressing the f9 key if you just watch web and so they say this this time here every time I press f9 it recalculates cells which call your custom they function like so and that's not true for the a cell which simply used our function to generate a fixed string of text this will not change so because every time you recalculate the worksheet it calls your function again it can make spreadsheet relatively inefficient if you're calling custom functions because some VBA functions in the worksheet itself now the more code you put into a function the more useful it can become and the shorty or the subroutines can become as well you may remember from a few videos ago we've done this several times in fact we've we've rated a bunch of our top movies based on their running time so if I just quickly switch back into excel we have this list of films what we'd like to run down column a and for each film we encounter we like to read it length work out whether it is a short medium or long film and place the value into column E now to do that and I switch back to the VB editor here's some of the code that could do this is just one way to achieve that result it's fairly basic way of doing it but it works so we start at cell a3 and we carry on going down until we hit a blank cell and for each cell we pick up the running time from the value of the cell three columns to the right and then passing it through a module rather large if statement to work out which category it should be in short medium long more epic what I'd like to do is replace this set of the calculation with a custom function so I'm going to declare that function at the top of my module I'm going to call it function film length and I'm going to give it a single parameter which is going to be called run time and that's going to be an integer I'm going to also say my function returns a string because it will turn a descriptive piece of tech short medium long or epic well I can do them is copy my entire if statement from the subroutine I've just written and then paste it in to my function well also need to do is change the way I'll just tidy up my indenting a little bit as well we also need to do is change my my if statement so that it says run time rather than running time so running time was the the variable that I declared in the 17 run time is the name of the parameter that I've just created a political them the same thing should night save a bit of time anyway there we go quick tweak I also need to make sure that rather than trying to set the value of the cell equal to this specific value I simply want to make sure that it's my film length function which returns that value so I'm going to copy the film length function name and paste it over the top where it says active cell offset 0 from a4 dot value so it's actually a relatively simple function if you think about it well if this is a text number called run time it works out if that run time falls within certain set of categories and whichever category it falls into the function will return a single piece of text now the great thing about this is that even though the function itself is quite simple because the code itself is quite long it means that anytime we want to categorize our films by length we can replace an enormous amount of code in each subroutine so if I scroll down back to my o subroutine I can get rid of several things here I can get rid of the entire if statement for a start because that's the job that my function now does I can also get rid of my variable dim running time I don't need that anymore either and I also need to tweak this line here where it says running time equals actor cell offset 0 comma 3 what I actually want to do is set the value of the cell that is offset for columns to the right and we'll set it to be equal to the result of my film length function so I start setting in film length open a certain product sees I need to pass a value to the runtime parameter and the value that I'm going to pass is the value of the cell that's three columns to the right of the active cell so I close the parentheses at the end get rid of my extra couple of blank lines there how nice is that compare this to the original version that subroutine and the code itself is so much shorter and neater and if you remember all the times we've actually done this in previous videos in this series where we categorize films by length think about how nice it would have been to have had this function available in the first place we could have saved an awful lot of typing so if I just give this subroutine a quick run make sure that it actually works quickly check back to the spreadsheet first so we don't have that column of figures in there just yet if I go back to the VB editor and run this subroutine we should find it does the same job as it has them previously it's just that the code that we have to write to do that is much much neater so there's the power of using functions in VBA if you've enjoyed this training video you can find many more online training resources at ww-why Salvio UK

Channel: WiseOwlTutorials

Views: 146,701

Keywords: Microsoft Excel (Software), Visual Basic For Applications, function, parameter, argument, wise owl

Id: Bsfe-2VcvZg

Length: 19min 57sec (1197 seconds)

Published: Mon Feb 17 2014