Friday, November 30, 2012

What is the difference between Subroutine and Function in VBA

Excel VBA Sub Routine Vs Function
The Sub(routine) can NOT return a value. But a Function can return a value. If you want to call them then a Function can be called by it’s name with variables in parenthesis and  a Subroutine can be called only by writing it name with variables without parenthesis as explained below.
Subroutine (Sub) Syntax with Example:                                                              
Sub  Add(x as integer, y as integer, z as integer)
z= x+y
End Sub
'Calling Subroutine
Add  x, y, z
when it will return result will be saved in z variable, so it has not returned anything.

Function Syntax with Example:                                                                 
Function  Add(x as integer, y as integer) as integer
Add= x+y        
End Function
'Calling Function
T=Add( x, y)
when it will return result will be stored in ‘T’ variable. The function always returns a value so its value must be saved in some variable

Explanation:
Sub:

The easiest way to think of a Sub vs a Function is that a Function can return a value and a Sub cannot. A Sub can be thought of as a small program that performs some action that is contained within the program. 
Subs can be used to update a cell or perform an import and calculation, but the result can’t be returned to another sub or function. Another thing to note is that a sub (or macro) cannot be accessed directly by a cell reference. 
For example, when a cell is used to show today’s date, =Today(), that formula is also a built-in function, not a sub.

Function :

A Function is similar to a Sub, except that a Function can return a value. It may be easier to think of a function as similar to a formula in excel. You can provide the function the necessary inputs and the function returns the desired value. 
You can build a custom function for just about any action and then access that function from either a call from a macro, or a direct reference from a cell. 

Thanks, TAMATAM

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog