An Interactive Learning Tool

Proud to be a Teacher


Subroutines and Functions

Subroutines

A Subroutine in Excel VBA is a procedure that performs a specific task and to return values, but it does not return a value associated with its name. However, it can return a value through a variable name. Subroutines are usually used to accept input from the user, display information, print information, manipulate properties or perform some other tasks. It is a program code by itself and it is not an event procedure because it is not associated with a runtime procedure or an Excel VBA control such as a command button. It is called by the main program whenever it is required to perform a certain task. Sub procedures help to make programs smaller and easier to manage. A Subroutine begins with a Sub statement and ends with an End Sub statement.
Sub SubRoutine(arguments)
    Statements
End Sub
Subroutines are called by the main program using the Call keyword.
Sub PrimaryProg( )
    Call SubRoutine()
End Sub
A subroutine is not a function because it does not return a value directly. You can include parameters in a subroutine.

Functions

In Excel VBA, a function is similar to a subroutine but the main purpose of the function is to accept a certain input from the user and return a value which is passed on to the main program to finish the execution. There are two types of functions, the built-in functions (or internal functions) and the functions created by the programmers, or simply called user-defined functions.
Functions can take inputs from different ways i.e Spreadsheet data and InputBox entries.
A function can be created for some complex calculations or create a functions that is not available as in-built function.
Example:

Simple Temperature converter

We are using this function to convert Centigrade to Fahrenheit and formula is C*9/5+32, here we will write this function as see result in spreadsheet

Let see it in Spreadsheet
We are taking value 38 as Centigrade to convert

And result will be appeared in cell as regular in-built functions.


And a revert function to convert Fahrenheit to Centigrade
To convert Fahrenheit to Centigrade we will use Function 'Fern2Cent' that we have created.

Here is result in the spreadsheet.

You can create any of the calculations that you want to create for your purpose, keep it in mind that Excel calculate formulas using BODMAS method.
You can use another functions using long statements including comparison and logical method.

Value Conditional Test

We are testing if value is greater than 100 or not and will set max value to 100

Below is the spreadsheet function, values are put in cell F2 and results are shown in F3.

In the above test if value will be lesser than or equal to 100, that value will be shown in F3, but when value of F2 will crossed 100 value of Cell F3 will not change.


Terms of use Website Policy Contact us
© Copyrights ExcelYourVBA.com 2020