VBA for Excel
VBA is based on VB (Visual Basic). This is a programming language derived from BASIC (Beginner’s All-purpose Symbolic Instruction Code). VBA shares a common core of objects and commands as VB but also includes Microsoft application specific objects and language elements. For example, references to Excel objects.
VBA stands for Visual Basic for Applications. VBA is a programming language created by Microsoft. You can use VBA to automate operations in MS office applications like Excel, Word, Access, but we will mainly discuss Excel applications here.
So what can we do in Excel using VBA? The answer is simple: whatever you can do in excel manually, there is a way to automate it with VBA.
How to automate or streamline tasks using VBA:
- You can record a macro which automatically carries out a series of actions that you frequently perform.
- Writing a code in the VBA editor: the code will perform various actions in a certain order.
- Interacting with the user by creating forms or dialogue boxes.
- Using VBA you can manipulate files that are not currently active.
- You can also manipulate one application with another application for example, you can make extract a query from Access into your Excel file. Or you can use Excel to send an email through Outlook.
- You can download information from web sites, such as stock information from Yahoo finance
Writing you first VBA code in excel
Step 1: Open excel workbook, press ALT+F11 or go to Developer tab in Excel Ribbon, and click Visual Basic
Step 2 – Write in the code modular below code
Sub ShowMessagbox()
Msgbox(“Hello world”)
End Sub
When you will run the code below message box will pop at excel workbook.
Congratulation, you have created your first macro and have written your first VBA code.
Variable and declaring the variable:
A variable is one of the most important weapons for applying logic and writing codes dynamically.
A variable is a named area in memory that you use for storing data while a procedure is running.
Variables can be of different types:
String variable – to store text
Integer variables – for storing integer values
Date variables – storing date and time
Boolean variable – for storing True or False
Object variable –to store objects
Array variable – to store multiple pieces of information at the same time (advanced concept)
A few important things you should know about variables and when you use variables:
Variable name must start with a letter and can be up to 255 characters in length.
Variable names must not contain spaces, for example ‘final row’ is wrong it should be ‘final_row’.
Variable names can not contain characters such as periods, mathematical operators (+,-,/,*), exclamation points, comparison operators(=, <>,>, etc), or type declaration characters (&,#, $).
Declaring Variable:
It’s a good idea to declare your variable first:
Dim x as integer
or
Dim x as String
Then you can use the variable in your code, such as:
X = 45
Or
X = Range("A1")
IF statements:
If statements are used in most programming languages for making decisions. Types of If statement for VBA:
If ……Then
If ……Then ………..Else
If …….Then ……..Elself ……..Else
If …….Then
In this statement, you tell VBA that if the condition is met, then execute the next statement. If the condition is not met, skip the next line and reach the End If statement. If statements always begin with “If” and end with “End If”.
If condition Then
[Statements]
End If
Example:
If StAge < 21 then
MsgBox “You may not purchase alcohol”
End If
If ……..then ……..Else:
This statement is used when you decide between two courses of action. You can take one course of action if a condition is True and another course of action if it is False.
Syntax:
If condition Then
Statements 1
Else
Statements 2
End If
Example:
If StAge < 21 then
MsgBox “You may not purchase alcohol…underage”
Else
MsgBox “You may purchase”
End If
If ……..Then ……..Elself ……..Else:
This If statement is used when you want VBA to decide between multiple courses of action. You can use multiple Elself statements, it depends on the complexity of your algorithm.
Syntax:
If condition 1 Then
Statement 1
ElseIf condition 2 Then
Statement 2
[ElseIf condition 3 Then
Statement 3]
[Else
Statement 4]
End If
If the condition expressed in condition1 is True, VBA executes statement1, the first block of statements, and then resumes execution at the line after the End If clause. If condition1 is False, VBA branches to the first ElseIf clause and evaluates the condition expressed in condition2. If this is True, VBA executes statement2 and then moves to the line after the End If line; if it’s False, VBA moves to the next ElseIf clause (if there is one) and evaluates its condition (here, condition3).
Loops in VBA:
In VBA you can use loops to repeat actions. By using loops you can transform a simple recorded macro into a powerful program. You usually use loops to repeat an action until a certain condition is met.
Various kind of loops available in VBA –
For ---Next
For Each……Next
Do While…Loop
While…When
Do Until……Loop
Do…Loop While
Do….Loop Until
Each of these have different times and applications when they are most appropriate.
For…Next :
Very useful loop but is limited because it has a fixed number of repititions.
Syntax
For counter = start to end [Step stepsize]
[statement]
[Exit For]
[statement]
Next [counter]
Example:
Sub Example ()
For x = 1 to 10
Cells(x,1).value = x
Next
End Sub
When you run this code it will loop 10 times and will put 1 to 10 values in range A1:A10.
For Each …Next Loops:
Same as For ..next loop where you work with a known number of repetitions but in For Each Next loops a known number is the number of objects in a collection.
Syntax:
For Each object in collection
[statements]
[Exit For]
[statements]
Next [object]
Do …Loops:
Do loops give you more flexibility than For loops because you can test conditions in them and direct the flow of the procedure accordingly.
Loops that test a condition before performing any action. Do While…Loop and Do Until…Loops are used.
Loops that perform an action before testing a condition. Do..Loop While and Do …Loop Until are used.
Syntax for Do While ….Loop:
Do While condition
[statements]
[Exit Do]
[statements]
Loop
Syntax for Do…Loop While:
If the condition is True, the loop continues to run until the condition becomes False, the actions in the loop are executed at least once, whether the condition is True or False
Do
[statements]
[Exit Do]
[statements]
Loop While condition
Syntax for Do Untill…Loop
Do Until condition
[statements]
[Exit Do]
[statements]
Loop
Syntax for Do …Loop Until:
Do
[statements]
[Exit Do]
[statements]
Loop Until condition
Referencing Cells and Ranges in VBA:
You can select a single cell using this code.
Range("A1").Select
If you want to select set of contiguous cells you will use the colon and write:
Range("A1:G5").Select
If you want to select set of non-contiguous cells you will use the comma and write:
Range("A1,A5,B4").Select
If you want to select a set of non-contiguous cells and a range you will use both the colon and the comma:
Range("A1,A5,B4:B8").Select
Cells(1,1).Select is the same thing as
Range("A1").Select and
Cells(2,4).Select is the same as
Range("D2").Select.
The Cells method is useful when using variables and combining with loops ie:
Cells(x,y).select
And
Do Until Cells(x,1) = 5
Loop
You can use Cells when you want to select all the cells of a worksheet. For example:
Cells.Select
To select all cells and then to empty all cells of values or formulas you will use:
Cells.ClearContents
You can combine the offset feature with VBA:
To move one cell down (from A1 to A2):
Range("A1").Offset(1,0).Select
To move one cell to the right (from A1 to A2):
Range("A1").Offset(0,1).Select
To move one cell up (from B2 to B1):
Range("B2").Offset(-1,0).Select
To move one cell to the left (from B2 to A2):
Range("B2").Offset(0,-1).Select
To move one cell down from the selected cell:
ActiveCell.Offset(1,0).Select
As you can see, the first argument between the parentheses for Offset is the number of rows and the second one is the number of columns. So to move from A1 to G6 you will need:
Range("A1").Offset(5,6).Select
This gives you enough to get started in exploring the power of VBA and Excel. Good luck!
