VBA - Decisions
Decision making allows programmers to control the execution flow of a script or one of its sections. The execution is governed by one or more conditional statements.
Following is the general form of a typical decision making structure found in most of the programming languages:
VBA provides following types of decision making statements. Click the following links to check their details.
| Statement | Description |
|---|---|
| if statement | An if statement consists of a boolean expression followed by one or more statements. |
| if..else statement | An if else statement consists of a boolean expression followed by one or more statements. If the condition is True, the statements under If statements are executed. If the condition is false, Else part of the script is Executed |
| if...elseif..else statement | An if statement followed by one or more ElseIfStatements, that consists of boolean expressions and then followed by an optional else statement, which executes when all the condition becomes false. |
| nested if statements | An if or elseif statement inside another if or elseifstatement(s). |
| switch statement | A switch statement allows a variable to be tested for equality against a list of values. |
VBA - Loops
There may be a situation when you need to execute a block of code several number of times. In general, statements are executed sequentially: The first statement in a function is executed first, followed by the second, and so on.
Programming languages provide various control structures that allow for more complicated execution paths.
A loop statement allows us to execute a statement or group of statements multiple times and following is the general from of a loop statement in VBA.
VBA provides the following types of loops to handle looping requirements. Click the following links to check their detail.
| Loop Type | Description |
|---|---|
| for loop | Executes a sequence of statements multiple times and abbreviates the code that manages the loop variable. |
| for ..each loop | This is executed if there is at least one element in group and reiterated for each element in a group. |
| while..wend loop | This tests the condition before executing the loop body. |
| do..while loops | The do..While statements will be executed as long as condition is True.(i.e.,) The Loop should be repeated till the condition is False. |
| do..until loops | The do..Until statements will be executed as long as condition is False.(i.e.,) The Loop should be repeated till the condition is True. |
Loop Control Statements:
Loop control statements change execution from its normal sequence. When execution leaves a scope, all the remaining statements in the loop are NOT executed.
VBA supports the following control statements. Click the following links to check their detail.
| Control Statement | Description |
|---|---|
| Exit For statement | Terminates the For loop statement and transfers execution to the statement immediately following the loop |
| Exit Do statement | Terminates the Do While statement and transfers execution to the statement immediately following the loop |
VBA Strings
Strings are a sequence of characters, which can consist of alphabets or numbers or special characters or all of them. A variable is said to be a string if it is enclosed within double quotes " ".
Syntax :
variablename = "string"
Examples :
str1 = "string" ' Only Alphabets
str2 = "132.45" ' Only Numbers
str3 = "!@#$;*" ' Only Special Characters
Str4 = "Asc23@#" ' Has all the above
String Functions :
There are predefined VBA String functions, which help the developers to work with the strings very effectively. Below are String methods that are supported in VBA. Please click on each one of the methods to know in detail.
| Function Name | Description |
|---|---|
| InStr | Returns the first occurence of the specified substring. Search happens from left to right. |
| InstrRev | Returns the first occurence of the specified substring. Search happens from Right to Left. |
| Lcase | Returns the lower case of the specified string. |
| Ucase | Returns the Upper case of the specified string. |
| Left | Returns a specific number of characters from the left side of the string. |
| Right | Returns a specific number of characters from the Right side of the string. |
| Mid | Returns a specific number of characters from a string based on the specified parameters. |
| Ltrim | Returns a string after removing the spaces on the left side of the specified string. |
| Rtrim | Returns a string after removing the spaces on the right side of the specified string. |
| Trim | Returns a string value after removing both leading and trailing blank spaces. |
| Len | Returns the lenght of the given string. |
| Replace | Returns a string after replacing a string with another string. |
| Space | Fills a string with the specified number of spaces. |
| StrComp | Returns an integer value after comparing the two specified strings. |
| String | Returns a String with a specified character the specified number of times. |
| StrReverse | Returns a String after reversing the sequece of the characters of the given string. |
VBA Date-Time Function
VBScript Date and Time Functions help the developers to convert date and time from one format to another or to express the date or time value in the format that suits a specific condition.
Date Functions
| Function | Description |
|---|---|
| Date | A Function, which returns the current system date |
| CDate | A Function, which converts a given input to Date |
| DateAdd | A Function, which returns a date to which a specified time interval has been added |
| DateDiff | A Function, which returns the difference between two time period |
| DatePart | A Function, which returns a specified part of the given input date value |
| DateSerial | A Function, which returns a valid date for the given year,month and date |
| FormatDateTime | A Function, which formats the date based on the supplied parameters |
| IsDate | A Function, which returns a Boolean Value whether or not the supplied parameter is a date |
| Day | A Function, which returns an integer between 1 and 31 that represents the day of the specified Date |
| Month | A Function, which returns an integer between 1 and 12 that represents the month of the specified Date |
| Year | A Function, which returns an integer that represents the year of the specified Date |
| MonthName | A Function, which returns Name of the particular month for the specifed date |
| WeekDay | A Function, which returns an integer(1 to 7) that represents the day of the week for the specified day. |
| WeekDayName | A Function, which returns the weekday name for the specified day. |
Time Functions
| Function | Description |
|---|---|
| Now | A Function, which returns the current system date and Time |
| Hour | A Function, which returns and integer between 0 and 23 that represents the Hour part of the the given time |
| Minute | A Function, which returns and integer between 0 and 59 that represents the Minutes part of the the given time |
| Second | A Function, which returns and integer between 0 and 59 that represents the Seconds part of the the given time |
| Time | A Function, which returns the current system time |
| Timer | A Function, which returns the number of seconds and milliseconds since 12:00 AM |
| TimeSerial | A Function, which returns the time for the specific input of hour,minute and second |
| TimeValue | A Function, which converts the input string to a time format |
VBA Arrays
What is an Array?
We know very well that a variable is a container to store a value. Sometimes, developers are in a position to hold more than one value in a single variable at a time. When a series of values are stored in a single variable, then it is known as array variable.
Array Declaration
Arrays are declared the same way a variable has been declared except that the declaration of an array variable uses paranthesis. In the below example, the size of the array is mentioned in the brackets.
'Method 1 : Using Dim
Dim arr1() 'Without Size
'Method 2 : Mentioning the Size
Dim arr2(5) 'Declared with size of 5
'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")
- Although, the Array size is indicated as 5, it can hold 6 values as array index starts from ZERO.
- Array Index Cannot be Negative.
- VBScript Arrays can store any type of variable in an array. Hence, an array can store an integer, string or characters in a single array variable.
Assigning Values to an Array
The values are assigned to the array by specifying array index value against each one of the values to be assigned. It can be a string.
Example :
Add a button and add the below function
Private Sub Constant_demo_Click()
Dim arr(5)
arr(0) = "1" 'Number as String
arr(1) = "VBScript" 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("Value stored in Array index 0 : " & arr(0))
msgbox("Value stored in Array index 1 : " & arr(1))
msgbox("Value stored in Array index 2 : " & arr(2))
msgbox("Value stored in Array index 3 : " & arr(3))
msgbox("Value stored in Array index 4 : " & arr(4))
msgbox("Value stored in Array index 5 : " & arr(5))
End Sub
When you execute the function the output is shown below:
Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM
Multi Dimension Arrays
Arrays are not just limited to single dimenstion and can have a maxinum of 60 dimensions. Two-dimension arrays are the most commonly used ones.
Example :
In the below example, a multi-dimension array is declared with 3 rows and 4 columns.
Private Sub Constant_demo_Click()
Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
arr(0,0) = "Apple"
arr(0,1) = "Orange"
arr(0,2) = "Grapes"
arr(0,3) = "pineapple"
arr(1,0) = "cucumber"
arr(1,1) = "beans"
arr(1,2) = "carrot"
arr(1,3) = "tomato"
arr(2,0) = "potato"
arr(2,1) = "sandwitch"
arr(2,2) = "coffee"
arr(2,3) = "nuts"
msgbox("Value in Array index 0,1 : " & arr(0,1))
msgbox("Value in Array index 2,2 : " & arr(2,2))
End Sub
When you execute the function the output is shown below:
Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee
Redim Statement
ReDim Statement is used to Declare dynamic-array variables and allocate or reallocate storage space.
ReDim [Preserve] varname(subscripts) [, varname(subscripts)]
- Preserve - An Optional parameter used to preserve the data in an existing array when you change the size of the last dimension.
- varname - A Required parameter, which denotes Name of the variable, which should follow the standard variable naming conventions.
- subscripts - A Required parameter, which indicates the size of the array.
Example
In the below example, an array has been redefined and then preserved the values when the existing size of the array is changed.
Note : Upon resizing an array smaller than it was originally, the data in the eliminated elements will be lost.
Private Sub Constant_demo_Click()
Dim a() as variant
i=0
redim a(5)
a(0)="XYZ"
a(1)=41.25
a(2)=22
REDIM PRESERVE a(7)
For i=3 to 7
a(i)= i
Next
'to Fetch the output
For i=0 to ubound(a)
Msgbox a(i)
Next
End Sub
When you execute the function the output is shown below:
XYZ
41.25
22
3
4
5
6
7
Array Methods :
There are various inbuilt functions within VBScript which help the developers to handle arrays effectively. All the methods that are used in conjuction with arrays are listed below. Please click on the method name to know in detail.
| Function | Description |
|---|---|
| LBound | A Function, which returns an integer that corresponds to the smallest subscript of the given arrays. |
| UBound | A Function, which returns an integer that corresponds to the Largest subscript of the given arrays. |
| Split | A Function, which returns an array that contains a specified number of values. Splitted based on a Delimiter. |
| Join | A Function, which returns a String that contains a specified number of substrings in an array. This is an exact opposite function of Split Method. |
| Filter | A Function, which returns a zero based array that contains a subset of a string array based on a specific filter criteria. |
| IsArray | A Function, which returns a boolean value that indicates whether or not the input variable is an array. |
| Erase | A Function, which recovers the allocated memory for the array variables. |
VBA User Defined Arrays and Functions
What is a Function?
A function is a group of reusable code which can be called anywhere in your program. This eliminates the need of writing same code over and over again. This will enable programmers to divide a big program into a number of small and manageable functions.
Apart from inbuilt Functions, VBA allows us to write user-defined functions as well. This section will explain you how to write your own functions in VBA.
Function Definition
A VBA function can have an optional return statement. This is required if you want to return a value from a function.
For example, you can pass two numbers in a function and then you can expect from the function to return their multiplication in your calling program.
NOTE : A function can return multiple values separated by comma as an array assigned to the function name itself.
Before we use a function, we need to define that particular function. The most common way to define a function in VBA is by using the Function keyword, followed by a unique function name and it may or may not carry a list of parameters and a statement with aEnd Function keyword, which indicates the end of the function. The basic syntax is shown below:
Syntax
Add a button and add the below function
Function Functionname(parameter-list)
statement 1
statement 2
statement 3
.......
statement n
End Function
Example
Add the below function which returns the area. Note that a value/values can be returned with the function name itself.
Function findArea(Length As Double, Optional Width As Variant)
If IsMissing(Width) Then
findArea = Length * Length
Else
findArea = Length * Width
End If
End Function
Calling a Function
To invoke a function, call the function using function name as shown below:
The Output of the area would be displayed to the user.
VBA Sub Procedure
Sub Procedures
Sub Procedures are similar to functions but there are few differences.
- Sub procedures DONOT Return a value while functions may or may not return a value.
- Sub procedures Can be called without call keyword.
- Sub procedures are always enclosed within Sub and End Sub statements.
Example :
Sub Area(x As Double, y As Double)
MsgBox x * y
End Sub
Calling Procedures :
To invoke a Procedure somewhere in the script, you can make a call from a function. We will not be able to use the same way as that of a function as sub-procedure WILL NOT return a value.
Function findArea(Length As Double, Width As Variant)
area Length, Width ' To Calculate Area 'area' sub proc is called
End Function
1. Now we will be able to call the function only but not the sub procedure as shown below.
2. The Area is calculated and shown only in Message box.
3. The result cell displays ZERO as the area value is NOT returned from the function. In short, you cannot make a direct call to a subprocedure from the excel worksheet.
