Visual Basic for Applications

Basic VBA training concentrated on beginners

Vishal Kumar 15th May 2018

 Contents
 What is VBA  Special Exercise : Interpolation and Extrapolation
 Accessing VBA  Arrays
 VBA Terminologies  Functions
 Range Objects  VBA Excel Functions
 Your First Program  ActiveX Controls
 Comments in VBA Code  UserForms
 Variables
 Constants
 Operators
 Cells Object
 Desicions
 Loops
 Exiting Loops

2
 What is VBA
VBA and why it is useful

 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 etc.

 It helps techies to build customized applications and solutions to enhance the

capabilities of those applications.

 Among VBA, Excel VBA is the most popular. The advantage of using VBA is
that you can build very powerful tools in MS Excel using linear programming.

 MS-Excel provides only basic inbuilt functions which might not be sufficient to
perform complex calculations. Under such circumstances, VBA becomes the
most obvious solution.
3
 Accessing VBA

 Make sure DEVELOPER ribbon is active.

 Accessing Visual Basic

4
 Setting Up the VBA Window

 Project Tab
 Properties Tab
 Background and Text Color
 Short Cuts

5
 VBA Terminologies
Some Basic Terminologies

 Modules : Set of standardized parts or independent units that can be used to

construct a more complex structure. Within the modules, we can write VBA
code and the code is written within a Procedure.

 Procedure : Procedures are a group of statements executed as a whole,

which instructs Excel how to perform a specific task.
 Sub : Sub-procedures work similar to functions. While sub procedures DO NOT
Return a value, functions may or may not return a value.
 Function : A function is a group of reusable code, which can be called anywhere in
your program. Excel Formulae are functions as they return a value.

 Objects : In Excel VBA, an object can contain another object, and that object can
contain another object, etc. In other words, Excel VBA programming involves working
with an object hierarchy.

6
 Range Object
 In Excel, Excel Workbook itself is the mother of all objects. The hierarchy in
excel follows the following sequence:
Workbook  Worksheet  Range/Cell/Array etc.Property

 Range : Represents a cell, a row, a column, a selection of cells containing

one or more contiguous blocks of cells, or a 3-D range.

 The objects are connected with a dot.

7
 Your First Program
Hello World

 Message Box

 Filling values in Cells (Range)

8
 Adding Comments in VBA
 To document user information within the code.

2 methods:

 Using a Single Quote (’)

 Starting the statement with REM

9
 VBA - Variables

 Variable is a named memory location used to hold a value that can be

changed during the script execution. Following are the basic 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.
 You cannot use Visual Basic reserved keywords as variable name.

 In VBA, you need to declare the variables before using them.

 Syntax for declaring variables is:
Dim <<variable_name>> As <<variable_type>>

10
 Data Types
Used for Declaration – Numeric Types

11
 Data Types
Used for Declaration – Non-Numeric Type

Run a simple procedure/Function for variable test in VBA.

12
 VBA - Constants

 Constant is a named memory location used to hold a value that CANNOT be

changed during the script execution.

 Constants are declared the same way the variables are declared.

 Syntax:
Const <<constant_name>> As <<constant_type>> = <<constant_value>>

 Run a Simple procedure/Function to test Constants

13
 VBA - Operators
 An Operator can be defined using a simple 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

 These operators are used to perform calculations, modify/compare data and

also merge values. These data/values can be both numerical or string.

14
 VBA - Operators
The Arithmatic Operators

 Assume variable A holds 5 and variable B holds 10

15
 VBA - Operators
The Comparison Operators

 Assume variable A holds 10 and variable B holds 20

16
 VBA - Operators
The Logical Operators

 Assume variable A holds 10 and variable B holds 0

17
 VBA - Operators
The Concatenation Operators

 Assume variable A holds 5 and variable B holds 10

 Assume variable A = "Microsoft" and variable B = "VBScript"

 Concatenation operates the same for both numbers and string.

18
 VBA – InputBox and MsgBox Function
 InputBox asks the user to enter values.
 MsgBox displays a message box and waits for user to click a button.
 InputBox and MsgBox are functions that returns a value.

 Exercise : Creating an area calculator

19
 Workshop 1

 Activity 1 : .Font Property

(.Bold, .Italic, .Underline, .Size, .Name)
 Activity 2 : .Value & .Text property
 Activity 3 : .Row and .Column Property
 Activity 4 : .Select and .Count Property
 Activity 5 : .Row, .Column and .Address Property
 Activity 6 : .Formula
 Activity 7 : Testing Operators

20
 Cells Object
 Cells (row number, column number)
 Can be used with Range Object as
 Range(cells(1,2) , cells (3,4))
 Useful when using Column Number instead of Column Alphabet name.

21
 Decisions
If based statements

 Decision making allows

the programmers to
control the execution flow
of a script or one of its
sections.

 The execution is governed

by one or more conditional
statements.

22
 Workshop 2
Testing If Statements

In the below table we have a Grade Table. Our task is to write a program that
accepts Marks from user and displays the corresponding Grade.

23
 Loops
For and Do based statements

 There may be a situation

when you need to execute
a block of code several
number of times.

 A loop statement helps us

achieve that.

 Loop Control statements

are used to exit loops.

24
 Loops – Do... While... Loop
 Code placed between Do While and Loop will be repeated as long as the part
after Do While is true.
 In For Loop, we provide a range to be checked. But a Do-While loop is
conditional.
 Syntax :
Dim i As Integer
i=1

Do While i < 6
Cells(i, 1).Value = 20
i=i+1
Loop
As long as i is lower than 6, Excel VBA enters the value 20 into the cell at the intersection
of row i and column 1 and increments i by 1.

25
 Loops – Loop Control Statements
 Loop Control statements are
used to exit loops.

 Exit For Syntax

For i = 1 To 100
If dValues(i) =
dVal Then
indexVal = i
Exit For
End If
Next i

26
 Functions in VBA
 A function is a group of reusable code which can be called anywhere in your
program.
 The difference between a function and a sub in Excel VBA is that a function
can return a value while a sub cannot. Functions become very useful as
program size increases.
 Calling a function in Excel  using = Functionname(Input)

27
 Special Activity 1 – Interpolation and Extrapolation
 Useful if you find yourself needing to interpolate from tables of data
frequently.
 Mathematics behind it :

 Required items:
 An array of known x-values
 An array of known y-values
 The value of x for which we want to estimate a corresponding y-value
 Desired Solution :
 A Function which takes ‘Required Items’ as Input and provides interpolated value
as output.

28
 VBA - ARRAYS

 Variables can hold values

 Arrays can hold more than 1 value.

 Array Declaration :
'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")

29
 VBA – ARRAYS... Continued...
 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.

30
 Multi-Dimensional Arrays
Oh yes... Multi-dimensional

 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.

31
 Re-Dimensioning the Array
 Existing Array can be redimensioned.
 ReDim statement is used to declare dynamic-array variables and allocate or
reallocate storage space.

32
 Array inbuilt functions

33
 VBA - Application Object
 The mother of all objects is Excel itself. We call it the Application object.
 The application object gives access to a lot of Excel related options.

 Worksheet Function : Used to access Excel inbuilt functions

 Note: instead of Application.WorksheetFunction.Average, simply use

WorksheetFunction.Average.

 Inserting a formula into cell using VBA.

34
 VBA - Application Object
 ScreenUpdating
 Sometimes you may find it useful to disable screen updating (to avoid flickering)
while executing code. As a result, your code will run faster.

35
 More...

 Active X controls  Fancy Buttons

 User Forms  custom-built dialog box that makes a user data entry
more controllable and easier to use for the user.

 Syntax to recognize Long type variable : number<no space>& eg  1&

 Syntax to recognize Double type variable : number<no space># eg 

1#

36
Questions…?
shi-fw.com