Excel VBA Basics #11 Create your Own Custom Functions with or without Arguments

hey there youtubers welcome back this Daniel strong with excel vba is fun this is lesson 11 of our basic series today we're going to be using module 2 you can use any of them but we're going to be making a custom function that's right you heard me Excel has a lot of built-in functions if you click on any cell and type in equals some sure everybody's familiar with that you could get the sum of a large group of numbers but you can't edit that one what I'm saying is that we can build our own functions that if the user or we ourselves type equals and whatever then they can do custom functions and excel so let's build one here by the way I'm going to rename this from module two I'll call this maybe miscellaneous or functions or something you notice if you right-click right here there's nothing that says the properties so what do you do well I know that you can hit f4 to bring up your properties menu and rename this however if you look here in the view there's your properties window f4 so let's hit escape and f4 ok we're going to rename this we're going to call it miscellaneous and with these you can't have any spaces so if you wanted to have another word you could put an underscore or something like that we'll just say M is C and if I let's see if I scroll this down a little bit you'll see that it's been renamed a miscellaneous alright first thing off off the bat is instead of starting out our procedure with a sub you know you're going to say function because we're making a brand new function so we got to give it a name our name is going well our what's our objective here first of all let's say we are going to try to get the last row in column one on whatever page we happen to be on so let's just call it function last row all right and you put you can put your parentheses like so so that's the most basic form of starting out of function you could put variables in here for example if one of our arguments was we wanted to put equals last row and then we wanted to click on a certain row or a cell or something that would go in here and we'll do that in more videos we're going to say function last row and you know there's no arguments so in order to get last row how do we say we do that want to say last row equals and we're going to take active sheet dot cells rows are count comma one for the for the column one dot end using Excel up dr. Rowe alright and that yeah believe it or not is our new function let's try it out and see if it worked equals last row you notice it came up now because it's our new custom function so I'll close the parentheses there's no arguments needed and hit enter 331 well let's see if that work I'm going to scroll down well did you look at that 331 is our last row now we could make a second function if we want to all-deaf 11 in our thing here we could call this function last row C let's just say last row C that means we choose the column so this time we're going to need an argument we're going to say column and let's go ahead and do that now so last Rosi let's make a little note here I'm going to say this is gives last row of select cell or column let's try this um we're going to copy and paste this information here last row is blah blah blah however instead of only on column one we may want to know what's column B or C or whatever so we're going to take the column here okay what we're going to do is we're going to change that to something we call it selected cell and when you do we need to declare this as a range okay so I actually pause the video a minute ago because it turns out if we don't dim this as a range then whatever we clicked on inside there is going to take the value for example if I click here it would take the value of I or a instead of taking the actual cell and so we're going to need it as a range because we need the range object of dot column so here's what we're going to do I'm going to take whatever they selected cell is dot column that's what we want to know and we'll give it a name how about SC equals as in selected column and now we're going to replace the column with SC so instead of in column one it'll use whatever column we're on so let's see this in action I'm going to put a little stop marker there and we're going to do and notice I changes from last row - last row see because that's our function and in order to give your function an actual value they're going to plug into the cell you need to finalize your function with whatever the function name is equals the final result okay so let's go ahead and try this equals last row see tab and we'll click on one of the columns how about this one you know in parentheses and enter now I put a stopper so we can see this step-by-step SC is going to be this selected cell column so you see if I hover over selected cell sure enough that's the only selected selected cell that column equals three and when I hit f8 it will take that over into the SC now last row C is going to be equal to all this but in the column of SC which is the third column right here let's hit it f8 last row C is 222 all right let's see if that's right 222 let's see oh boy it sure is it looks like we stopped early on the dates if I click that all the way down and I don't know if our function updated yet looks like we'll need to run this one again it is 331 which is now the end of the range so check that out so that's our first lesson in creating a custom function we created the last row which had no arguments inside then we did last row C which had an argument because we wanted to get the whatever column not just only the last row in column one and two the next time we'll talk about some more custom functions or something of the like

Channel: ExcelVbaIsFun

Views: 177,716

Rating: 4.7676611 out of 5

Keywords: Microsoft Excel (Software), macro, VBA, Visual Basic, reports, reporting, debug, Computer, excel vba, programming, vba excel, ms excel, ms excel vba, excel visual basic, microsoft visual basic, thisworkbook, object, vba basics, vba tutorial, visual basic tutorial, range, excel, excel tutorial, excel basics, easy excel, easy vba, easy visual basic, UDF, Custom function, custom formula, function, diy, easy

Id: uh409n2gGVU

Length: 8min 13sec (493 seconds)

Published: Mon Mar 04 2013