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

50+ Project Management Templates Pack
Excel PowerPoint Word

Advanced Project Plan & Portfolio Template
Excel Template

Business Presentations Templates Pack
PowerPoint Slides

20+ Excel Project Management Pack
Excel Templates

20+ PowerPoint Project Management Pack
PowerPoint Templates

10+ MS Word Project Management Pack
Word Templates


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 mongopono.ru.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


Related pages


kpi dashboard examples excelvba excel case statementvba access sqlhow to use the sumif function in microsoft excel 2010do while loop excelspreadsheet control vbavba listbox additemms excel programming tutorialactivex command buttonexcel macro to merge cellsvlook up examplevba vlookup functionvba examples excelexcel basic macrosmerge multiple cells in excelms access vba run query with parametersif cell is blank excelvba vlookup codeexcel formula data validationvba range commandvba guideexcel 2007 macro securityworksheet unprotectcheck duplicate excelexcel formula list 2007how to combine data from multiple worksheets in excel 2010delete selection vbavba excel sort columnmatch type excelexcel isnumberexcel input form templateadd drop down list in excel 2007excel vba cell fill colorvba excel tableremove duplicate values in excel 2010shortcuts for excel formulasexcel newline in formulahow to activate activex controlidentifying duplicates in excelexcel vba insert rowhow to make pivot tables in excel 2007excel basic macrosexplain macros in excelexcel autofit columnsexcel vba formattingexcel macro applicationunprotect sheet excel 2013how to filter duplicate rows in excelexcel formula sortexcel formulas vlookupvba for to steplearn advanced sql programminghow to lock a tab in excelunprotect worksheetsample interview questions and answers for freshersvba enumerationunderstanding vbavba while loopexcel auto sortduplicate excel worksheetms excel formulas with examples for 2007fancy graphs in excelexcel formulas lookupexcel dashboard widgetsunhide columns in excel 2013excel sheet passwordduplicate sheet excelvba excel copy worksheet to another workbookcheckbox en excelexcel estimating templatesusing hyperlinks in excelexcel vba range copy