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 for­mu­las like Sin(), Avg() and many more, which we dis­cuss in detail in future arti­cles, but apart from that you can write your own func­tions which will act as any other built-in for­mula in excel , Which is one of the pow­er­ful  fea­ture of Excel.

Like other for­mu­las, you can apply your func­tion in each cell or range of cells.

Syntax:

Pub­lic Func­tion func­tion­Name (Arg As dataType,……….) As dataType

or

Pri­vate Func­tion func­tion­Name (Arg As dataType,……….) As dataType

Arg As dataType -> data type of argument

The sec­ond data type is the return type of function.

Pub­lic : The func­tion is applic­a­ble to the whole project.

Pri­vate: The func­tion is only applic­a­ble to a cer­tain mod­ule or procedure.

Steps to follow:
  1. Open Visual Basic Editor(link to Visual Basic Editor)
  2. Clike on the “Mod­ule” under “Insert” menu.
  3. Write the Function
  4. Use the func­tion in Excel

Open Visual Basic Editor(link to Visual Basic Editor)

Open Excel and press “Alt+F11” , For detail read ( Link)

Click on the “Mod­ule” under “Insert” menu.

User Defined Functions-Module

User Defined Functions-Module

Write a Function

User Defined Functions-Function

User Defined Functions-Function

Use the func­tion in Excel

Now you func­tion is ready to be used as a for­mula in your excel

User Defined Functions- Use Function

User Defined Func­tions– Use Function

 

Exam­ple 1: Cal­cu­late Percentage

Cre­ate a Func­tion called Per­cent­age 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

Exam­ple 2: Cal­cu­late Grade Based on Percentage

Cre­ate a Func­tion 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 : Calu­late poly­no­mial (Ax^2+Bx+C)

Cre­ate a Func­tion called poly­no­mial under mod­ule

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 fol­low­ing your web site for a while now and finally
    got the courage to go ahead and give you a shout out
    from Huff­man Texas! Just wanted to men­tion keep up the excel­lent job!

Leave a Reply

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

%d bloggers like this: