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.
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!
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!
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.
Must watch this video th-cam.com/video/P4Po-2AHybs/w-d-xo.html This is very important for all of you. This video is about 46 USER DEFINED FUNCTION which will encourage you.
Must watch this video th-cam.com/video/P4Po-2AHybs/w-d-xo.html This is very important for all of you. This video is about 46 USER DEFINED FUNCTION which will encourage you.
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?
Hi Neil, Great question! Yes, the IIF function with two i's is only available in VBA. However, the syntax is the same as the IF function in Excel. I believe the IIF function in VBA has two i's so it is not confused with the IF statement in VBA. I'll do a follow-up video on the differences. The main difference is that IIF is only going to return one result, similar to a formula in Excel. The IF statement has many more uses to run code based on conditions. It also has the ELSE and ELSEIF conditions. I hope that helps. Thanks! 🙂
@@ExcelCampus Does it stand for "Inline IF"? Or did I just make that up! For some reason, I rarely utilise the 1/0 for TRUE/FALSE in formulas. For example, your Excel formula could shorten to: =(MONTH(E2)>=10)+YEAR(E2) which I think looks quite nice and still readable. Annoyingly, VBA expresses as -1 for TRUE for some reason and so: FiscalYear = Abs(Month(dDate)>=10)+Year(E2) would be the equivalent, which slightly confuses things because of the additional ABS.
Hi Rico, Yes, I believe it does stand for Inline If. I'm not sure it's in the official documentation, but have seen it referred to as that. Great point on shortening the formula in Excel. Another option for True is a double negative, like the following. -(Month(dDate) >= 10) + Year(dDate) Always a million ways to solve a problem in Excel... That's what makes it fun! 🙂
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.
![[TH] JFT vs RLF - VCT Ascension Pacific - Day 2](http://i.ytimg.com/vi/PuIH7w_jO6c/mqdefault.jpg)
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.
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!
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.
Evening John,
Thank you for the video, I found it very useful, informative and most importantly everything seemed to made sense - Thank You
You're welcome, Chris ! 😀
You've done an exceptional job with this video. Very clear and concise instruction!
Glad you liked it 😊
Great video Jon, it helped me a lot. Thanks for sharing your knowledge.
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!
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.
Thanks Jon, I've been wanting to get into UDFs, going to have to explore this more. As always, a great video!
Thanks Heather! I appreciate your support! 🙂
Must watch this video
th-cam.com/video/P4Po-2AHybs/w-d-xo.html
This is very important for all of you.
This video is about 46 USER DEFINED FUNCTION which will encourage you.
Thanks !!! Great video!
Well explained
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 :)
Must watch this video
th-cam.com/video/P4Po-2AHybs/w-d-xo.html
This is very important for all of you.
This video is about 46 USER DEFINED FUNCTION which will encourage you.
@@PiyushMedicalKnowledge I watched your videos it is really helpful
Thanks, Toby
Thanks for the knowledge...
Thanks for sharing!
Thanks Jon for the share.
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?
Hi Neil, Great question! Yes, the IIF function with two i's is only available in VBA. However, the syntax is the same as the IF function in Excel. I believe the IIF function in VBA has two i's so it is not confused with the IF statement in VBA.
I'll do a follow-up video on the differences. The main difference is that IIF is only going to return one result, similar to a formula in Excel. The IF statement has many more uses to run code based on conditions. It also has the ELSE and ELSEIF conditions.
I hope that helps. Thanks! 🙂
@@ExcelCampus Does it stand for "Inline IF"? Or did I just make that up!
For some reason, I rarely utilise the 1/0 for TRUE/FALSE in formulas. For example, your Excel formula could shorten to:
=(MONTH(E2)>=10)+YEAR(E2)
which I think looks quite nice and still readable. Annoyingly, VBA expresses as -1 for TRUE for some reason and so:
FiscalYear = Abs(Month(dDate)>=10)+Year(E2)
would be the equivalent, which slightly confuses things because of the additional ABS.
Hi Rico,
Yes, I believe it does stand for Inline If. I'm not sure it's in the official documentation, but have seen it referred to as that.
Great point on shortening the formula in Excel. Another option for True is a double negative, like the following.
-(Month(dDate) >= 10) + Year(dDate)
Always a million ways to solve a problem in Excel... That's what makes it fun! 🙂
well done!
I have addin for number to word for 2007 but not showing automatically I need to press shift f3 to call it pls help
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.
14:15 is there a way to have the screen tip argument?
What about the tool tip ? Any ideas ?
Great stuff, how learn to debug?
Could you create xlookup in xl 2013?
Hello, can I have your help please.