Excel VBA Basics #12 Custom Functions - Making VLOOKUP Even Easier for Coworkers

hey thanks for tuning in again this is Daniel strong with excel vba is fun we're going to create another custom function if you're looking on Google or searching this a lot of times people will call this user-defined function or UDF we're going to do another UDF today today we're going to make it super simple for even laypeople who don't understand Excel at all to use a vlookup so instead we're going to show you what regular vlookup does you should know this by now but if I'm going to do CR 1:52 that's our first item number here and this we're going to use a quick vlookup oops equals vlookup and we'll take this whole table here oops excuse me we're going to vlookup this in this table here control shift down f4 comma we want to get the description so we'll say column two and comma zero false we want an exact lookup so there we have it we have the product and if I copy this over let's see here OOP I need to put F 9 we need to lock in F so that should work if I copy this down okay and then we'll say column 3 that will give us our date ctrl shift 3 to give us a date format kind of a crappy date format control one date okay and then we want column 4 which is our status and that ought to do it okay here's our vlookups these three are looking up this and if I change this to PA to 5 one looks like that's this one right here it vlookup all this stuff we're going to create a user-defined function 4v looking up one of these item numbers that make it ridiculously easy for people so how about we'll make one function here for the description the date and the status so let's get started on f11 here is our visual basic code we're going to say actually first of all let's make it really easy for ourselves we'll start off control shift down we're going to give this a name we'll call it my table you may have a better name for your product table or whatever alt F 11 here we go we're going to make a new function let's go ahead and make a new module so we can clear up the space here so we're going to say function and let's make the first one called disc the ESC okay and we're going to say let's see do we need an argument we do need an argument we need to have the product number prod num that's fine whatever okay so how do we get started I want to say let's see well let's just make it real simple DSC that's our answer it equals application dot worksheet function dot vlookup okay that's how you do any of these functions in excels Visual Basic users to application down worksheet function and then use your function so if you look at what is our argument number one that is prod number so we're going to say whatever they either type in quotes or click on whatever cell that they put as their argument is going to be this right here so the product number comma what is our table name we can use the evaluate brackets to put my table or the table name otherwise you could put I'll show you both ways you could put a range my table okay in quotes and in parentheses we'll do it that way for this comma what column is description commits to and then comma zero for exact match not approximate so let alack Chua ly do it I believe let's try this one out let's say equals de SC and click on that one and hit enter product eight hey awesome so we have equals de ser description function all def 11 here let's make another one let's just copy paste a little bit this will be once the next function is going to be called a date so equals let's say D added our function will be called D added copy that here and that's going to be column 3 and we'll copy that over one more time and we'll make our final function called status copy and paste and that was column four I believe so Believe It or Not ladies and gents we've done it we've got our vlookup right here so let's go ahead and click over here equals what do we call that one D added and click on the product number okay there's our date serial number but if we change that to a date format it will look more like it okay and then equals status and click on a product number hey we've got a working thing here let's change this to oh I seven to nine I'm looking right here enter so we see that our description function works our D added function works and our new status function works so check that out anyway thanks for tuning in we just over oversimplified a simple vlookup and made it even easier for our users to use their own functions for your company for your private use whatever thanks for tuning in god bless

Channel: ExcelVbaIsFun

Views: 129,115

Rating: 4.8997216 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, VLOOKUP

Id: lU-I0WKPIG8

Length: 6min 44sec (404 seconds)

Published: Mon Mar 04 2013