VBA MODULE 6
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.
|