How To Create Custom Functions In Excel With Vba

In this video I explain how to write UDFs (User Defined Functions) for Excel in VBA. UDFs allow us to create custom functions that can be used in formulas. If a function doesn't exist in Excel, then we can write our own to perform calculations.

For this example I show how to create a function that calculates the fiscal year based on a date. There is not fiscal year function built into Excel, so we write our own with VBA.

I also explain the pros and cons of UDFs. It's important you know where to store the code for the UDF, especially when sending files to other users, and I explain everything you need to know in the video.

Additional Resources:

00:00 Introduction
00:10 UDFs
00:55 VBA Editor

[shadeburst]

As a refresher after a long break, this was very clear. You got into the meat of it immediately and within two minutes it was all coming back to me. Thank you.

"Show us what you're going to show us, show us, and show us what you've shown us." The basic lesson plan is still the best. I'll be back.

[rcu]

Oh man! I have used macros to create workbooks for my job, and I spent days copying and pasting more than 1, 000s of manual formulas within VBA for a project, and all I had to do was a UDF?
It's great to learn new things though! I can see myself doing that project in 10 minutes now. LOL!
Thank you very much for these videos!! There are extremely helpful!

[JohnOvens]

Thanks, Jon, I have not used UDFs in the past but see that they could help with complex formulas, so will use if future. The video was most helpful.

[chrismarsden]

Evening John,

Thank you for the video, I found it very useful, informative and most importantly everything seemed to made sense - Thank You

[mikemoore]

You've done an exceptional job with this video. Very clear and concise instruction!

[bjj]

It was probably too basic to mention in the video, but one step skipped over here was how to open the window for writing the function in the first place. So, back after getting access to the Visual Basic screen (Alt+F11), the user needs to choose "Module" from the "Insert" menu. That gives the user a fresh page for writing the function.

[vladimirmeono]

Great video Jon, it helped me a lot. Thanks for sharing your knowledge.

[heatherr]

Thanks Jon, I've been wanting to get into UDFs, going to have to explore this more. As always, a great video!

[wayneedmondson]

Hi Jon.. thanks for the video on UDFs. I've been experimenting with them and they are very handy. I've created UDFs to extract uniques from a Range, perform math (SUM, COUNT, AVERAGE, etc.) on a range based on the fill color or text color, extract the nth word from a string, etc. The possibilities are endless and there are many videos and blogs to help anyone interested in learning more. It is a bummer that UDFs don't have screen tips.. love to hear your workarounds for that. I've used your example of entering something descriptive in the cell on error. But, that does not help the user keep from making an error in the first place. Thanks again for the insights and Thumbs up!

[apriltrexieigot]

Thanks for this video Jon. Can you also help me with as to how I should formulate the function when it comes to tax calculation :)

[neilmadgwick]

Hi Jon. I was intrigued by the IIF statement in the code. When I went into Excel I could not find this function. Is it a function that is only available in VBA?

[cathycordes]

Can this function be used to make a due date default to a particular P/N when the PN is entered? I'm trying to set up a function to auto enter a date when I type in a P/N that defaults to 8, 20 or 30 days depending on the PN.

[ishitvaishnav]

I have addin for number to word for 2007 but not showing automatically I need to press shift f3 to call it pls help

[rdc]

14:15 is there a way to have the screen tip argument?