VBA stands for Visual Basic for Applications an event driven programming language from Microsoft that is now predominantly used with Microsoft office applications such as MS-Excel, MS-Word and MS-Access.
It helps techies to build customized applications and solutions to enhance the capabilities of those applications. The advantage of this facility is that we NEED NOT have visual basic installed on our PC but installing office will implicitly help us to achieve the purpose.
We can use VBA in all office versions right from MS-Office 97 to MS-Office 2013 and also with any of the latest versions available. Among VBA, Excel VBA is the most popular one and the reason for using VBA is that we can build very powerful tools in MS Excel using linear programming.
Application of VBA
You might wonder why we need to use VBA in excel as MS-Excel itself provides loads on inbuilt functions. MS-Excel provides only basic inbuilt functions which maynot be sufficient to perform complex calculations. Under those circumstances VBA becomes the most obvious solution.
One of the best examples is it is very hard to calculate monthly repayment for a loan using Excel's built-in formulas but it is easy to program a VBA for such calculation.
Accessing VBA Editor
In Excel window, press "ALT+F11". VBA window opens as shown below.
Excel VBA Macros
In this chapter let us understand how to write a simple macro. Let us take it step by step.
Step 1. First let us enable 'Developer' menu in Excel 20XX. To do the same, click on File >> Options.
Step 2. Click Customize Ribbon Tab and check 'Developer' and click 'OK'.
Step 3. The 'Developer' ribbon appears in menu bar.
Step 4. click 'Visual Basic' Button to open VBA Editor.
Step 5. Now Let us start scripting by adding a button. Click 'Insert' >> Select 'button'.
Step 6. Perform a Right Click and choose 'properties'.
Step 7. Edit the name and Caption as shown below.
Step 8. Now Double click the button, the sub procedure outline would be displayed as shown below.
Step 9. Let us start coding by simply adding a message.
Private Sub say_helloworld_Click()
MsgBox "Hi"
End Sub
Step 10. Now you can click the button to execute the sub-procedure. The Output of the sub-procedure is shown below. We will demostrate further chapters using a simple button as explained from step#1 to 10. Hence It is important to understand this chapter thoroughly.
Excel VBA Terminologies
In this chapter let us understand commonly used excel VBA terminologies. These terminologies will be used in further modules hence understanding each one of these is a key.
Modules
1. Modules is the area where code is written. This is a new Workbook hence there aren't any Modules.
2. To insert a Module navigate to Insert >> Module. Once a module is inserted 'module1' is created. Within the modules, we can write VBA code and the code is written within a Procedure. A Procedure/Sub Procedure is a series of VBA statements instructing what to do.
Procedure
Procedures are group of statements that are executed as a whole which instructs Excel how to perform a specific task. The task performed can be very simple or very complicated and it is a good practice to break down complicated procedures into smaller ones.
The two main types of Procedures are Sub and Function.
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 and statements are written between Function and End Function
Sub Procedures
Sub Procedures work similar to functions while 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.
Comments in VBA
Comments are used to document the program logic and the user information with which other programmers can seamlessly work on the same code in future.
It can include information such as developed by, modified by and it can also include incorporated logic. Comments are ignored by the interpreter while execution.
Comments in VBA are denoted by two methods.
1. Any statement that starts with a Single Quote (�) is treated as comment. Following is the example:
' This Script is invoked after successful login
' Written by : TutorialsPoint
' Return Value : True / False
2. Any statement that starts with the keyword "REM". Following is the example:
REM This Script is written to Validate the Entered Input
REM Modified by : Tutorials point/user2
What is a Message Box?
The MsgBox function displays a message box and waits for the user to click a button and then an action is performed based on the button clicked by the user.
SyntaxMsgBox(prompt[,buttons][,title][,helpfile,context])Parameter Description
- Prompt - A Required Parameter. A String that is displayed as a message in the dialog box. The maximum length of prompt is approximately 1024 characters. If the message extends to more than a line, then we can separate the lines using a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between each line.
- buttons - An Optional Parameter. A Numeric expression that specifies the type of buttons to display, the icon style to use, the identity of the default button, and the modality of the message box. If left blank, the default value for buttons is 0.
- Title - An Optional Parameter. A String expression displayed in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.
- helpfile - An Optional Parameter. A String expression that identifies the Help file to use to provide context-sensitive help for the dialog box.
- context - An Optional Parameter. A Numeric expression that identifies the Help context number assigned by the Help author to the appropriate Help topic. If context is provided, helpfile must also be provided.
The Buttons parameter can take any of the following values:
- 0 vbOKOnly Displays OK button only.
- 1 vbOKCancel Displays OK and Cancel buttons.
- 2 vbAbortRetryIgnore Displays Abort, Retry, and Ignore buttons.
- 3 vbYesNoCancel Displays Yes, No, and Cancel buttons.
- 4 vbYesNo Displays Yes and No buttons.
- 5 vbRetryCancel Displays Retry and Cancel buttons.
- 16 vbCritical Displays Critical Message icon.
- 32 vbQuestion Displays Warning Query icon.
- 48 vbExclamation Displays Warning Message icon.
- 64 vbInformation Displays Information Message icon.
- 0 vbDefaultButton1 First button is default.
- 256 vbDefaultButton2 Second button is default.
- 512 vbDefaultButton3 Third button is default.
- 768 vbDefaultButton4 Fourth button is default.
- 0 vbApplicationModal Application modal. The current application will not work until the user responds to the message box.
- 4096 vbSystemModal System modal. All applications will not work until the user responds to the message box.
The above values are logically divided into four groups: The first group(0 to 5) indicates the buttons to be displayed in the message box. The second group (16, 32, 48, 64) describes the sytle of the icon to be displayed, the third group (0, 256, 512, 768) indicates which button must be the default, and the fourth group (0, 4096) determines the modality of the message box.
Return Values
The MsgBox function can return one of the following values using which we will be able to identify the button the user has clicked in the message box.
- 1 - vbOK - OK was clicked
- 2 - vbCancel - Cancel was clicked
- 3 - vbAbort - Abort was clicked
- 4 - vbRetry - Retry was clicked
- 5 - vbIgnore - Ignore was clicked
- 6 - vbYes - Yes was clicked
- 7 - vbNo - No was clicked
Example
Function MessageBox_Demo()
'Message Box with just prompt message
MsgBox("Welcome")
'Message Box with title, yes no and cancel Butttons
a = MsgBox("Do you like blue color?",3,"Choose options")
' Assume that you press No Button
msgbox ("The Value of a is " & a)
End Function
Output
1. The above Function can be executed either by clicking "Run" Button on VBA Window or by calling the function from Excel Worksheet as shown below.
2. A Simple Message box is displayed with a message "Welcome" and an "OK" Button
3. After Clicking OK, yet another dialog box is displayed with a message and "yes, no, and cancel" buttons.
4. After Clicking Cancel button the value of that button(7) is stored as an integer and displayed as a message box to the user as shown below. Using this value we will be able to know which button user has clicked..
What is an Input Box?
The InputBox function helps the user to get the values from the user. After entering the values, if the user clicks the OK button or presses ENTER on the keyboard, the InputBox function will return the text in the text box. If the user clicks on the Cancel button, the function will return an empty string ("").
Syntax
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])
Parameter Description :
- Prompt - A Required Parameter. A String that is displayed as a message in the dialog box. The maximum length of prompt is approximately 1024 characters. If the message extends to more than a line, then we can separate the lines using a carriage return character (Chr(13)) or a linefeed character (Chr(10)) between each line.
- Title - An Optional Parameter. A String expression displayed in the title bar of the dialog box. If the title is left blank, the application name is placed in the title bar.
- Default - An Optional Parameter. A default text in the text box that the user would like to be displayed.
- XPos - An Optional Parameter. The Position of X axis which represents the prompt distance from left side of the screen horizontally. If left blank, the input box is horizontally centered.
- YPos - An Optional Parameter. The Position of Y axis which represents the prompt distance from left side of the screen Vertically. If left blank, the input box is Vertically centered.
- helpfile - An Optional Parameter. A String expression that identifies the Help file to use to provide context-sensitive Help for the dialog box.
- context - An Optional Parameter. A Numeric expression that identifies the Help context number assigned by the Help author to the appropriate Help topic. If context is provided, helpfile must also be provided.
Example
We will calculate the area of a rectangle by getting values from the user at run time with the help of two input boxes (one for length and one for width)
Function findArea()
Dim Length As Double
Dim Width As Double
Length = InputBox("Enter Length ", "Enter a Number")
Width = InputBox("Enter Width", "Enter a Number")
findArea = Length * Width
End Function
Output
1. To Execute the same, we will need to call using the function name and press Enter as shown below.
2. Upon Execution, The First Input box(Length) is displayed and user has to enter a value into the input box.
3. After entering the first value, the second input box(width) is displayed to the user.
4. Upon entering the second number and clicking OK button, the area is displayed to the user as shown below.
VBA Variables
Variable is a named memory location used to hold a value that can be changed during the script execution. Below are the basic rules for naming a variable. Listed below are the rules for naming a variable.
- You must use a letter as the first character.
- You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
- Name can't exceed 255 characters in length.
- Cannot use Visual Basic reserved keywords as variable name.
Syntax
In VBA, we need to declare the variables before using them.
Dim <<variable_name>> As <<variable_type>>
Data Types
There are many VBA data types, which can be grossly divided into two main categories namely numeric and non-numeric data types.
Numeric Data-Types
Below table displays the numeric data types and allowed range of values.
| Type | Range of Values |
|---|---|
| Byte | 0 to 255 |
| Integer | -32,768 to 32,767 |
| Long | -2,147,483,648 to 2,147,483,648 |
| Single | -3.402823E+38 to -1.401298E-45 for negative values 1.401298E-45 to 3.402823E+38 for positive values. |
| Double | -1.79769313486232e+308 to -4.94065645841247E-324 for negative values 4.94065645841247E-324 to 1.79769313486232e+308 for positive values. |
| Currency | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Decimal | +/- 79,228,162,514,264,337,593,543,950,335 if no decimal is use +/- 7.9228162514264337593543950335 (28 decimal places). |
Non-Numeric Data Types
Below table displays the Non-numeric data types and allowed range of values.
| Type | Range of Values |
|---|---|
| String(fixed length) | 1 to 65,400 characters |
| String(variable length) | 0 to 2 billion characters |
| Date | January 1, 100 to December 31, 9999 |
| Boolean | True or False |
| Object | Any embedded object |
| Variant(numeric) | Any value as large as Double |
| Variant(text) | Same as variable-length string |
Example
Let us create a button and name it as 'Variables_demo' to demostrate the use of variables.
Private Sub Variables_demo_Click()
Dim password As String
password = "Admin#1"
Dim num As Integer
num = 1234
Dim BirthDay As Date
BirthDay = 30 / 10 / 2020
MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " & num & Chr(10) & "Value of Birthday is " & BirthDay
End Sub
Output
Upon Executing the script, the output will be as shown below.
VBA Constants
Constant is a named memory location used to hold a value that CANNOT be changed during the script execution. If a user tries to change a Constant Value, the Script execution ends up with an error. Constants are declared the same way the variables are declared.
Below are the rules for naming a constant.
- You must use a letter as the first character.
- You can't use a space, period (.), exclamation mark (!), or the characters @, &, $, # in the name.
- Name can't exceed 255 characters in length.
- Cannot use Visual Basic reserved keywords as variable name.
Syntax
In VBA, we need to assign a value to the declared Constants. Error would be thrown if we try to change the value of the constant.
Const <<constant_name>> As <<constant_type>> = <<constant_value>>
Example
We will create a button "Constant_demo" to demonstrate how to work with constants.
Private Sub Constant_demo_Click()
Const MyInteger As Integer = 42
Const myDate As Date = #2/2/2020#
Const myDay As String = "Sunday"
MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is " & myDate & Chr(10) & "myDay is " & myDay
End Sub
Output
Upon executing the script, the output will be displayed as shown below.
VBA Operators
What is an operator?
Simple answer can be given using expression 4 + 5 is equal to 9. Here, 4 and 5 are called operands and + is called operator. VBA supports following types of operators:
- Arithmetic Operators
- Comparison Operators
- Logical (or Relational) Operators
- Concatenation Operators
The Arithmatic Operators
There are following arithmatic operators supported by VBA:
Assume variable A holds 5 and variable B holds 10, then:
| Operator | Description | Example |
|---|---|---|
| + | Adds two operands | A + B will give 15 |
| - | Subtracts second operand from the first | A - B will give -5 |
| * | Multiply both operands | A * B will give 50 |
| / | Divide numerator by denumerator | B / A will give 2 |
| % | Modulus Operator and remainder of after an integer division | B MOD A will give 0 |
| ^ | Exponentiation Operator | B ^ A will give 100000 |
The Comparison Operators
There are following comparison operators supported by VBA:
Assume variable A holds 10 and variable B holds 20, then:
| Operator | Description | Example |
|---|---|---|
| == | Checks if the value of two operands are equal or not, if yes then condition becomes true. | (A == B) is False. |
| <> | Checks if the value of two operands are equal or not, if values are not equal then condition becomes true. | (A <> B) is True. |
| > | Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true. | (A > B) is False. |
| < | Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true. | (A < B) is True. |
| >= | Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true. | (A >= B) is False. |
| <= | Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true. | (A <= B) is True. |
The Logical Operators:
There are following logical operators supported by VBA:
Assume variable A holds 10 and variable B holds 0, then:
| Operator | Description | Example |
|---|---|---|
| AND | Called Logical AND operator. If both the conditions are True then Expression becomes true. | a<>0 AND b<>0 is False. |
| OR | Called Logical OR Operator. If any of the two conditions are True then condition becomes true. | a<>0 OR b<>0 is true. |
| NOT | Called Logical NOT Operator. Use to reverses the logical state of its operand. If a condition is true then Logical NOT operator will make false. | NOT(a<>0 OR b<>0) is false. |
| XOR | Called Logical Exclusion. It is the combination of NOT and OR Operator. If one, and only one, of the expressions evaluates to True, result is True. | (a<>0 XOR b<>0) is false. |
The Concatenation Operators
There are following Concatenation operators supported by VBA:
Assume variable A holds 5 and variable B holds 10 then:
| Operator | Description | Example |
|---|---|---|
| + | Adds two Values as Variable Values are Numeric | A + B will give 15 |
| & | Concatenates two Values | A & B will give 510 |
Assume variable A="Microsoft" and variable B="VBScript", then:
| Operator | Description | Example |
|---|---|---|
| + | Concatenates two Values | A + B will give MicrosoftVBScript |
| & | Concatenates two Values | A & B will give MicrosoftVBScript |
Note : Concatenation Operators can be used for both numbers and strings. The Output depends on the context if the variables hold numeric value or String Value.