Function FiscalYear4(dDate As Variant, Optional iStartMo As Long) As LongįiscalYear4 = IIf(Month(dDate) >= iStartMo, 1, 0) + Year(dDate) The point is that you will typically need to handle the cases when the user doesn't specify a variable if you have Optional arguments. The If statement will change the value to 13 if it is not specified and this will calculate a fiscal year that starts in January. In this case the value of the variable will be zero if it is not specified since that is the default for the Long integer data type. I also added an If statement to handle the scenario when the iStartMo argument is not specified. UDFs that you create can also have multiple arguments as well as optional arguments.įor example, in the code below, I've added the Optional keyword so that the iStartMo argument becomes optional and does not need to be specified. VTemp = IIf(Month(dDate) >= 10, 1, 0) + Year(dDate)Įnd Function Multiple and Optional Parameters However, User Defined Functions in VBA can have multiple lines of code.įor example, if we want to add a bit of error-handling to our code so that we get the words “Date not specified” anytime the function passes through data that isn't in date format, the code would look something like this: Private Function FiscalYear2(dDate As Variant) As Variant The example we just looked at is fairly straightforward. I've created a syntax guide to help you remember the order of the function's components. Here is the code that you can copy and paste:įunction FiscalYear(dDate As Variant) As LongįiscalYear = IIf(Month(dDate) >= 10, 1, 0) + Year(dDate) This code basically tells Excel that if the date month is on or after October, use the next year as the fiscal year, and if not, leave it as the year that is found in the date. For my example, I set the function like this: IIf (Month(dDate) >=10, 1, 0) + Year(dDate). The function name always needs to be set somewhere within the function, usually at the end. On the next line, we are going to set our function name. In this case, I use Long as the data type. Next, type “as” and specify the return data type. In my example I use dDate and then the data type is Variant because we might pass through text or other data that is not in date format. Next, open the parenthesis and specify the arguments. It can be any function name that isn't already in use. In my example, I've called it FiscalYear. This will be followed by the function name, which you designate. To write the function, go to the VB Editor and begin with the word Function.
If that date falls before October, it will return one value, and if it falls in or after October, we will get a different value.
In this image, you can see that I've called a function named FiscalYear in order to calculate the fiscal year based on the date found in cell E2. Like any other function, the UDF can be called from a cell formula. We're going to create a Fiscal Year UDF for a company that has a fiscal year beginning in October. So let's write a UDF to create our own function that returns the fiscal year. However, if this is a calculation you do often, you might want a shorter formula that is easier to write. Here is one example for a fiscal year starting in October. There are many ways to calculate the fiscal year from a date with existing functions in Excel. One example of a function that is not included in Excel is a function that returns the fiscal year. When you create your own custom function for Excel, it's called a UDF, a User Defined Function. But what do you do if the function you need isn't already built in? UDF-Examples.zip Download Create Custom Functions in ExcelĮxcel is a fantastic tool with SO many capabilities and functions.