Procedures in VBA
2019-12-26 05:20:59

Procedures in VBA

Procedures in VBA

A procedure is a block of statements or units of computer code that performs some action. It is enclosed with a declaration statement, and its primary purpose is to carry out a particular task or action. VBA supports two types of procedures: Sub procedures and Function procedures.

Sub Procedure

The Sub procedure consists of a series of statements and can be executed in several ways. Here’s an example of a simple Sub procedure called Test: This procedure calculates a simple sum and then displays the result in a message box.

VBA Sub procedure names must follow specific rules. The Four elements which are required to declare VBA Sub procedure are as follows:

  1. Sub Statement: The Sub keyword is used to declare the beginning of the VBA Sub procedure. 
  2. Name: It represents the name of the VBA Sub procedure. The first character must be a letter rest can be letters, numbers, or certain punctuation characters (excluding #, $, %, &, @, ^, *, ! and spaces). The maximum number of characters a name can have is 255.
  3. Parentheses: Parentheses are used to declare the arguments from other procedures where the arguments must be separated by comma (,). Although it’s optional i.e., you can even declare a VBA without any arguments just by putting empty parenthesis {}.
  4. Statements: Sub procedure has a block of statements that is enclosed by the declaration and End declaration statements
  5. End Sub Keyword: An End Sub keyword is used to exit the VBA Sub procedure in which the statements are included.

Program:

Sub Example()
Mul = 1 * 1
MsgBox “ The multiplicative answer is” & Mul
End Sub
Procedures in VBA Excel

Function procedures

A Function procedure returns a single value (or possibly an array). A Function can be called from another VBA procedure or used in a worksheet formula. A VBA function returns a value by allocating a value to its name in one or more statements of the procedure. A Function procedure is similar to a Sub procedure, with only one difference that a function can also return a value.

VBA Function procedure names must follow specific rules. The Four elements which are required to declare VBA Function procedure are as follows:

  1. Function Statement: The Function keyword is used to declare the beginning of the VBA Function procedure. 
  2. Name: It represents the name of the VBA Function procedure. The first character must be a letter rest can be letters, numbers, or certain punctuation characters (excluding #, $, %, &, @, ^, *, ! and spaces). The maximum number of characters a name can have is 255.
  3. Parentheses: Parentheses are used to declare the arguments from other procedures where the arguments must be separated by comma (,). Although it’s optional i.e., you can even declare a VBA Function without any arguments just by putting empty parenthesis {}.
  4. Statements: Function procedure has a block of statements that is enclosed by the declaration and End declaration statements
  5. End Function Keyword: An End Function keyword is used to exit the VBA Function procedure in which the statements are included.

Program:

Function MulTwo (arg1, arg2) 
MulTwo= arg1 * arg2
End Function
Function procedures