Sub Procedure In Vba Excel #06

In the discussion about modules and procedures, we mentioned that there are 3 types of procedures in VBA: Sub, Function and Property . In this VBA Excel guide series we will learn more about the types of Sub Procedures.

Understanding Sub Procedures in VBA Excel

Sub Procedure is a type of procedure in VBA . The term Sub in this type of VBA procedure refers to the term Subroutines which can generally be interpreted as a set of instructions designed to perform certain operations or actions on a computer program.

A sub procedure in Excel VBA is a collection of VBA code that is marked with an opening Sub statement and ends with an End Sub statement . The sub procedure will execute a certain action, action or task according to the program code provided between the Sub and End Sub statements, but does not return or produce a certain value ( return value / return result ).

Return Value or the final value is the main difference between the Sub Procedure Type and the Function Procedure in VBA Excel. Function Procedures can return or return a certain value while Sub Procedures cannot. About the Function Procedure will be discussed in the next series.

If a sub procedure is executed, the program code in it will be executed from the beginning until the End Sub/Exit Sub statement is found .

If you run the ” Record Macro ” command or menu on the Excel Developer Tab, Excel will automatically create a VBA Sub procedure.

How to Create Sub Procedures

The syntax or way of writing in making a sub procedure is more or less as follows:

[Private|Public|Friend] [Static] Sub Procedure_Name ([List_Argument])

[Instructions/Program Code]

[Exit Sub]

[Instructions/Program Code]

End Sub

Each Sub Procedure element that is in square brackets “[…]” is optional, meaning it can be used and can be ignored or not written. If you’ve ever seen a sub procedure in a VBA module, it’s possible to write it in a simple way like this:

SubName_Procedure()

'Instructions/Program Code

'Instructions/Program Code

'Etc.

End Sub

A simple example of a Sub Procedure is as follows:

SubcopyValue()

     ' Copy Value C2 to A1

     Sheet1.Range("A1") = Sheet1.Range("C2")

     'Copy Value D6 to A2

     Sheet1.Range("A2") = Sheet1.Range("D6")

End Sub

Information:

1. Sub : Indicates the type of Procedure
2. copyValue : Indicates the name of the procedure.
3. The line of text code ” Copy Value C2 to A1 ” and ” Copy Value D6 to A2 ” which begins with quotation marks (‘) is a description / comment and does not mean anything.
4. Sheet1.Range(“A1”) = Sheet1.Range(“C2”) is a line of code that contains a command to fill the value of Cell A1 on Sheet1 with the value of Cell C2 on Sheet1
5. Sheet1.Range(“A2”) = Sheet1.Range(“D6”) is a line of code as before which can also be interpreted as a command to copy the value of cell D6 to cell A2 on Sheet1.
6. End Sub : A line of code as a closing statement of the Sub Procedure which indicates the end of the copyValue procedure.

Next, we will try to explain each of the elements that make up a Sub Procedure in VBA Excel.

 

Element [Private|Public|Friend]

Private, Public and Friend keywords set the access level or scope of the related procedure.

• Private : Indicates that the sub procedure can only be accessed/invoked by procedures in the same module where the procedure was written
• Public : Indicates that the sub procedure can be accessed/called by any procedure in all modules.
• Friend : This keyword is used in the Class module. For now, let’s ignore it.

The three keywords which are also often referred to as access modifiers above are optional. If you don’t use one of them during the creation or declaration of a sub procedure, then VBA Excel will use the Default keyword, namely Public. This means that the procedure you create can be called from all existing modules.

We have talked about this Scope a lot in the previous series, for more details, an example will be given in the exercise section below.

Element [Static]

The Static keyword in the procedure declaration states that every local variable in a sub procedure will be stored or not removed from memory. If you don’t use this keyword, normally the variable will be emptied after a called procedure ends or finishes executing.

If you’ve read the previous guide on VBA Constants and Variables, of course, you’ve got an overview of Static Keywords above.

Element “Sub”

You must use this “Sub” text or element if you intend to create a Sub procedure. This element is the main difference in the preparation of sub procedures with other types.

Element “Procedure_Name”

This section shows the name of the procedure you created and you should use.

The procedure name is used as a marker to distinguish one procedure from another as well as to determine how the procedure will be called by other procedures.

Even though it doesn’t affect anything from a procedure, it’s best to use a name that represents the purpose of the entire procedure that you make, so that when you have many procedures compiled you don’t have trouble distinguishing the intent or purpose of each existing procedure.

As explained in the previous section, the name of a procedure follows the following rules:

1. The first character must be a letter of the alphabet (AZ, az).
2. Subsequent characters can be letters, numbers or certain punctuation characters (not all punctuation marks can be used).
3. You can’t use periods (.) and spaces ( ) and you can’t use the following characters: #, $,%, &, @, ^, * and !.
4. There is no difference between uppercase and lowercase letters.
5. The maximum number of characters that can be used is 255.

Element [List_Argument]

This section shows a list of variables that represent the arguments to be used in the Sub procedure when it is called. If you use more than one argument or variable, they are separated by commas.

Arguments in procedure declarations are written like variable declarations without including a scope. Writing these arguments can be accompanied by other statements such as ByVal or ByRef or Optional, while the variable declaration does not exist.

The scope of an argument is local to the procedure. That is, as if it were equivalent to the variable declared with Dim in the procedure. So the argument can result in a locally scoped variable within the procedure that can accept values ​​from outside the procedure.

We will discuss using List_Argument in the next series of VBA guides. For now, it can be ignored.

Element [Instructions/Program Code]

Instructions / Program Codes are lines of command code that will be carried out by the sub procedure that is executed or called. The contents of course adjust to your needs or desires, which in essence are what things will be carried out by the related procedure when it runs. For example, hiding certain lines, displaying notifications, saving files and so on.

Element [Exit Sub]

The line of code “Exit Sub” is a command to exit the related Sub Procedure.

“Exit Sub” indicates that the procedure must be terminated and not continue to the next line of the program. In other words, when the code sequence reaches “Exit sub”, the code sequence that is executed will immediately jump to the end of the procedure or “End Sub”.

“End Sub” Element

This section marks the end of a Sub Procedure .

When completing the opening statement to create a new procedure, usually the closing statement for this sub procedure will be automatically created in the VBE (Visual Basic Editor) .

Practice Making Excel VBA Sub Procedures

Learning VBA is not complete if there is no practice. Right?

In this section we will learn to understand some of the terms that have been explained above. At the same time we will also learn how to determine the scope of a VBA Sub Procedure.

Now please create a file with the extension xslm then save it with the name ” Excel Class-VBA Practice 4 ” in the folder we created in the first part of this Basic VBA tutorial series .

Public Sub Procedure

In the file that we created earlier, create a new module and then change the name of the new module from ” Module1 ” to ” SubProcedure1 “. How to? If you are still having trouble doing this activity, please read the previous series.

What’s next if you follow this Basic VBA guide series from the beginning, when you open the ” SubProcedure1 ” module automatically at the top of the Module Window the ” Option Explicit ” text / code will appear .

Under this code create 2 Sub Procedures, each with the name Exercise11 and Exercise12 as shown below:

Public Sub Exercise11()

     MsgBox "This is Exercise Procedure11"

End Sub

Sub Exercise12()

     MsgBox "This is Exercise Procedure12"

End Sub

The main difference between the two sub procedures above is the keyword “Public” in the “exercise11” procedure, while the “exercise12” procedure does not.

Now please try to run the two procedures earlier. If your steps are correct then when executed, each procedure will bring up a message box.

To run the procedure, position the cursor on one part of the procedure then press F5 or select the Run–Run Sub/Userform menu.

After successfully running both Exercise11 and Exercise12 procedures, then please make another 2 procedures below. Each with the name “Exercise13” and “Exercise14” as below.

Sub Exercise13()

     Call Exercise11

End Sub

Sub Exercise14()

     Call Exercise12

End Sub

If so, please run these two procedures alternately.

When executed, the “Practice13” procedure invokes the “Practice11” procedure and raises the following message box:

Whereas the “Practice14” procedure will call the “Practice12” procedure and display the following message:

• One way to call a procedure from another procedure is to use the Call keyword followed by the name of the procedure being called.
• Msg Box is one of the VBA commands that is used to bring up a message box that contains certain text.

Up to this stage, it turns out that using the Public keyword or not, does not affect the access of a procedure when it is called from the same module. Then what if it is called from a different module?

Now please create one more module then change its name to ” SubProcedure2 “. So now we have 2 modules. The first Module contains 4 procedures, while the second VBA Module is empty.

Next, in the 2nd module or the module with the name “SubProcedure2” earlier, create two Sub Procedures as follows:

Sub Exercise21()

     Call Exercise11

End Sub

Sub Exercise22()

    Call Exercise12

End Sub

The Exercise 21 sub procedure contains code that calls the Exercise 11 procedure, while the Exercise 22 sub procedure contains the code to call the Exercise 12 procedure.

If you have created it, run both the Exercise21 and Exercise22 procedures on the SubProcedure2 module in turn.

How is the result?

Yes, it turns out that the two Sub Procedures of Exercise11 and Exercise12 can also be called from other different modules.

Next, write the following code script again to add a sub procedure to the following SubProcedure2 module :

Sub Exercise23()

     Exercise11

     Exercise12

End Sub

If you have finished making this Exercise23 Sub Procedure, please try to run it.

Now in the SubProcedure1 module there are 4 VBA procedures named Exercise11, Exercise12, Exercise13 and Exercise14 . Meanwhile, in the SubProcedure2 module, there are 3 procedures each with the names Exercise21, Exercise22, and Exercise23 .

From the above exercises, the following conclusions can be drawn:

1. The Public keyword in the declaration statement of a Sub Procedure makes the procedure have a Project-Level scope, meaning that it can be accessed/called from other modules, even in the right way, it can also be accessed from VBA Projects or other excel files.
2. If a procedure is declared without including the public or private keywords, it will be treated as using the public keyword as the default access modifier.
3. A sub procedure can be called from another procedure by using the keyword Call followed by the name of the procedure or directly invoked by writing the name of the procedure.

How? already tired? If not, we will continue to practice and discuss this Excel VBA Sub Procedure.

Private Sub Procedure

After understanding the use of the keyword “Public” now we will practice trying to use the keyword “Private” .

In the VBA module ” SubProcedure1 ” add the following code to create 2 other Sub Procedures:

Private Sub Exercise15()

     MsgBox "This is Exercise Procedure15"

End Sub

Sub Exercise16()

    Exercise15

End Sub

The Exercise15 procedure contains code to display a message, while the procedure with the name Exercise16 contains a command to call or run the “Practice15” procedure.

I hope the way I named the procedures above doesn’t confuse you. If so, I’m sure you just read it without practicing writing the codes above. hehehe.

Alternately when these two procedures are run it should bring up a message box like this:

Now we move to the module ” SubProcedure2 ” and add the following Sub procedure under the existing “Practice23” procedure:

Sub Exercise24()

    Exercise15

End Sub

When the Exercise24 procedure is executed to call the Exercise15 procedure, the following error message appears:

Yes, this is because our Exercise15 procedure is declared as Private, so it cannot be called from another module.

From this exercise, it can be concluded that a VBA procedure declared as Private has a module level scope so that it can only be called by other procedures in the same module and cannot be called from other modules.

Static Sub Procedure

In the previous series it was mentioned that the procedure level static variable retains its value when a procedure is finished (Not deleted/reset).

So the keyword “Static” affects the lifetime of the local variable. This section will not be explained further because we have already discussed in a series that discusses VBA constants and variables .

For practice now please create a new module again and change its name to ” SubProcedure3 “

In this module write the following two VBA scripts:

Sub Exercise31()

     Dim int_Variable1 As Integer

     int_Variable1 = int_Variable1 + 5

     MsgBox "Variable value int_Var1 is: " & int_Variable1

End Sub

Static Sub Exercise32()

     Dim int_Variable2 As Integer

     int_Variable2 = int_Variable2 + 5

     MsgBox "Variable value int_Var1 is: " & int_Variable2

End Sub

Next please run these two procedures each 3x or more. Then notice the difference.

You can make all VBA Sub procedures in a particular module only accessible from the same VBA project (not from other VBA projects/files) by adding the ” Option Private Module ” statement without quotes at the beginning of the module.

So first our discussion for the VBA Sub Procedure, in the next series we will learn in more detail about how to run the Sub procedure and discuss the Function Procedure in VBA Excel.

If you have any questions, please submit them in the comments column provided and don’t forget to share this page so that more friends will benefit from it.