VBA Functions. A function is a group of statements that together perform a task. Every C program has at least one function, which is the main one, and all the most trivial programs can be defined as additional functions.
Program codes can be divided into separate functions depending on the user’s initiatives but logically the division usually is to allow each function to perform a specific task.
You can create a function or a user-defined function that can be used as regular functions in a worksheet in VBA, which is great when an existing excel function is not enough. In these situations, you can create your own user-defined function to suit your needs.
You can place a function into a module. Let take a look at the following steps:
Step 1: Navigate to the Developer checkbox and click on the Visual Basic editor.
Step 2: Click on the Insert button and select the Module option from the drop-down menu.
Syntax
Private Function FunctionName (ByVal arg1 As Integer, ByVal arg2 As Integer) FunctionName = arg1 + arg2 End Function
For example, Suppose a function named Sum in the following code, such as:
Private Function Sum (x As Double, y As Double) As Double Dim sumValue As Double SumValue = WorksheetFunction.Sum(x, y) End Function
Explanation
In this example, two arguments (of type double) and a return type (of type double) are used. The function name Sum is used to indicate which result you wish to return, such as (x + y).
Then, using the function’s name (Sum) and a value for each argument, you can call this function from elsewhere in your code.
To do this, you must insert a command button on your worksheet and include the code below and write the function in worksheet cells:
See More: VBA Date Format
User Defined Function: =VBAFuncation_Example_1(2,5)
Function VBAFuncation_Example_1(x As Double, y As Double) As Double Dim sumValue As Double sumValue = WorksheetFunction.Sum(x, y) VBAFuncation_Example_1 = sumValue End Function
Explanation
You can use another variable sumValue to store the result. The function returns a value, so you have to detect these values in your code. You can also change this variable by displaying its value in a worksheet cells.
After write the functions in Cells (A1) on the sheet, the following message will appear.
Example
A function is identical to a subroutine, but the only difference between the two is that a function returns a value when it is called, while a subroutine does not.
If you want to find the area, you must create a function that accepts the values that are possible to be returned by the function name itself.
Step 1: Add a Command Button to the worksheet.
1. Then set the Name property of the Command button and set the Caption property of the Command button. Now, the interface looks like the below screenshot.
Step 2: Open the code window and write the following code.
Private Sub cmdVBAFunction_Click() Dim sumValue As Double Dim x As Double, y As Double x = 2 y = 5 sumValue = WorksheetFunction.Sum(x, y) MsgBox sumValue End Sub
Step 3: Write the code that calls the function.
1. Right-click on the cmdVBAFunction_Click command button and select the View Code option.
2. Add the following code as shown above.
Step 4: Run the above code, and you will get the following results as shown in the below screenshot.
