# 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)

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

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

### 1 thought on “VBA-Excel: User Define Functions”

1. Hola! I’ve been following your web site for a while now and finally
got the courage to go ahead and give you a shout out
from Huffman Texas! Just wanted to mention keep up the excellent job!