How to Create a User Defined Function in Excel

PDF download Download Article
Take advantage of custom functions in Excel with this handy guide
PDF download Download Article

Microsoft Excel has many built-in functions, such as SUM, VLOOKUP, and LEFT. As you start using Excel for more complicated tasks, you may find that you need a function that doesn't exist. That's where custom functions come in! This wikiHow teaches you how to create your own functions in Microsoft Excel.

Things You Should Know

  • Begin creating a user defined function by selecting New Module within the "Insert" menu.
  • Create your function's header by replacing "FunctionName" with a name that outlines the parameters of your function.
  • Be sure to write "EndFunction" at the end of your function's code.

Steps

PDF download Download Article
  1. Double-click the workbook in which you want to use the custom-defined function to open it in Excel.
  2. This opens the Visual Basic Editor.
    Advertisement
  3. This opens a module window in the right panel of the editor. [1]
    • You can create the user defined function in the worksheet itself without adding a new module, but that will make you unable to use the function in other worksheets of the same workbook.
  4. The first line is where you will name the function and define our range. [2] Replace "FunctionName" with the name you want to assign your custom function. The function can have as many parameters as you want, and their types can be any of Excel's basic data or object types as Range: 
     Function 
 FunctionName 
 ( 
 param1 
 As 
 type1 
 , 
 param2 
 As 
 type2 
 ) 
 As 
 return 
 Type

    • You may think of parameters as the "operands" your function will act upon. For example, when you use SIN(45) to calculate the Sine of 45 degree, 45 will be taken as a parameter. Then the code of your function will use that value to calculate something else and present the result.
  5. Make sure you use the values provided by the parameters, assign the result to the name of the function, and close the function with "End Function." Learning to program in VBA or in any other language can take some time and a detailed tutorial. However, functions usually have small code blocks and use very few features of the language. Some useful elements are:
    • The If block, which allows you to execute a part of the code only if a condition is met. Notice the elements in an If code block: IF condition THEN code ELSE code END IF . The Else keyword along with the second part of the code are optional: 
       Function 
 Course 
 Result 
 ( 
 grade 
 As 
 Integer 
 ) 
 As 
 String 
 If 
 grade 
 >= 
 5 
 Then 
 CourseResult 
 = 
 "Approved" 
 Else 
 CourseResult 
 = 
 "Rejected" 
 End 
 If 
 End 
 Function


    • The Do block, which executes a part of the code While or Until a condition is met. In the example code below, notice the elements DO code LOOP WHILE/UNTIL condition . Also notice the second line in which a variable is declared. You can add variables to your code so you can use them later. Variables act as temporary values inside the code. Finally, notice the declaration of the function as BOOLEAN, which is a datatype that allows only the TRUE and FALSE values. This method of determining if a number is prime is by far not the optimal, but I've left it that way to make the code easier to read. 
       Function 
 IsPrime 
 ( 
 value 
 As 
 Integer 
 ) 
 As 
 Boolean 
 Dim 
 i 
 As 
 Integer 
 i 
 = 
 2 
 IsPrime 
 = 
 True 
 Do 
 If 
 value 
 / 
 i 
 = 
 Int 
 ( 
 value 
 / 
 i 
 ) 
 Then 
 IsPrime 
 = 
 False 
 End 
 If 
 i 
 = 
 i 
 + 
 1 
 Loop 
 While 
 i 
 < 
 value 
 And 
 IsPrime 
 = 
 True 
 End 
 Function
    • The For block executes a part of the code a specified number of times. In this next example, you'll see the elements FOR variable = lower limit TO upper limit code NEXT . You'll also see the added ElseIf element in the If statement, which allows you to add more options to the code that is to be executed. Additionally, the declaration of the function and the variable result as Long . The Long datatype allows values much larger than Integer : 
       Public 
 Function 
 Factorial 
 ( 
 value 
 As 
 Integer 
 ) 
 As 
 Long 
 Dim 
 result 
 As 
 Long 
 Dim 
 i 
 As 
 Integer 
 If 
 value 
 = 
 0 
 Then 
 result 
 = 
 1 
 ElseIf 
 value 
 = 
 1 
 Then 
 result 
 = 
 1 
 Else 
 result 
 = 
 1 
 For 
 i 
 = 
 1 
 To 
 value 
 result 
 = 
 result 
 * 
 i 
 Next 
 End 
 If 
 Factorial 
 = 
 result 
 End 
 Function
  6. Once you've created your function, close the window to return to your workbook. Now you can start using your user-defined function.
  7. First, click the cell in which you want to enter the function. Then, click the function bar at the top of Excel (the one with the fx to its left) and type =FUNCTIONNAME() , replacing FUNCTIONNAME with the name you assigned your custom function.
    • You can also find your user-defined formula in the "User Defined" category in the Insert Formula wizard—just click the fx to pull up the wizard.
  8. For example, =NumberToLetters(A4) . The parameters can be of three types:
    • Constant values typed directly in the cell formula. Strings have to be quoted in this case.
    • Cell references like B6 or range references like A1:C3 . The parameter has to be of the Range datatype.
    • Other functions nested inside your function. Your function can also be nested inside other functions. Example: =Factorial(MAX(D6:D8)) .
  9. The results will display in the selected cell.
    10. Advertisement

Community Q&A

Search
Add New Question
  • Question
    How can I use these functions in all Excel files?
    Igal Livne
    Community Answer
    Save the workbook with the custom class as "Excel Add-In (*.xlam")," by default Excel will take you to "Addins" folder. Go to Excel Options > Add-ins > Manage: "Excel Addins" - press "Go..." button. Browse for your newly create xlam file.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 5 Helpful 11
  • Question
    How can I do well in exams?
    Community Answer
    Read the directions several times, leaving time for you to absorb between readings. Also practice writing VBA to do various things.
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 4 Helpful 7
  • Question
    How do I know what to write as the function code?
    Community Answer
    In order to create functions, you need a skill called "programming". Excel macros are written in a language called "Visual Basic for Applications", which you will need to learn to be able to write macros. It's quite easy once you've got the hang of it though!
    Thanks! We're glad this was helpful.
    Thank you for your feedback.
    If wikiHow has helped you, please consider a small contribution to support us in helping more readers like you. We’re committed to providing the world with free how-to resources, and even $1 helps us in our mission. Support wikiHow
    Yes No
    Not Helpful 16 Helpful 12
Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Video

      Tips

      • Use a name that's not already defined as a function name in Excel or you'll end up being able to use only one of the functions.
        Thanks
        Helpful 2 Not Helpful 3
      • 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 the Tab key. That will make your code easier to understand and you'll find a lot easier to spot errors and make improvements.
        Thanks
        Helpful 0 Not Helpful 0
      • If you don't know how to write the code for a function, see How to Write a Simple Macro in Microsoft Excel .
        Thanks
        Helpful 0 Not Helpful 0
      Show More Tips
      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!
      Advertisement

      Warnings

      • The functions used in this article are, by no means, the best way to solve the related problems. They were used here only to explain the usage of the control structures of the language.
        Thanks
        Helpful 1 Not Helpful 0
      • VBA, as any other language, has several other control structures besides Do, If and For. Those have been explained here only to clarify what kind of things can be done inside the function source code. There are many online tutorials available where you can learn VBA.
        Thanks
        Helpful 1 Not Helpful 0
      • Due to security measures, some people may disable macros. Make sure you let your colleagues know the book you're sending them has macros and that they can trust they're not going to damage their computers.
        Thanks
        Helpful 1 Not Helpful 1
      Advertisement

      You Might Also Like

      How to
      Add Two Numbers in Visual Basic.NET
      How to
      Write a Simple Macro in Microsoft Excel
      How to
      Create Pivot Tables in Microsoft Excel to Analyze Data
      How to
      Create a Currency Converter With Microsoft Excel
      How to
      Apply Conditional Formatting in Excel
      Easy AutoFilter Basics for Beginners
      How to
      Merge Cells in Microsoft Excel: A Quick Guide
      How to
      Unprotect an Excel Sheet
      How to
      Unhide All Rows and Columns in Microsoft Excel
      How to
      Use If‐Else in Microsoft Excel: Step-by-Step Tutorial
      How to
      Insert Pictures in Excel That Automatically Size to Fit Cells
      How to
      Create a Mortgage Calculator With Microsoft Excel
      How to
      Create a Graph in Excel
      How to
      Show Hidden Columns in Microsoft Excel & Unhide Column A
      Advertisement

      References

      1. ↑ https://support.microsoft.com/en-us/office/create-custom-functions-in-excel-2f06c10b-3622-40d6-a1b2-b6748ae8231f
      2. ↑ https://www.excel-easy.com/vba/examples/user-defined-function.html

      About This Article

      Article Summary X

      1. Open Excel.
      2. Press Alt + F11 to open the Visual Basic editor.
      3. Create the function's name and set the parameter types.
      4. Use VB code to write your function.
      5. Close the editor.
      6. Run your function as you would other functions.

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 655,216 times.

      Reader Success Stories

      • G. Weiss

        Jan 31, 2017

        "Well done with lots of detail."
      Share your story

      Is this article up to date?

      Advertisement