VBA - Sub Procedure

Sub Procedures are similar to functions, however there are a few differences.

• Sub procedures DO NOT Return a value while functions may or may not return a value.
• Sub procedures CAN be called without a 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

Now you will be able to call the function only but not the sub procedure as shown in the following screenshot.

The area is calculated and shown only in the Message box.

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 sub procedure from the excel worksheet.

VBA - User Defined Functions

A function is a group of reusable code which can be called anywhere in your program. This eliminates the need of writing the same code over and over again. This enables the programmers to divide a big program into a number of small and manageable functions.

Apart from inbuilt functions, VBA allows to write user-defined functions as well. In this chapter, you will learn 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 a 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 End Function keyword, which indicates the end of the function. Following is the basic syntax.

Syntax

Add a button and add the following function.

Function Functionname(parameter-list)
   statement 1
   statement 2
   statement 3
   .......
   statement n
End Function

Example

Add the following 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 the function name as shown in the following screenshot.

The output of the area as shown below will be displayed to the user.

VBA - Arrays

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 an array variable.

Array Declaration

Arrays are declared the same way a variable has been declared except that the declaration of an array variable uses parenthesis. In the following 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 an array index value against each one of the values to be assigned. It can be a string.

Example

Add a button and add the following 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 above function, it produces the following output.

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-Dimensional Arrays

Arrays are not just limited to a single dimension, however, they can have a maximum of 60 dimensions. Two-dimensional arrays are the most commonly used ones.

Example

In the following example, a multi-dimensional 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 above function, it produces the following output.

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.

Syntax

ReDim [Preserve] varname(subscripts) [, varname(subscripts)]

Parameter Description

• 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 the 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 following example, an array has been redefined and then the values preserved 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 above function, it produces the following output.

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 conjunction with arrays are listed below. Please click on the method name to know about it in detail.

S.No.	Function & Description
1	LBound A Function, which returns an integer that corresponds to the smallest subscript of the given arrays.
2	UBound A Function, which returns an integer that corresponds to the largest subscript of the given arrays.
3	Split A Function, which returns an array that contains a specified number of values. Split based on a delimiter.
4	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.
5	Filter A Function, which returns a zero based array that contains a subset of a string array based on a specific filter criteria.
6	IsArray A Function, which returns a boolean value that indicates whether or not the input variable is an array.
7	Erase A Function, which recovers the allocated memory for the array variables.

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

S.No.	Function & Description
1	Date A Function, which returns the current system date.
2	CDate A Function, which converts a given input to date.
3	DateAdd A Function, which returns a date to which a specified time interval has been added.
4	DateDiff A Function, which returns the difference between two time period.
5	DatePart A Function, which returns a specified part of the given input date value.
6	DateSerial A Function, which returns a valid date for the given year, month, and date.
7	FormatDateTime A Function, which formats the date based on the supplied parameters.
8	IsDate A Function, which returns a Boolean Value whether or not the supplied parameter is a date.
9	Day A Function, which returns an integer between 1 and 31 that represents the day of the specified date.
10	Month A Function, which returns an integer between 1 and 12 that represents the month of the specified date.
11	Year A Function, which returns an integer that represents the year of the specified date.
12	MonthName A Function, which returns the name of the particular month for the specified date.
13	WeekDay A Function, which returns an integer(1 to 7) that represents the day of the week for the specified day.
14	WeekDayName A Function, which returns the weekday name for the specified day.

Time Functions

S.No.	Function & Description
1	Now A Function, which returns the current system date and time.
2	Hour A Function, which returns an integer between 0 and 23 that represents the hour part of the given time.
3	Minute A Function, which returns an integer between 0 and 59 that represents the minutes part of the given time.
4	Second A Function, which returns an integer between 0 and 59 that represents the seconds part of the given time.
5	Time A Function, which returns the current system time.
6	Timer A Function, which returns the number of seconds and milliseconds since 12:00 AM.
7	TimeSerial A Function, which returns the time for the specific input of hour, minute and second.
8	TimeValue A Function, which converts the input string to a time format.

VBA - Strings

Strings are a sequence of characters, which can consist of either alphabets, numbers, 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. Following are String methods that are supported in VBA. Please click on each one of the methods to know in detail.

S.No.	Function Name & Description
1	InStr Returns the first occurrence of the specified substring. Search happens from the left to the right.
2	InstrRev Returns the first occurrence of the specified substring. Search happens from the right to the left.
3	Lcase Returns the lower case of the specified string.
4	Ucase Returns the upper case of the specified string.
5	Left Returns a specific number of characters from the left side of the string.
6	Right Returns a specific number of characters from the right side of the string.
7	Mid Returns a specific number of characters from a string based on the specified parameters.
8	Ltrim Returns a string after removing the spaces on the left side of the specified string.
9	Rtrim Returns a string after removing the spaces on the right side of the specified string.
10	Trim Returns a string value after removing both the leading and the trailing blank spaces.
11	Len Returns the length of the given string.
12	Replace Returns a string after replacing a string with another string.
13	Space Fills a string with the specified number of spaces.
14	StrComp Returns an integer value after comparing the two specified strings.
15	String Returns a string with a specified character for specified number of times.
16	StrReverse Returns a string after reversing the sequence of the characters of the given string.