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
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.
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
“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 ways1. Ignoring the error.2. Specific code to tackle the error conditions.
IGNORING THE ERROR
USE THE FOLLOWING STATEMENT
ONERROR RESUME NEXT
On Error Resume Next
On Error GoTo 0
‘ other code
End Sub GoTo 0 resumes the normal execution.
CATCHING THE ERROR
On Error GoTo ErrorHandler
Sub name() On Error Resume Next code……. If Err.Number = 5 Then MsgBox "Error found." On Error GoTo 0 other code…… End Sub
CONGRATULATIONS!!! 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]
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 function 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 base_power = TextBox2.Value 'picking up value from textbox 2 Dim base_terms As Integer base_terms = TextBox3.Value 'picking up value from textbox 3 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.
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
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
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 up to 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.
UserForm1.Show ‘Show the userform named USERFORM1.
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.
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.