VBA-Excel: User Define Functions
Microsoft Excel has lot of built in formulas like Sin(), Avg() and many more, which we discuss in detail in future articles, but apart from that you can write your own functions which will act as any other built-in formula in excel , Which is one of the powerful feature of Excel.
Like other formulas, you can apply your function in each cell or range of cells.
Public Function functionName (Arg As dataType,……….) As dataType
Private Function functionName (Arg As dataType,……….) As dataType
Arg As dataType -> data type of argument
The second data type is the return type of function.
Public : The function is applicable to the whole project.
Private: The function is only applicable to a certain module or procedure.
Steps to follow:
- Open Visual Basic Editor(link to Visual Basic Editor)
- Clike on the “Module” under “Insert” menu.
- Write the Function
- Use the function in Excel
Open Visual Basic Editor(link to Visual Basic Editor)
Open Excel and press “Alt+F11” , For detail read ( Link)
Click on the “Module” under “Insert” menu.
Write a Function
Use the function in Excel
Now you function is ready to be used as a formula in your excel
Example 1: Calculate Percentage
Create a Function called Percentage under module
Private Function Percentage(num As Variant, Total As Variant) As Variant Percentage = (num / Total) * 100 End Function
Example 2: Calculate Grade Based on Percentage
Create a Function called Grade under module
Public Function Grade(pNum As Variant) As String Dim num Dim Result num = CInt(pNum) Select Case num Case Is >= 90 Result = "S" Case Is >= 80 Result = "A" Case Is >= 70 Result = "B" Case Is >= 60 Result = "C" Case Is >= 50 Result = "D" Case Is < 50 Result = "F" Case Else: Result = "NA" End Select Grade = Result End Function
Exmaple 3 : Calulate polynomial (Ax^2+Bx+C)
Create a Function called polynomial under module
Public Function Polynomial(a As Variant, b As Variant, c As Variant) Dim base base = 10 Polynomial = a * base * base + b * base + c End Function