Creating a User-Defined Function in Excel using VBA :www.bluepecan.co.uk Creating a User-Defined Function in Excel using VBA Microsoft Office Training Excel Training


Slide 2:www.bluepecan.co.uk Excel includes many different functions that help you complete calculations, but have you ever wished for a function that that doesn’t exist in Excel? If you have, this tutorial will explain how to create a function DIY style.


Slide 3:www.bluepecan.co.uk You create custom functions in the Visual Basic Editor (VBE) which you can get to by clicking Tools > Macro > Visual Basic Editor or by using the shortcut key ALT F11. If you are using Excel 2007 click on the Developer ribbon and then click on the Visual Basic button. Once in the VBE environment you will need to create a module to hold your function. Click Insert > Module


Slide 4:www.bluepecan.co.uk A function is defined with a name (for the function) and if necessary between 1 and 60 arguments. For example the Excel worksheet function VLookup has 4 arguments.


A function with no arguments :www.bluepecan.co.uk A function with no arguments Several VBA functions such as rand() have no arguments. In the same way you can create custom functions that have no arguments. The following function will display the path and filename of the active workbook.


Slide 6:www.bluepecan.co.uk Function File() File = ActiveWorkbook.FullName End Function Notice the function starts and ends with ‘Function’ rather than sub.


Slide 7:www.bluepecan.co.uk Enter =File() into a worksheet to see the result. or Click on fx (Insert Function) and open the User Defined category to see your function listed here


Slide 8:www.bluepecan.co.uk The next function displays the username (as set in Tools | Options | General) Function User() User = Application.username End Function


A Custom Functions with Arguments :www.bluepecan.co.uk A Custom Functions with Arguments The following function simply calculates a value plus VAT. In an empty worksheet create a column of prices. Then switch to the VBE environment and in a module create the following custom function. Notice that with this function you need to place arguments in the brackets after the function name. The sales argument will require you to select the cell containing the sales value for which you wish to add the VAT to.


Slide 10:www.bluepecan.co.uk Function vat(sales) vat = sales * 1.15 End Function Use the VAT function to calculate the VAT inclusive value in your list of prices


Slide 11:www.bluepecan.co.uk We could also add a markup value as part of our function by adding a second argument.


Slide 12:www.bluepecan.co.uk Function retail(sales, markup) retail = sales * (markup + 1) * 1.15 End Function


Slide 13:www.bluepecan.co.uk Enter a markup percentage on your worksheet and refer to this value in the second argument of the function (separated from the first by a comma). You can always use the functions argument dialogue box to enter cell references or values.


Slide 14:www.bluepecan.co.uk The following function calculates the amount of time that has elapsed between a start time and the end time. The function also works for times over two separate days, in other words when the start time is greater than the end time.


Slide 15:www.bluepecan.co.uk Function CalTime(StartTime, EndTime) If StartTime > EndTime Then CalTime = EndTime - StartTime + 1 Else CalTime = EndTime - StartTime End If End Function


Slide 16:www.bluepecan.co.uk See this Excel training tutorial on the Blue Pecan website