Procedures in Excel VBA

Procedures in Excel VBA help us to write the sub – procedures to automate the tasks.

In this Section:

• What is a Sub Procedure?
• Calling a Sub Procedure
• Procedures and Access Levels
• Examples on Sub Procedures – With and With Out Parameters
• Example File

Procedures in Excel VBA – What is a Sub Procedure?

A sub procedure in VBA is a procedure that performs a specific task.Sub procedures are generally used to accept input from the user and do required action and then print the results.
generally sub procedures are called by using command button or by another sub procedure. Sub procedures are helpful to split a program into smaller piece of procedures.
We can write the Sub Procedures in any modules, Such as Code Module, Class Module,Forms and ActiveX Controls.

A Sub procedure begins with a Sub statement and ends with an End Sub statement. Following is the programming structure of sub procedure:

Sub ProcedureName (Arguments)

    '---------------
    'Statements
    '---------------	

End Sub

Below is the simple sub procedure to accept values from the user and find its square value:

Sub FindSquare()

   Dim i As Integer
   i=InputBox ("Please Enter a value to calculate square value:")
   MsgBox "Square Value of a given number is: " & i*i
   
End Sub

Calling a Sub Procedure

We can write and call Sub Procedures from another Sub Procedure, ActiveX Controls, or we can assign it to a shape or form button in a worksheet. We can also assign a shortcut key to a procedure and call it.

Calling a procedure using ActiveX Control – Command Button.

When You Double Click on the Command Button, it will open the code window with its event handler as shown below.

Private Sub CommandButton1_Click()

End Sub

You can call the procedure using ‘Call’ Keyword as shown below.

Private Sub CommandButton1_Click()
    Call FindSquare
End Sub

Sub FindSquare()

   Dim i As Integer
   i=InputBox ("Please Enter a value to calculate square value:")
   MsgBox "Square Value of a given number is: " & i*i
   
End Sub

Calling a Procedure in another Procedure: The following example shows how to call a sub procedure(FindSquare) from another Procedure(MainProcedure).

Sub MainProcedure()
    Call FindSquare
End Sub

Sub FindSquare()

   Dim i As Integer
   i=InputBox ("Please Enter a value to calculate square value:")
   MsgBox "Square Value of a given number is: " & i*i
   
End Sub

Assigning a Procedure to a Worksheet button or a Shape:Follow the below Steps to assign a procedure to a shape or button.

Step 1: Place a button or a Shape in the Worksheet.
Step 2: Right Click on a Shape or button, Which you have just placed in the Worksheet.
Step 3: Select Assign Macro, It will Open the Macro Dialog Box.
Step 4: Select a Macro from the list of Macros which you want to run by clicking the shape or button.

Calling the Procedure by Assigning a Shortcut key to a procedure. Please refer the following tutorial.
Executing a Macro

Procedures and Access Levels

We can control the accesbility of a procedure by making it ‘Private’ or ‘Public’. By default all Procedures are public, If you want to chang, It should be preceded with the keyword ‘Private’.

Private: If you make a Procedure as a Private, it can be accessable by other procedures of the same module. We cannot access Private Procedures in other modules. We can’t see the Private Prtocedures in the Macro Dialog Box.

Private Sub ProcedureName(Arguments)

    'Statements

End Sub

Public: If you make a Procedure as a Public, it can be accessable by other procedures of the same module as well as other Modules.We can see the Public Procedures in the Macro Dialog Box.

[Public] Sub ProcedureName(Arguments)

    'Statements

End Sub

Examples on Sub Procedures – With and With Out Parameters

Example 1: A Simple Procedure to Find a Square Value of an integer

Sub FindSquare()

   Dim i As Integer
   i=10   
   MsgBox "Square Value of a given number is: " & i*i
   
End Sub

Example 2: A Simple Procedure to Find a Square Value of an integer with Parameters.

Sub FindSquare(ByVal i As Integer)

   Dim i As Integer   
   MsgBox "Square Value of a given number is: " & i*i
   
End Sub

Calling the above Procedure in another Procedure.

Sub MainProcedure()

   Call FindSquare(10)  'It will Call the FindSquare procedure to find the Square value of 10.
   Call FindSquare(220)  'It will Call the FindSquare procedure to find the Square value of 220.

End Sub

The Procedures with the parameters or very useful when you are doing the same task again and again.In the above example we are finding the square values for 10 & 220 by calling same procedure.

Example File

Download the file below and have a look into the example procedures.

ANALYSIS-TABS-Sub-Prcedures