Excel VBA Introduction Part 18 - Creating Functions

[@ghoomketu.forever]

These are probably the best tutorials I've seen on youtube. Kudos!!

[@janezklun]

After more than seven years, still great tutorial, thank you

[@WiseOwlTutorials]

Thanks Janez, happy to hear that you still found this one useful, thanks for taking the time to leave a comment!

[@USSBARBEL]

I learned so much from this - Thank you for taking the time to put together and share! Deeply in your debt.

[@WiseOwlTutorials]

Happy to hear that Steve! Pleased that you found it useful, thank you for watching and taking the time to pass on your comments!

[@uditkapoor2611]

What a co-incidence. Watching this video on your brother's birthday. Happy birthday to him. And a big thank you for all the videos. :D :D :D

[@ashokkandukury2870]

nOW i AM ABLE TO DO SOME THING NEW IN EXCEL WITH VBA ; YOUR LECTURES INSPIRED ME TO DO THAT. WONDERFUL......tHANK YOU aNDREW.

[@tiisetsomashele1494]

Brilliant! Thank you so much. I only started understanding function procedures after I had watched this video

[@wayneedmondson1065]

Hi Andrew.. thanks for this great tutorial on creating and using custom functions in VBA. Here's a tip.. when using a custom function on the worksheet, you can use the keyboard shortcut CTRL+SHIFT+A after opening the function.. meaning you've typed in a cell: =CustomDate( .. and you will see the argument list for the function appear, as in: =CustomDate(DateToFormat,IncludeTime). This is the same behavior as for a built-in EXCEL function and it also works for custom functions. It is not quite as good as a true argument list like on an in-built function, but it can help if you forget your arguments or the order in which you specified them. Thought this tip might be useful to anyone who does not know about it. Thanks again for all of your great tutorials and Thumbs up!

[@WiseOwlTutorials]

Wayne, you have just blown my mind! I knew that you could hit SHIFT + F3 to show the arguments dialog box but I didn't know about this one, that's fantastic! Thanks for taking the time to share it, very useful!

[@wayneedmondson1065]

@@WiseOwlTutorials Thanks Andrew.. before discovering it, I would sometimes design an error handler in my functions that would insert a message in the cell indicating the correct form and arguments for the function when an error was encountered. CTRL+SHIFT+A (or SHIFT+F3) is so much easier for a reminder vs. a formal error handler, although one can still be useful at times. Even though functions are designed to return values and not trigger events, I discovered that it is possible to use MsgBox and InputBox with functions if you really want to communicate with the user. There is always a surprise in VBA.. it can do so many things. Thanks again for your great videos. I've learn a lot already and looking forward to more, as I re-play and study each more closely. Thumbs up!

[@WiseOwlTutorials]

I have certainly used similarly convoluted methods to workaround other problems before discovering a hidden simple solution! It's great how much you can learn by doing things the hard way though! Happy to hear that you still find the videos useful, it's great knowing that they still help people!

[@beenay18]

Bob Ross of VBA! Thanks.

[@WiseOwlTutorials]

Well now, you've just made me a happy little cloud :D

[@blauertoons]

Thank you sooo much for creating this =)) I have learned an incredible amount from your vids

[@WiseOwlTutorials]

You're welcome Tommie, happy to hear you found the videos useful and thanks for taking the time to leave a comment!

[@amandafernandesramospasson5862]

your tutorials are great! thanks. i've learnt a lot!

[@WiseOwlTutorials]

Thanks Amanda, you're really getting through these videos quickly!

[@yashrajkanaskar2452]

Awesome stuff there man ! You solved my error issue! Thanks

[@WiseOwlTutorials]

Happy to hear that it helped you, thanks for watching!

[@illabillatilla]

thank you sir for sharing valuable knowledge

[@karlmin8471]

It's great for programmers,we can even make a game with excel VBA.

[@diegothaumaturgo]

Yes using random function to let ppl think that the excel has AI.

[@edtardaguila3599]

the video and tutorial was superb, this is useful specially if you running your code several times with dynamic results thank you for sharing this cheers!

[@ouzytheoriginal]

very good clarified some of questions in my mind thanks

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@vivianlu3021]

This is a great course. I want to know how many people have watched all the videos?

[@nuhzakir8896]

I like your videos.Thank you.These are very useful.

[@pradeeprawatvlogs8358]

your videos are awesome I am interested in the training programme but I can't afford and on the top of it I cannot visit the London as again it is out of my pocket but your youtube videos and blogs on the website help me a lot thanks a lot sir I express heartfelt gratitude to you

[@rihannababez]

Thank you for sharing valuable information! Was searching alot for a proper video which would solve my doubt..explained very well👌🏻🙌🏻

[@ashrafkader1972]

Great explanation-regards

[@WiseOwlTutorials]

Thanks!

[@maurocastagnera8949]

Dear Andrew, given that I think you're really a genius, I would like to ask you how you can remind yourself of all this about the VBA! I've only seen a dozen of your videos but I already can not remember everything! I believe your students are really lucky to have a good teacher like you! I hope one day to come to England and congratulate you personally. It would be an honor for me. Thank you very much.

[@amnartrittirong9379]

Great tutorial

[@WiseOwlTutorials]

Thanks, glad you enjoyed it!

[@itspapu]

Great tutorial for experienced. thank you

[@WiseOwlTutorials]

You're welcome, Pashant! Thank you for watching!

[@heathergray4880]

For anyone who wants to feel like a real programmer, when he breaks out the existing code into generic functions, that's calling "refactoring". Now you can feel cool when you say "I'm refactoring my code!" :P

[@b.petrushchak]

Thank you for an interesting video!

[@grigoriefimovic1547]

Hi Andrew...when you call the function from the spreadsheet, you can actually see the list of parameters to pass in by holding down ctrl and shift, then tapping the letter A on the keyboard. At least that works for me

[@grigoriefimovic1547]

You''re welcome Andrew...hope you incorporate that in future videos...

[@321chiru]

Hi Andrew It's really helpful for us. Could you please provide web scrapping videos.

[@AshishKumar-cw2so]

Thanks for share this video But my question is that how to enable tooltips of UDF functions. Please sir share information about this topic

[@sukumars9168]

It is important to note how at timeline 2:30 the lowercase of customdate changed to CustomDate which is name of function.

[@wasimbader9170]

Thank you again

[@divyal5852]

Excellent!

[@AmbaPrasadReddy]

Hi Andrew, Instead of using the functions cant we just create labels as you did in Error Handing tutorial and call it when ever needed? If no, can you please tell me the reason for that? Thanks, Prasad

[@knowmore4810]

hi Andrew, ur video's are really helpful tq soo much. While working on function I n stuck in problem. After entering ?customdate in immediate window I don't get any value return even when the code's are correctly written request u to help me out thank you

[@markdandrea2027]

Range("E1").Select Do ActiveCell.Offset(1, 0).Select If ActiveCell.Value = 0 Then ActiveCell.EntireRow.Delete ActiveCell.Offset(-1, 0).Select End If Loop Until ActiveCell.Value = "" Hi Andrew, This code works well until it gets to the end of the data, first blank row. It doesn't end the loop. Any suggestions? Thanks,

[@shahimkhlaifat]

Dear Andrew, please have a look at this, it gives an error when refer to the range a:a Sub chang() Application.ScreenUpdating = False Dim i As Integer For i = 4 To 11 If i = 9 Then i = 10 Worksheets(i).Select End If Worksheets(i).Range("A:A").Find(what:="Month Average").Select ActiveCell.Offset(-2, 0).Select ActiveCell.EntireRow.Select Selection.Insert ActiveCell.Offset(1).EntireRow.Copy ActiveCell.EntireRow.PasteSpecial xlPasteValues ActiveCell.Offset(2).EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow.PasteSpecial xlPasteValues Worksheets(i).Range("A:A").Find(what:="Month Average").Select ActiveCell.Offset(-1, 0).Select ActiveCell.Value = DateAdd("d", 1, ActiveCell) Next i End Sub

[@shahimkhlaifat]

Shahim Khlaifat oh , I have to get the (Worksheets(i).Select) out of if statement

[@patcalasukal]

thanks for this! is your experience with cleaning up Sub with Funtions faster than putting all conditions in a Sub?

[@WiseOwlTutorials]

You're welcome, Pat! I don't think that you will notice any difference in speed using this technique. It will help to create much better organised code and will make you think more clearly about the structure of complicated procedures. This can help to make it quicker and easier to maintain your code and make changes to it later. I hope that helps and thanks for the question!

[@patcalasukal]

@@WiseOwlTutorials wow thanks for replying! Was giddy when i saw your notification haha! You have a great channel

[@WiseOwlTutorials]

@@patcalasukal 😀 you're very welcome Pat, happy to hear you're enjoying the channel and thank you for the comments!

[@bruce2357]

Regarding typing =FunctionName( into a cell and not getting the argument tool tip: Was there a previous version of Excel that actually show the tool tip? I remember writing some custom functions years ago and I don't remember this happening. Maybe I was picking them from the function list. The tool tip not showing up is quite annoying. Any information on whether it will ever be changed? It seems like it would be a given that people would want this feature when they type their custom function name into a cell.

[@sukumars9168]

It is important to note at timeline 6:40 , how the date ( enclosed within # ) changed format of itself.

[@sridharkota6969]

I just want to know the difference between the subroutine and function. Its been confusing me a lot to fail me understand. Kindly help on this.....

[@irynamusiiovska828]

Thank you

[@AbhisheakSaraswat]

How we can create the function with parameters, and when I am using the same function in excel then all the parameters should be display, plz help me on this.

[@CyberAbyss007]

Great videos! Thank you so much. If you added payment support for PayPal I would have donated.

[@scotolivera8207]

Hello sir, I will appreciate a lot if could you please help me with this issue, How can I put a tooltip on a user-defined function when I call it from inside the worksheet. cannot thank you enough for your videos.

[@gregk6112]

Thank you for doing this, I am learning. I have a question about this specific lesson. I tried to enter the function on Excel(I have version 2013) and the formula was thus: =CustomDate(Now(), True). When I completed writing out the function, the matched parenthesis, (), were red in the formula indicating a problem. When I hit enter, the answer was given as zero. I can't figure out what is wrong. Any suggestions? Thanks again.

[@TheFikaky]

Please help on this: ........ Range(ActiveCell, ActiveCell.End(xlToRight)).Copy Worksheets(XXXXXX).Select Range("A1").End(xlDown).Offset(1, 0).Activate ActiveCell.PasteSpecial LOOP This is a part of code from one of your previous videos. XXXXX would in this case be the filmlength. Everything works fine, but I can't go through this part of the code (i get info: argument non-optional)..in previous videos this would create and copy data from one sheet to several sheets based on filmlength, but with a function separated I can't use the filmlength here as before. How can one avoid this, is there a way to do this somehow? Pls help, thanks

[@swapnilgholap5567]

It's very useful. Just one question. I wan to make a macro based automatic roster based on the availability of the resources. In-total there are 3 shifts and 14 employees. Is it possible to make it using vba macro and custom functions.

[@TonyDiaz.]

Hi Andrew, I've learned a lot from the videos, you're quite awesome in teaching. I have a question: how would I go on by using the ParamArray argument in my custom function? I know it's a variant, and it's used to pass any number of arguments, but I'd like to know how to use it. Thanks my friend.

[@TonyDiaz.]

Thanks for your example; I developed a worksheet UDF, similar to VLOOKUP, but instead of searching based on the column index, it searches based on the column header. I basically have this: TLOOKUP(ver_value,hor_value,ver_search,hor_search) ver_value would be the value you would be looking for in a vertical range, so this parameter is of a Variant data type, same with the hor_value, which is the value you would search for in a vertical range, i.e. the column headers on a table; ver_search and hor_search are the ranges to search the respective values in. The table should have unique values. Now, when I try concatenating two values in the first parameter, you know a trick to make my values unique, it works fine, but when I try it as an array formula, it throws an error. Either way, thanks for your response and your vids are so awesome. Even with the small amount of my knowledge, I come and watch your tutorials often, and I always leave with something new and awesome. Kudos!!

[@TonyDiaz.]

Thanks my friend! Just saw the Creating emails one, so helpful! Cheers!

[@gtr3440]

Nice video Sir ..

[@mohamedgaber8398]

Bravo

[@dbascb]

Thank you!

[@Mansoorsadat]

Magic!! :)

[@rodrigopd8994]

Hello Andrew, Do you know if it's possible make a "function" defined by user? By a Inputbox for instance?

[@rodrigopd8994]

***** Thank you very much Andrew, I'll think if it's really necessary do this and congratulations for share your videos.

[@TheMigider]

Hey andrew! How did you tidy up your indentation, as you did on 17:00?

[@FRANKWHITE1996]

thank you! :)

[@user-yd8xl6zu9f]

Hello Andrew, First...thank you for this course. It is very useful! I am having an issue with a subroutine if you could help me please? I have been saving each session in a new module. I went back to my 'DoUntilLoop' module to copy the Film Length subroutine like the one you have at the end of this video to put it in my latest (Functions) module for modification. I decided to run through the original subroutine (from its original 'DoUntilLoop' module location) to familiarise myself with it again. BUT, when the step-through got towards the bottom of the subroutine it opened up my 'Functions' module and started running through my latest function (the date function with optional parameters that you just did in this video)! That function had not even been created when I wrote the subroutine and the subroutine does not link to it. It isn't just a glitch as I turned everything off/back on and it continued to do the same thing. Why would it be doing this?? I have pasted the subroutine/function below for you to look at. I am totally baffled! Thank you Gemma Sub DoLoopExample() Dim FilmLength As Integer Dim FilmRating As String wsIntro5eg.Activate Range("A3").Select Do Until ActiveCell.Value = "" FilmLength = ActiveCell.Offset(0, 3).Value If FilmLength < 100 Then FilmRating = "Short" ElseIf FilmLength < 150 Then FilmRating = "Medium" Else FilmRating = "Long" End If ActiveCell.Offset(0, 4).Value = FilmRating ActiveCell.Offset(1, 0).Select Loop End Sub Function FunctionWithOptionalParameters(DateToFormat As Date, Optional IncludeTime As Boolean = False) As String If IncludeTime Then FunctionWithOptionalParameters = Format(DateToFormat, "dddd dd mmm yyyy hh:mm:ss") Else FunctionWithOptionalParameters = Format(DateToFormat, "dddd dd mmm yyyy") End If End Function

[@user-yd8xl6zu9f]

***** Hi Andrew, Thanks for getting back to me. It switches to the function between 'ActiveCell.Offset(0, 4).Value = FilmRating' and 'ActiveCell.Offset(1, 0).Select'. I wonder if things are messing up simply because it is now such a huge workbook? I have copied down all of the subroutines and additional explanations since your first video in the one workbook so everything is together. Perhaps wires are getting crossed due to the large number of modules, subroutines and worksheets? I'm using Excel 2010. G

[@user-yd8xl6zu9f]

***** Nope. And the last function from this video, that subs-in the function for the loop, also links to the same un-related function! I think, then, that there must just a glitch due to the amount of subroutines and functions stored in the same workbook. I have started a new workbook for your proceeding videos and everything there is working fine. I just wanted to check with you (since I don't know anyone personally who can help), to make sure that it is a glitch rather than a known issue that can occur when writing macros. Thanks for your help!

[@vexapple92]

hi. may i ask.. if i just want to add 4 and 8 together, how do I type out the function procedure for it??

[@vexapple92]

I typed this function called addtwo, where addtwo = arg1+arg2. How do I use this addtwo in my sub procedure?

[@mainaacharya4008]

Hi..my first output in VBA is 0000000.00 but I want output as 0,000,000.00..plealse help me...

[@scotolivera8207]

Hello sir , any ideas to make a tool tip appear for the udf ?

[@WiseOwlTutorials]

Hi George, as far as I know you can't add a tooltip that appears in Excel, but you can add information to the Function Arguments dialog box as described here stackoverflow.com/questions/4262421/how-to-put-a-tooltip-on-a-user-defined-function I hope that helps!

[@ashokkandukury2870]

hOW U DOIN ANDREW ! GREAT DIGITAL LECTURE

[@Search4Knowledge]

Great!

[@emaus8344]

Hi there, is there any chance to solve this issue: When I send this parameter "partName1" to this function, it changes its value to what function returns. Can I send a parameter to a function, so that parameter doesn't change its value in MainSub? as I need to use that parameter in another funcion. Function getBag(partName1 As String) As String partName1 = Left(partName1, 11) partName1 = Replace(partName1, "_", ".") getBennenung = partName1 End Function

[@emaus8344]

edit: getBag = partName1

[@emaus8344]

Found it: kzbin.info/www/bejne/mJWTqJSNh7CFm9U Thanks :)

[@badassack]

do functions work on a form? I have a macro that has a function in it and I had to enable "microsoft scripting runtime" in order to get it to work. I tried running that same sub in a form and it throws an error when it gets to the function part of the routine. is there another reference that needs to be checked? thanks for your input.

[@badassack]

Oh, the error is," Only comments may appear after end sub, end function, end property." I wasn't getting that error in the module.

[@badassack]

***** Oh how wise this owl is. You were right on the money. Had an extra End Function in there. Your awesome. Thank you again for all your help.

[@daved1113]

how do you do this with math functions?

[@Alexey0795]

6:46 i was born 19 Feb

[@kaustubhk8648]

Making easy things difficult When you are teaching how to create function, don't try to overshow your knowledge of other complicated functions/formulas R y tring to teach how to create function or are you trying to show off that you know complicated thing dddd dd mm yyyy? If you really want to teach it in proper spirit, pls show how to create function.... Paste special values, if particular cell has value "yes"