PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

USE SUB PROCEDURES USING VBA

INTRODUCTION

We have created our first program HELLO WORLD using the sub procedures and created a function also.

In this article, we’ll try to create some more sub procedures to solve our excel problems.

As we know sub procedures are simply a series of VBA statements which are executed one by one.

Procedures are put in the modules. We can put procedure of any length in a module but its better if we can make small meaningful portions, that is better to understand.

We can also pass any argument into the sub procedure but as its not a function it can’t return any value.

The procedures are executed from first to last statement but can be ended.

In this article, we’ll inspect each and every part of a sub procedure in detail.

STRUCTURE OF A SUB PROCEDURE

BASIC STRUCTURE

Although we have seen a sub procedure in the HELLO WORLD PAGE, but let us again revisit it in deep.

Here is the standard format of a sub procedure.

Private/Public Static Subprocedure name (argument list)
All the instructions to be executed.
Optional exit
more instructions.
End Sub

EXPLANATION

PRIVATE: PROCEDURE can be accessed only from the PROCEDURES IN THE SAME MODULE.

PUBLIC: PROCEDURE can be accessed from any procedure from any module of the workbook.

STATIC: The variables are preserved even after the execution ends.

ALL THESE THREE ELEMENTS ARE OPTIONAL. EVEN IF WE DON’T USE THEM, NO SPECIFIC PROBLEM WOULD OCCUR FOR SIMPLE PROCEDURES.

Sub: ESSENTIAL ELEMENT. It indicates the beginning of a procedure.

Name of Sub procedure: Required. Any name can be given but its advisable to give a name that suits the procedure.

ARGUMENT LIST: List of arguments to be passed into the procedure. The arguments are put in the “()” parenthesis. If more than one argument put a comma between them.

EXIT SUB: If at any condition we want an immediate exit.

END SUB: Declares end of the procedure.

SCOPE OF A PROCEDURE

BY DEFAULT

“EVERY PROCEDURE IS PUBLIC” i.e. they can be called from any other procedure in any module within the same workbook.

For the clarification , we may use this word.

Private Procedures are limited to the same module only.

RUNNING A SUB PROCEDURE

STEPS TO RUN A SUB PROCEDURE

The sub procedure can be run or executed in various ways . Let us have a brief look at all of them.

Go to RUN MENU of the VBE and click RUN sub procedure..
Press F5 in the VBE. It’ll directly open you up in the MACRO dialog box. Select the Macro and run.
Through MACRO button from Excel. It’ll again open MACRO dialog box. Choose and run.
MACRO can be assigned a hot key combination also. So a macro can be directly run through that also.
From the button present in the toolbar.
By creating a userform and giving control to any button. i.e. by clicking a button.
Customize ribbon to have a button for your macro.

A few more are there which are advanced and we’ll learn them when we reach at that level.

We can also link any event like opening of opening of workbook, changing any cell value etc. to run the procedure.
From the immediate window by typing the name of the sub procedure.

Many of the options you have already seen but we’ll see an animation to have a look again.but let us know a few new ones first.

ERROR HANDLING IN SUB PROCEDURES

Errors can always occur in the code or the errors when we are running the code. So it means that errors are of two types

1. Syntax Errors

2. Runtime Errors

SYNTAX ERRORS REASONS

SYNTAX ERRORS:

Syntax is the style or a fixed format in which the statements must be written in the code so that the compiler(which converts the code into machine language) understands and executes the program as per our expectations. So syntax errors are the ones which occur due to mistake while coding a program. These errors can include

Spelling mistakes
Capital and Small letters
Wrong passage of arguments
Number of parenthesis
Missing some expected character etc.

CORRECTING SYNTAX ERRORS

Syntax error would emerge only at the compiling time before the program is running.

The VBE would show a slight hint about the type of error.

We need to find out the error and correct it, only then we would be able to compile and run the code.

Now after we have corrected the SYNTAX ERRORS let us check out the errors which can emerge while the program is running.

RUNTIME ERRORS are the errors which occur when our program is running. These errors occur only when running and can’t be expected before it starts running.

RUNTIME ERRORS REASONS

Runtime errors can occur because of many reasons.

The reasons maybe connected with the operating system, or the application may encounter any situation which is not defined for. These can only be checked when the error occurs but good thing is that we have got ways to tackle these errors. Let us see how we can trap these errors.

TRAPPING RUNTIME ERRORS

Runtime errors can be trapped mainly in two ways

1. Ignoring the error.

2. Specific code to tackle the error conditions.

IGNORING THE ERROR

USE THE FOLLOWING STATEMENT

ONERROR RESUME NEXT

Example

Sub name()
On Error Resume Next
statements
On Error GoTo 0
‘ other code
End Sub

GoTo 0 resumes the normal execution.

CATCHING THE ERROR

On Error GoTo ErrorHandler

Example

Sub name()
On Error Resume Next
code…….
If Err.Number = 5 Then MsgBox “Error found.”
On Error GoTo 0
other code……
End Sub

CONGRATULATIONS!!! YOU HAVE CREATED, AND USED YOUR FIRST PROCEDURE

EXAMPLE 1-VBA SUB PROCEDURE

OBJECTIVE OF THE FUNCTION

CREATE A USERFORM WHICH WILL CALCULATE THE SUM OF A SERIES, WHERE THE BASE IS GIVEN , POWER IS GIVEN AND NUMBER OF TERMS IS GIVEN.

E.G. THE SUM OF 2^2+3^2+4^2 UPTO THE SPECIFIED NUMBER OF TERMS

PRE PLANNING OF THE FUNCTION

Let us plan the way we would try to solve this problem.

Here, we have an additional thing, i.e. USERFORM. We need to design a simple userform .
The mathematical calculations will be done in a sub procedure.
The output will be shown in a userform.

STEP 1-DESIGNING THE USERFORM IN vba

Whenever you use any application, we work in GUI i.e. graphical user interface. For example, right now we are working in a userform, where we have labels , text boxes, buttons etc. So let us see how we can design this in VBA.

STEPS TO ADD USERFORM

Right click the project folder in project explorer.
Go to insert>User Form
A userform will be generated. Click the userform.
Design Userform as per need.

look at the picture below for userform.

PICTURE [HOW TO ENTER CODE]

UNDERSTAND USERFORMS

The userforms are the graphic based interface as already discussed.

A small window containing different controls can be seen near the userform. Just pick the controls from there and put on the userform as desired.

Next main thing to be considered is the recognition of the components of userforms.

Here are the steps to find out the name of the components of userforms.

Select the component on the userform.
Check out the name i.e. first field in the bottom lower corner properties window.
Name is the identifier of that component.
For changing the text on the component use CAPTION FIELD.

CREATING SUB PROCEDURE

Now we have to create a code for the execution.

The code for the main funciton is as follows

Sub Label3_Click()

Dim base_digit As Integer ‘picking up value from textbox 1
base_digit = TextBox1.Value

Dim base_power As Integer ‘picking up value from textbox 2
base_power = TextBox2.Value

Dim base_terms As Integer ‘picking up value from textbox 3
base_terms = TextBox3.Value
Dim result As Integer

Call Calculate(base_digit, base_power, base_terms) ‘ Calling the procedure

End Sub

TEXTBOX.VALUE It gives the current value put in any text box.

EXPLANATION

The code has been given above. Here is the explanation for the code.

We have declared three variables as integer for getting the value from TEXTBOXES . The statement used is

Dim base as integer – to declare base as integer

base=textbox.value which will take the value from text box and put it to work.

After taking all the values from the text boxes, we pass them through another sub procedure as

Call Calculate(base_digit, base_power, base_terms)

which we have made to do the calculations by having all the parameters.

After this the procedure ends.

Let us check the calculate procedure now.

Sub Calculate(base_digit, base_power, base_terms) ‘Procedure getting three parameters

Dim temp As Integer

For i = base_digit To base_terms + base_digit – 1 ‘loop for the calculation

temp = temp + i ^ base_power

Next i

Label3.Caption = temp ‘Setting the result in Label 3

End Sub

EXPLANATION

Its a sub procedure to do the calculation part.

As we saw that procedure was called with the three parameters which we had taken the input from the user.

After taking the inputs we’ll put them in a loop

The outer loop is upto the number of terms .

A temporary integer temp is taken to keep the temporary value.

Inside the loop, we add the power of i to the power given , and i will start from the base digit.

The loop will continue till the number of terms.

For all the terms temp will store the values

After finishing the loop the temp will pass the value

to the caption of LABLE3 which was put for the answer.

AFTER THIS WE NEED A SUB TO CALL THE USERFORM SO THAT THE APPLICATION CAN START.

FOR this type in the module, the following code.

Sub cal()

UserForm1.Show ‘Show the userform named USERFORM1.

End Sub

Create a button and assign the macro cal to that.

Run the program.

The picture below shows the program running.

We’ll find the sum of 3^2+4^2+5^2+6^2

The result should be 86

' ( Text followed by an apostrophe) Comments can be put anywhere inside the code starting with an apostrophe. Commenting is very necessary for the code understanding.

USING THE USERFORM

After saving the function, now the function will be listed in our function library .

We can use this function in the workbook anywhere. Let us try to use the function.

EXPLANATION

We have assigned the macro cal to the button.

We clicked the button and window appeared.

Now we’ll put three inputs as asked.

The inputs will be evaluated and answer will be displayed.

TUTORIAL HOME

CONTENTS

USE SUB PROCEDURES USING VBA

INTRODUCTION