Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkdin
Share On Reddit
Contact us
Hide Buttons

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.

User Defined Functions-Module

User Defined Functions-Module

Write a Function

User Defined Functions-Function

User Defined Functions-Function

Use the function in Excel

Now you function is ready to be used as a formula in your excel

User Defined Functions- Use Function

User Defined Functions- Use Function

 

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 
User Defined Functions-Percentage

User Defined Functions-Percentage

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 

 

User Defined Functions-Grade

User Defined Functions-Grade

 

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 
User Defined Functions-Polynomial

User Defined Functions-Polynomial

Happy Macro­ing :)

Sumit Jain

You may also like...

1 Response

  1. zeze says:

    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!

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: