How to Create User Defined Functions in Microsoft Excel

2024 Author: Jason Gerald | [email protected]. Last modified: 2023-12-16 10:50

Although Excel already has hundreds of built-in functions such as SUM, VLOOKUP, LEFT, and so on, the available built-in functions are usually not sufficient enough to do fairly complex tasks. However, don't worry because you only need to create the required functions yourself.

Step

Step 1. Create a new workbook or open the workbook that you want to process with User Defined Functions (UDF)

Step 2. Open the Visual Basic Editor in Microsoft Excel via Tools->Macro->Visual Basic Editor (or press the shortcut Alt+F11)

Step 3. Click the Module button to add a new module to your worksheet

You can create a UDF in a workbook without adding a new module, but the function will not work in other worksheets in the same workbook.

Step 4. Create the "head" or "prototype" of your function

The function prototype must follow the following structure:

public function "Function Name" (parameter1 As type1, parameter2 As type2) As Result type.

Prototypes can have as many functions as possible, and their types can be all basic data types or Excel object types in the form of Range. You can think of parameters as “operants” (operators) that the function will act on. For example, when you write SIN(45) to calculate the sine of 45 degrees, the number 45 will be taken as a parameter. Then, the function code will use those values to perform calculations and display the results.

Step 5. Add the function code to ensure that you: 1) use the value given by the parameter; 2) pass the result to the function name; and 3) close the function with the sentence " end function ". Learning to program in VBA or in any other language takes a lot of time and detailed guidance. Fortunately, these functions usually have small code blocks and don't make much use of programming language features. Here are some elements of the VBA language that can be used:

1. The If (if) block, which allows you to execute a portion of code only if the condition is met. As an example:

Public Function Course Results (As Integer value) As String

If value >= 5 Then

Course Results = "Accepted"

Else

Course Results = "Rejected"

End If

End Function

Notice the elements in the If code block:

IF condition THEN code ELSE code END IF

2. . You may leave out the Else keyword along with the second part of the code as it is optional.
3. The Do (do) block, which executes a portion of While (while) or Until (until), when or until the condition is met. As an example:

Public Function BilPrima(value As Integer) As Boolean

Dim i As Integer

i = 2

BilPrima = True

Do

If value / i = Int(value / i) Then

BilPrima = False

End If

i = i + 1

Loop While i < value And NumberPrima = True

End Function

Look again at the elements:

DO code LOOP WHILE/UNTIL condition

4. . Also note the second line which "declares" the variable. You can add variables to your code for later use. Variables act as temporary values in the code. Finally, note the function declaration as BOOLEAN, which is a data type that only allows TRUE or FALSE values. This method of determining prime numbers is far from optimal, but the code has been written in such a way that it is easy to read.
5. For block (to), which executes a certain amount of code. As an example:

Public Function Factorial(As Integer value) As Long

Dim results 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

Look again at the elements:

FOR variable = lower limit TO upper limit of code NEXT

. Also, note the additional ElseIf element in the If statement, which allows you to add more options to the code being executed. Finally, consider the “result” function and variable declared as Long. The Long data type allows much larger values than Integer.

Below is shown the code for a function that converts small numbers into words.



Step 6. Return to the workbook and use the function by writing the “equals” symbol (=) followed by the name of the function in the cell

Write the opening brackets (“(“) after the function name, using the sign coma to separate the parameters, and end with closing brackets (“)”). As an example:

=NumberToLetter(A4)

. You can also use homemade formulas by searching for them in categories User Defined inside the Insert Formula option. You just click the button Fx to the left of the formula bar. There are three types of parameter forms in functions:

1. A constant value that is typed directly into the cell formula. In this case, the text (string) must be quoted.
2. Cell references, for example B6 or range like A1:C3 (parameter must be data type “Range”)

3. Another function that is enclosed in your function (your function can also be enclosed in another function), for example: =Factorial(MAX(D6:D8))

   

   Step 7. Make sure the results are correct

   Use it several times to make sure that the function is able to handle various parameter values correctly:

   Tips

   • When writing code blocks in control structures such as If, For, Do, etc., make sure you indent (insert the left line border slightly inside) the code block by pressing the spacebar several times, or tab. This will make the code easier to understand and errors will be much easier to find. In addition, the increase in functionality becomes easier to make.
   • If you don't know how to write code for functions, read the article How to Write a Simple Macro in Microsoft Excel.
   • Sometimes, functions don't need all the parameters to calculate the result. In this case, you can use the Optional keyword before the parameter name in the function header. You can use the IsMissing(parameter_name) function in your code to determine whether a parameter is assigned a value or not.
   • Use unused names as functions in Excel so that no functions are overwritten and deleted.
   • Excel has many built-in functions and most calculations can be performed using these built-in functions, either individually or all at once. Make sure you take a look at the list of available functions before you start coding yourself. Execution can be done faster if you use built-in functions.

   Warning

   • For security reasons, many people disable macros. Make sure you notify your workbook recipients that the submitted workbook has macros, and that these macros will not harm their computers.
   • The function used in this article is not the best way to solve the related problem. The example is used to explain the use of language control structures.
   • VBA, like other languages, has several other control structures besides Do, If and For. The structure discussed here only describes what can be done in the function's source code. There are many guides on the internet that can be used to help you learn VBA.