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.

Syntax:

Public Function functionName (Arg As dataType,……….) As dataType

or

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:

1. Open Visual Basic Editor(link to Visual Basic Editor)
2. Clike on the “Module” under “Insert” menu.
3. Write the Function
4. 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

Code:

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 

Happy Macro­ing

Sumit Jain