EDUCBA

Home VBA VBA Resources VBA Coding VBA SUB
 

VBA SUB

Pradeep S
Article byPradeep S
Madhuri Thakur
Reviewed byMadhuri Thakur

Excel VBA Sub

VBA Sub Function

It is the most essential & significant component of VBA. A Sub routine procedure is a series of Visual Basic statements enclosed by the Sub and End Sub statements. SUB means Subroutine procedure, it’s a series of VBScript statements, where it does not return a result or value.

 

 

Sub procedure usually take arguments or code (i.e. variables, constants or expressions that are passed by a calling procedure) which are carried out to perform a specific task. Sub statement contains empty set of parentheses () without any argument in it.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Write Sub Procedures in Excel VBA?

Let’s check out,

Basic Structure of VBA Sub

Sub [Sub Procedure or task name] ()

[What task needs to be done?]

End Sub

Usually, Subroutine begins with a Sub statement and ends with an End Sub statement. Sub Procedure or task name is also called a Macro name, where it should not contain any spaces and of a unique name. Sub accepts input or code from the user & displays or prints information. When the statement is finished, or at the end of the statement, End Sub is used

Note:  Sub can only take arguments, they don’t return results

Different Types of Subroutine in VBA (ACCESS MODIFIERS)

  1. Public Sub Procedure
  2. Private Sub Procedure

Prior to knowing the difference between them, you should be aware of “Access level” it’s an extent of ability to access it. Public Sub is similar to Sub, where Procedure allows you to use the procedure or VBA code in all the modules of the workbook. whereas Private Sub Procedure allows you to use the procedure or VBA code only in the current module. (Explained in the example)

How to Use Sub Function in Excel VBA?

Below are the different examples to use Sub Function in Excel using VBA code.

You can download this VBA Sub Excel Template here – VBA Sub Excel Template

VBA Sub Function – Example #1

Step 1: In the Developer tab, click on Visual Basic in the Code group or you can use the shortcut key Alt + F11

VBA Sub Example 1-1

Step 2: Now, you can create a blank module i.e. right-click on Sheet1(VBA_SUB), various options appear, in that select Insert and under the insert, three options appear, where you need to select Module, the blank module gets created. You can rename it as “VBA_SUB” under the properties section window

VBA module Example 1-2

Step 3: Start with subprocedure as follows and create code of VBA_SUB

Code:

Sub VBA_SUB()

End Sub

VBA SUB Example 1.3

Step 4: Once you start typing range, its argument appears in the parenthesis and click on the tab key to select it. Once you leave a space and enter open bracket “(”, CELLS argument will appear where you need to enter the syntax or argument for the range function i.e. “E5”

Suppose, I want SUBROUTINE to appear in the cell “E5” For this to happen, I need to type = “SUBROUTINE” after range function. and then click enter.

Now, the code is ready,

Code:

Sub VBA_SUB()

Range("E5") = "SUB ROUTINE"

End Sub

Example 1.4

Step 5:  Now, you can run the macro by clicking the Run Sub button (i.e. green “play” button) or by pressing F5. You can observe “SUBROUTINE” appears in the cell “B4”

VBA Sub 1

VBA Sub Function – Example #2

Step 1: Start with the Sub and now we will see how we can call the function by using the MsgBox.

Code:

Private Sub Display_Message()

MsgBox "my first programme"

End Sub

Example 2.1

Step 2: Suppose, when you run the above-mentioned code in VBA_SUB module it will work out, but when you try to copy & run it to another module, i.e. run this code in newly created module 1 of sheet 2 or 3, a popup message will not appear.

VBA Sub 2.2

VBA Sub Function – Example #3

Step 1: Public Sub Procedures can be accessed from a different module, let’s check out how it works.

Code:

Public Sub task_1()

Range("H7") = "PUBLIC SUBROUTINE"

End Sub

Example 3.1

Step 2: When I run the above-mentioned code (Public sub task_1) in VBA_SUB module, it returns the value or result i.e. “PUBLIC SUBROUTINE” text string in the cell “H7” of sheet1

VBA Sub 3.2

Suppose, I want to run this same code in newly created module i.e. PUBLIC_SUB, then it is not required to write the entire code in that module, just you mention the macro name i.e. call task_1, instead of range function in the second line of code, you will get the same output or result i.e. “PUBLIC SUBROUTINE” text string in the cell “H7” of sheet2.

Code:

Public Sub task_2()

Call task_1

End Sub

Example 3.3

Save your workbook as “Excel macro-enabled workbook”. When you open this excel file again, you can use below-mentioned shortcut key i.e.

  • Function + Alt + F11 short cut key helps you out to access all the created macro code of the workbook
  • Function + Alt + F8 short cut key helps you out to open a “Macro” dialog box window, which contains all the macro name, where you can run a specific macro code of your choice

Things to Remember

  • If no keyword like PUBLIC or PRIVATE is mentioned or inserted at the start of a VBA Function, i.e. Sub declaration, then, it will consider the default setting “Public” Sub.
  •  Intellisense menu is the drop-down menu that appears even when you type a period “.” in the VB code or Editor window) dropdown contains a list of all the members of the VB OBJECT MODEL active reference (i.e. includes objects, properties, variables, methods, and constants). This feature helps out in saving time & prevent misspell the words or typo-error.
  • Apart from End Sub, there is an option of Exit Sub statement between SUB and END SUB which causes or helps you out in an immediate exit from a Sub procedure.

Recommended Articles

This is a guide to VBA SUB. Here we discuss how to use Excel VBA SUB along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Active Cell
  2. VBA CDEC
  3. VBA Transpose
  4. VBA 1004 Error