
PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
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.
- 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.
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
- Spelling mistakes
- Capital and Small letters
- Wrong passage of arguments
- Number of parenthesis
- Missing some expected character etc.
CORRECTING SYNTAX ERRORS
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
TRAPPING RUNTIME ERRORS
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
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
EXAMPLE 1-VBA SUB PROCEDURE
OBJECTIVE OF THE FUNCTION
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.
PICTURE [HOW TO ENTER CODE]
UNDERSTAND USERFORMS
The userforms are the graphic based interface as already discussed.
- 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
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
EXPLANATION
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
Sub cal()
UserForm1.Show ‘Show the userform named USERFORM1.
End Sub
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.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.