Using macro as function IT402 explains how to use macro as a function.
Introduction to using macro as function IT402
AS you are aware that MS excel provides numerous functions and formulas. At the other side there are certain tasks which cannot be performed by a function.
In this scenario user need to create a function to do such tasks using macro as a function. In this article we are going to discuss how to create a function in MS Excel using macro. Let’s we begin!!!
Before string this read this:
using macro as function IT402 Creating a function
To create a function follow these steps:
Step 1: Click on Developer –> Visual Basic option from code group.
Visual Basic Code windows will appear. Insert a module through the insert option. And click on Insert –> Module option.
Step 2: Write following code in module:
Function Multiply(a As Integer, b As Integer) As Integer Multiply = a * b End Function
In above function, use the same name inside line as and pass the values in first line. The words Keywords – Function, Integer, End Function converts the first letter into capital by itself.
a and b are two values can be taken from excel worksheet.
Step 3: Now return to MS Excel screen and just type =, you will get Multiply function there as a formula. Observe the following screenshot:
Step 4: Select the Multiply function and now use it as traditional function or formula.
Passing Arguments and Values to a macro
In the above function variable, a and variable b is written inside a function in line 1 code that is considered as arguments.
The values of these arguments will come from excel worksheet when you write the function in the MS Excel cell.
Select the cells which reference you want to take to compute the data using function.
Accessing cells directly
To access cells directly refers to accessing cells in VB code using developer tab.
Visual basic provides range() function to access cells directly. The value can be written in following manner.range(celladress).value
Watch this video for understanding:
So I hope you understand this concept. You can access the entire module of IT 402 from this link:
Thank you for reading this article. Share this article with your friends and enjoy learning. Create your own functions for customization and use them. Comment your views about this article in comment section.
If you have doubt regarding this kindly comment.