Procedures in Excel VBA

Home/Excel VBA/Procedures in Excel VBA

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

PREMIUM TEMPLATES LIMITED TIME OFFER

ON SALE80% OFF

BROWSE ALL TEMPLATES

50+ Project Management Templates Pack
Excel PowerPoint Word

VIEW DETAILS

Advanced Project Plan & Portfolio Template
Excel Template

VIEW DETAILS

Business Presentations Templates Pack
PowerPoint Slides

VIEW DETAILS

20+ Excel Project Management Pack
Excel Templates

VIEW DETAILS

20+ PowerPoint Project Management Pack
PowerPoint Templates

VIEW DETAILS

10+ MS Word Project Management Pack
Word Templates

VIEW DETAILS


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

LIMITED TIME OFFER
By |August 4th, 2013|Excel VBA|2 Comments

Share This Story, Choose Your Platform!

About the Author:

PNRao is a passionate business analyst and having close to 10 years of experience in Data Mining, Data Analysis and Application Development. This blog is his passion to learn new skills and share his knowledge to make you expertise in Data Analysis (Excel, VBA, SQL, SAS, Statistical Methods, Market Research Methodologies and Data Analysis Techniques).

2 Comments

  1. Ivan Anděl April 8, 2015 at 10:36 PM - Reply

    Hi, I have Windows 7 x64 and Excel x64. Can I get Analysistabs.xlam Add-In for my configuration? The available version does not work. Thanks IA.

    • PNRao April 9, 2015 at 8:20 PM - Reply

      Hi Ivan,
      I am working on it to support multiple platform, will be available soon with loaded functionality.

      Thanks-PNRao!

Leave A Comment