Sub and Function Procedures

All the codes that we write in VBA are within the procedure so we can call all the programs as procedures .There are two types of procedure in VBA.

  • Sub Procedure
  • Function Procedure


Sub Procedure

A Sub procedure is a group of statements enclosed between Sub and End Sub statements. The Sub procedure performs a specific task and then returns control to the calling code, but it never returns a value to the calling code.


Function Procedure

A function procedure is a series of reusable code that performs a task and returns a single value or an array. Function procedures are very useful when the program size increases and we need to perform the same task again and again.

A Function procedure is a group of VBA statements


VBA function can be one of the following :-


  • Built in functions provided by VBA
  • Worksheet functions provided by EXCEL
  • Custom functions that you write ,using VBA


Example of using worksheet function of excel


Sub Price ()

Dim itemname as string

Dim price as double

Itemname= Inputbox (“enter the item name”)

Price =Worksheet function.Vlookup(itemname,Range(“Pricelist”),2,0)

Msgbox itemname & “cost is”& price

End sub


Now the above example is based on finding a price of any item  using EXCEL function vlookup so these are two columns in excel named itemname & price and this sample table range is named as pricelist.


Msgbox & Inputbox  are VBA Built in functions.


 Example of using user-defined function of excel

To tell the function what value to return, you need to assign that value to the name of function


Function Addnumber(Number)

Addnumber = number +number

       End function


Difference between Sub and Function Procedure


Sub Procedure

Function Procedure

A sub procedure is a group of VBA statements that perform an action with excel.          


A function procedure is a group of statements that perform calculations and return a single value or an array.


A sub procedure takes arguments from other procedures.


A function procedure takes one or more arguments (although few functions don’t use arguments) and does some calculation to return a single value.


When you record a macro with the excel macro recorder the result is always a sub procedure that takes no arguments.


You can’t use the excel macro recorder  to record a function procedure. You must manually enter every function procedure that you create.



© Snagoff. All right reserved.