You are reading the article Excel Vba Function Tutorial: Return, Call, Examples updated in September 2023 on the website Benhvienthammyvienaau.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Excel Vba Function Tutorial: Return, Call, Examples
What is a Function?A function is a piece of code that performs a specific task and returns a result. Functions are mostly used to carry out repetitive tasks such as formatting data for output, performing calculations, etc.
Suppose you are developing a program that calculates interest on a loan. You can create a function that accepts the loan amount and the payback period. The function can then use the loan amount and payback period to calculate the interest and return the value.
Why use functions
Rules of naming functions
The rules for naming functions as the same as the ones in the above section on rules for naming subroutines.
VBA Syntax for declaring Function Private Function myFunction (ByVal arg1 As Integer, ByVal arg2 As Integer) myFunction = arg1 + arg2 End FunctionHERE in the syntax,
Code Action
“Private Function myFunction(…)”
Here the keyword “Function” is used to declare a function named “myFunction” and start the body of the function.
The keyword ‘Private’ is used to specify the scope of the function
“ByVal arg1 As Integer, ByVal arg2 As Integer”
It declares two parameters of integer data type named ‘arg1’ and ‘arg2.’
myFunction = arg1 + arg2
evaluates the expression arg1 + arg2 and assigns the result to the name of the function.
“End Function”
“End Sub” is used to end the body of the function
Function demonstrated with Example:
Functions are very similar to the subroutine. The major difference between a subroutine and a function is that the function returns a value when it is called. While a subroutine does not return a value, when it is called. Let’s say you want to add two numbers. You can create a function that accepts two numbers and returns the sum of the numbers.
Create the user interface
Add the function
Write code for the command button
Test the code
Step 1) User interface
Add a command button to the worksheet as shown below
Set the following properties of CommanButton1 to the following.
S/N Control Property Value
1 CommandButton1 Name btnAddNumbers
2
Caption Add Numbers Function
Your interface should now appear as follows
Step 2) Function code.
Press Alt + F11 to open the code window
Add the following code
Private Function addNumbers(ByVal firstNumber As Integer, ByVal secondNumber As Integer) addNumbers = firstNumber + secondNumber End FunctionHERE in the code,
Code Action
“Private Function addNumbers(…)”
It declares a private function “addNumbers” that accepts two integer parameters.
“ByVal firstNumber As Integer, ByVal secondNumber As Integer”
It declares two parameter variables firstNumber and secondNumber
“addNumbers = firstNumber + secondNumber”
It adds the firstNumber and secondNumber values and assigns the sum to addNumbers.
Step 3) Write Code that calls the function
Select View Code
Add the following code
MsgBox addNumbers(2, 3) End Sub
HERE in the code,
Code Action
“MsgBox addNumbers(2,3)”
It calls the function addNumbers and passes in 2 and 3 as the parameters. The function returns the sum of the two numbers five (5)
Step 4) Run the program, you will get the following results
Download Excel containing above code
Download the above Excel Code
Summary:
A function is a piece of code that performs a specific task. A function returns a value after execution.
Both subroutines and functions offer code reusability
Both subroutines and functions help break down large chunks of code into small manageable code.
You're reading Excel Vba Function Tutorial: Return, Call, Examples
Update the detailed information about Excel Vba Function Tutorial: Return, Call, Examples on the Benhvienthammyvienaau.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!