Case Study Custom Functions Example and Explanation

Let's build a custom function used to add two values. After we've created it,we'll use it on a worksheet.

Insert a new module in the VBE.Type the following function into the module. It is a function called Add that will total two numbers in different cells.The function has two arguments:

Add(Number1,Number2)

Number1 is the first number to add; Number2 is the second number to add:

Function Add(Number1, Number2) As Integer Add = Number1 + Number2 End Function

Let's break this down:

Function name—Add.

Arguments are placed in parentheses after the name of the function.This example has two arguments—

Number1 and Number2.

As Integer defines the variable type of the result as a whole number. Add =Number1 + Number2—The result of the function is returned. Here is how to use the function on a worksheet:

1. Type numbers into cells A1 and A2.

2. Select cell A3.

3. Press Shift+F3 to open the Paste Function dialog box (or choose Insert, Function from the main menu).

4. Select the User Defined category.

5. Select the Add function.

6. In the first argument box,select cell A1.

7. In the second argument box,select cell A2.

Congratulations! You have created your first custom function.You can easily share it because the users are not required to know how the function works.

Most of the same functions used on sheets can also be used in VBA and vice-versa. In VBA, though, you would call the user-defined function (Add) from a procedure (Addition):

Sub Addition () Dim Total as Integer

Total = Add (1,10) 'we use a user-defined function Add MsgBox "The answer is: " & Total End Sub

0 0

Post a comment