Writing Your Own Functions In VBA. Writing Your Own Functions In VBA This page describes how to write your own worksheet functions in VBA.
While Excel provides a plethora of built-in functions, especially so if you include functions in the Analysis Took Pack (in Excel 2007, the functions that used to be in the ATP are now native Excel functions) you may find it useful to create your own custom function for things that Excel cannot (easily) do with the built-in functions. While it takes longer for Excel to calculate a VBA function than it does to calculate a worksheet formula, all else being equal, the flexibility of VBA often makes a VBA function the better choice. The rest of this page assumes that you are familiar with the basics of VBA programming. A User Defined Function (or UDF) is a Function procedure that typically (but not necessarily) accepts some inputs and returns a result. Function RectangleArea(Height As Double, Width As Double) As Double RectangleArea = Height * Width End Function =RectangleArea(A1,B1) Excel 2007 Tutorial for User Defined Functions (UDFs)
Microsoft Excel supports macros but it also supports library functions, known as User Defined Functions (UDF).
Library functions are less risky than macros because they must return a value to a cell. This is a quick tutorial, mostly for my students, but as usual for anybody who’s interested. Microsoft Excel User Defined Functions (UDFs) are different than standard VBA macros. They’re behavior is restricted. You can’t access other cells in a workbook, and may only return a value (also known as an expression) to the cell that uses the formula.
Here are the steps to configure your Excel 2007 environment to work with Visual Basic for Applications (VBA) and UDFs. Displaying Excel’s Developer Tab. How to Create a User Defined Function in Microsoft Excel. Edit Article Edited by Mauricio Peccorini, Krystle, ShrUtiable, Queenkiwi and 3 others Even when Excel has a lot, probably hundreds, of built in functions like SUM, VLOOKUP, LEFT, and so on, once you start using Excel for more complicated tasks, you will often find that you need a function that doesn't exist.
Don't worry, you're not lost at all, all you need is to create the function yourself. Ad Steps 1Create a new workbook or open the workbook in which you want to use your newly created User Defined Function (UDF). 7Verify the result is Ok after using the function several times to ensure it handles different parameter values correctly: Ad Tips Whenever you write a block of code inside a control structure like If, For, Do, etc. make sure you indent the block of code using a few blank spaces or tab (the style of indentation is up to you).
Warnings Due to security measures, some people may disable macros. Creating custom functions. Important notice for users of Office 2003 To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3).
The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003. In this article Sample files You can download sample files that relate to excerpts from Microsoft Office Excel 2003 Inside Out from Microsoft Office Online. Although Microsoft Excel includes a multitude of built-in worksheet functions, chances are it doesn't have a function for every type of calculation you perform. Refresh Excel VBA Function Results. Non-volatile randbetween() I wrote: I was going to suggest the use of non-volatile VBA UDFs.
But it appears that the formulas that call them are recalculated when the file is saved if Automatic calculation mode is selected. Aha! My bad! I was making changes in another VBA module just before the saves, and apparently that causes all formulas with UDFs to be recalculated when the workbook is subsequently changed or saved. So you might consider using non-volatile VBA UDFs like myRAND and myRANDBETWEEN below. Call myRAND and myRANDBETWEEN the same way that you call Excel RAND and RANDBETWEEN, namely RAND() and RANDBETWEEN(low,high).
The first set of UDFs below use VBA Evaluate to utilize the Excel RAND and RANDBETWEEN functions. However, VBA Evaluate is very slow relatively. Caveat: As noted above, apparently changes to any VBA module (in the VBA project?) UDFs using VBA Evaluate.... Function myRAND() As Double myRAND = Evaluate("rand()") End Function.