How to create professional VBA custom function and add screen tip to guide users

hey guys I'm loompa my and in this short video I'm gonna show you how to create a professional user-defined function using VBA and then I'll show you how to add a screen tip or information about the function so that it's easier for the user to understand what the function does let's get start first let me quickly show you some of the inbuilt excel functions if you go to your formulas you can see a wide variety of functions which are already in place so for example let's say this is this is a date let's say that you want to extract what is the day for this particular date you can use thanks function and then you select a value and this is the string tip or the parameter information so if I select this particular date and then I put in like in this format that you want it says this is Saturday so this particular function returns us a day from a particular day so similarly let's say that you want to enter a random number between you know like let's say 1 to 1000 so a random number so once you select this particular function it says what the function is supposed to do right so I hit tab and then it says bottom number top number so these are functions which are already inbuilt in your Excel I'm going to show you how to create a function similar to something like this that you can build as per your requirement okay so I'll just put in bottom number and then a top number 1000 ctrl enter it will just put in all these random numbers for me okay and the other you know like function that we use very frequently for example some some if index match we look up all these are inbuilt functions ok so one thing to note here is that when we do controls that so when we enter a function earlier you see that the bottom number this is the bottom number this is the top number and then this is the function description and this top basically is the largest number integer they mention the type of the number as well between random between will return okay and one thing to note here is that it says this is volatile that means every time you save your workbook or you change any value within the world well it's gonna change it's gonna recalculate okay so these are some of the functions that you need to watch out for because it's con it can make your work really heavy okay so I'm just gonna just clear up all this I'll quickly show you one small example and then we'll go to a more complicated version of that okay so let's say that there's a function add number something like that and then we didn't this bracket you can pass in a parameter let's say number one as long or you can say double double can take in decimal points where as long can only have a solid number absolute number and then um let's see num2 as a double again and then what number what sort of data do you want to return from there will be this so I'll specify I want to return a double number okay now add number is equal to that means you're add phone add number function is going to return this value so if I just say if I just put number one here it's going to return that one that is a static value but we want to return a number one plus number two okay so if I just take this function and put it here I'll say add number there's our add number without any description about the function and let's say I put a number one and then number one so we're expecting one plus one and our function is going to return two at the other way of you know like there are other functions that you can sort of write for example it's a function and then this one does not take any input as string let's call this function get user name so get user name can be vacationed user name and if I just put this function within your formulas it's gonna return me alpha my so that is my application username which you can find it from your file if you go to your options and then this is where I is fetching the information from okay so similarly let's say you want to get a computer name so you can write any sort of function like I get computer name I'm just giving you some example so that you know like you can create all sorts of function let's say that this computer name is gonna return and environ from here we want to get a computer name okay I get this function I say called the computer name it's gonna return me LP PC so you can verify this game by going to your computer properties and then you can see LP EPC for example so you can get all this ordering information let's say that you want to fetch the the time when the work was save loss or who edited last all sorts of information you can pull using similar function lighting are using the work group properties functions okay so now that we have already created some of the functions here you can you know I add a description about these functions okay so let's say um let's write one more function about arrange I'll say let's say get hyperlink from cell and then we'll say select cell as range and this is gonna return us a string and we'll say get hyperlink is equal to whatever range was selected here was they do start hyperlinks will just take the first index start address let's say that you have a hyperlink you want to add a hyperlink I'll do ctrl K and let's say this is Google site something like that let's say you have a cell with with a hyperlink and you want to extract this so you can use a function like this to get the hyperlink from there so use equal to and then your the custom function that you wrote you select the cell and then instead of the caption or the label is going to return you the hyperlink with within the cell okay all right so that's enough of an example for now let if I go to this particular function now it does not have it says no help available and then this is the caption this is the the hyperlink within it okay now let me show you how to add a description or information about the parameters about the function click anywhere within your function and hit f2 from your keyboard or you can go to view and then select object browser so it's gonna take you to all this library from here you can select vva project and then you can select the module where you're writing the code and then you'll see all your functions are here okay this is this does not work 100% this method does not work 100% but you can try to do this depending on the access level that you have in your computer so if I go to the function right click on a function and go to properties you this is where you can add our information about this particular function so let's say that this function will return hyperlink address within selected so and I click on OK and this particular cell click on this formula it says this function will return a hyperlink address so another method to add information about so the other method to add the description or information about the function is to use a macro options method so let's start writing a small code will call this up add info for function something like this okay and then we'll start off with applications that micro options so we didn't this you can specify micro name the description category the argument these are some of the things we're going to cover beyond this is like the menu menu Texas Orkut kiddies are some of the things you can implement as well beyond but beyond this particular tutorial okay so it's a simple let's start with adding an name of the micro let's say you want to get a computer name so you put in the name of the computer the function here and then we did in this description you can add whatever you want let's say this function or we can say will return the name of the function and we'll return the computer name that's it and then we'll just ignore all this for now and then within the category we'll say you can specify something like custom user-defined function something like that so your function is going to show up in that category from your formulas so if you go to your function here these are all the categories so once you're done with this you should be able to see your own category okay so coming back here now status bar I'm gonna leave that they've done helpful live the last one is the argument is where you can add information about the parameter in this scenario this get computer name function does not have a parameter so you can specify that this does not have a parameter but you'll have to pass this using an array so let's say there is an array that you've created or create a simple one they may this will only have one one particular item and we'll call this as variant type and we'll say the value is this function does not need any parameters or something like that well so let's try to run this so it ran okay I'm gonna copy this put it here and this entered is it returned the name of the computer if you go to the function it says get computer name will return the computer name that is what we added it from our macro here from down here as you can see okay and the the arguments and the parameter information is gonna be it says the function takes no arguments okay and it returned this particular value click on OK now let's say that you want to add about this particular this particular function you can simply replicate this or you can write a function to do a similar to ask ok so I'll do one more example and then we'll say to example 2 I'll copy the name of this function hyperlink dot add I'll put the name of the function here and then we'll say this function will return the hyperlink for selected so ok and this will also come under custom EDF and then here the arguments can be select select the cell you want to return the hyperlink something like that ok I'm gonna try to run this this also have only one arguments so nothing much to change there so I ran ok so if I go back here and go to this particular function it say this function will return the hyperlink for selected cell and select the cell once you look here this is the this is a parameter the select the cell you want to return a hyperlink from so that way the user cannot you know like easily understand what the function is supposed to do it really helps when you add information like this if you want to see where your formula or the function is located you can click on this FX function and then you can select custom custom function is where we added custom media this is where we added this particular function now one last thing I want to quickly tell you is that this this formulas are volatile as I said in the beginning that every time you add every time you change the value or every time you calculate or save these functions are gonna run so if you want to prevent that from happening and only want to calculate when you hit calculate or change the value of that particular related cell only you can add an option like application dot volatile and then you can save false so that way it's it's only gonna calculate when it's one you want it to calculate when you change the value of the related cell or when you hit calculate for that particular cell okay guys that's all I have for you today thank you so much for watching again please do not forget to leave a like or a comment if you found this video informative as always thanks for your support and have a good day bye bye

Channel: VBA A2Z

Views: 3,158

Rating: 5 out of 5

Keywords: user defined function, excel, visual basic for applications, vba tutorial, visual basic for applications excel, user defined function in excel, user defined function in excel vba, vba tutorials for beginners, excel vba user defined function, vba user defined function, excel udf function, excel udf vba, udf vba excel, excel user defined function, excel custom functions vba, how to add custom function in excel, vbaa2z

Id: _7_MX0sQHI0

Length: 14min 30sec (870 seconds)

Published: Sat Jan 11 2020