Excel VBA function and Sub

Excel Function and Sub

Function and Sub are programming blocks used for the purpose of write once and use many times. They take inputs on the runtime and process the instructions.

The fundamental difference between function and sub is that function has the capability to return a value whereas a sub can only process the instruction but cannot return results.

Functions and subs are extremely useful to keep a program size in control.

Function

To understand the role and implementation of function in VBA, follow the code below:

Function fncArea(dblX As Double, dblY As Double) As Double

fncArea = dblX * dblY

End Function

Explanation: This function has two arguments of Double type and also a return Double type. You can use the name of the function (fncArea) in the code to indicate which results are desired.

You can now call this function from somewhere else in your code by simply using the name of the function and providing the required value for each argument.

Now insert a command button on worksheet and add the following lines of code:

Dim dblArea As Double
dblArea = fncArea(7, 11)

MsgBox dblArea

Explanation: The function returns a value and needs to ‘catch’ this value within the code. For this, you will observe the use of another variable called dblAre in this tutorial and finally displayed the output through Msgbox.

Sub

To understand the role and implementation of Sub in VBA, follow the code below:

Sub fncArea(dblX As Double, dblY As Double) 

Msgbox  dblX * dblY

End Sub

Explanation: This sub has two arguments of double type however, it doesn’t have a return type associated with it which means it cannot return any output result. However, it will execute any instruction that is provided within the Sub routine.

Insert a command button on worksheet and add the following line of code:

Area 7, 11

On clicking the command button, the msgbox code mentioned within the Sub routine will execute and show the result of the inputs.