Visual Basic for Applications (VBA)

1
Visual Basic for Applications (VBA)

• VBA is a subset of full-featured Visual Basic
  adapted to the applications of Microsoft Office.
• VBA will be used throughout the course for
• creating macros
• creating custom functions
• solving problems in accounting, finance, and
  operations

2
Program units in VBA

• Variables
• Subroutines
• Functions

3
Declaration statements

• Declare ALL variables with the DIM statement
• General form
• Dim Variable1 as type1, variable2 as type2, etc.
• For example,
• Dim Name as string, Value as single
• Dim Taxes as Currency, Price as Currency
• Dim AmountDue as Currency

4
Declaration statements

• Two or more variables can be declared with the
  same Dim statement but you must include the
  variable type
• If you fail to declare a variable after the
  Option Explicit statement has been entered, an
  error occurs at Run time

5
Scope of variables

• Variable scope is important when executing
  procedures and functions Variables declared
  within procedures and functions are local and are
  reset to zero when procedure terminates.
• Variables defined at module level are known to
  all procedures and retain their value between
  events
• Module-level variables are declared right after
  the Option Explicit statement
• Static variables retain their value between
  events but are local to the event procedure
• Declared with Static keyword

6
Module-level Variables
Module-level variables
Procedure (Subroutine)
Function
Local variables
Local variables
7
Information presentation

• Elementary unit of information in computer is a
  bit (0 or 1).
• A group of 8 bits is called a byte.
• Using a byte it is possible to present 28 256
  values.
• Data types of variables exist because number of
  combinations with which to present information is
  limited.

8
Data Types

• A data type specifies the type of data that is
  assigned to a variable or constant
• Two primary types of data in VB
• numeric and string
• Numeric data can be used in arithmetic operations
• String data should not be used in arithmetic
• Numeric data types can be subdivided into
  specific types
• - currency 55,567.78 - integer 255
• - single 567.78 - long (integer) 35,455
• - double 567.78129086 - Boolean True or False

9
Data types
Numeric Data Type Range Precision Number of Bytes
Integer -32,768 to 32,767 Whole numbers 2
Long -2,147,483,648 to 2,147,483,647 Whole numbers 4
Date January 1, 100 to December 31, 9999 Not applicable 8
Boolean True or False Not applicable 2
10
Data types
Numeric Data Type Range Precision Number of Bytes
Single 1.4E-45 to 3.4E38 and -3.4E38 to -1.4e-45 Seven significant digits 4
Double 4.9E-324 to 1.8E38 and -1.8E308 to -4.9E-324 Fifteen significant digits 8
Cur-rency -922,337,203,685,477.5808 to 922,37,203,685,477.5807 4 places to right of decimal 8
11
Data types
Numeric Data Type Range Precision Number of Bytes
String (variable length) N/A N/A 10 byteslength of string
String (fixed length) N/A N/A Length of string
Variant adjusted adjusted 8

12
Integer Variables

• Store integers between -32,768 and 32,767
• Require no decimal point
• If the number is outside of the range
• ? Use the Long data type

13
Long Integer Variables

• Used to store integers between -2,147,483,648 and
  2,147,483,647
• Must be used whenever an integer number exceeds
  the range for the Integer type

14
Single Precision Variables

• Represents numbers which have both a whole and a
  fractional part
• Used to store values ranging in value from
• -3.402823E38 to -1.401298E-45 for negative
  values and from 1.401298E-45 to 3.402823E38 for
  positive values
• Can store numbers with greater precision than
  integers

15
Double-Precision Variables

• Store large floating point numbers, but require
  twice as many bytes of storage compared to type
  Single
• Stored as floating-point numbers ranging in value
  from - 1.79769313486232E308 to -
  4.94065645841247E-324 for negative values and
  from 4.94065645841247E-324 to 1.79769313486232E308
  for positive values

16
Single vs. Double

• Use Single unless the calculation requires
  greater precision
• Not only does Double take more memory to store,
  but it also requires more time to run than type
  Single

17
Boolean Variables

• Can be used whenever a variable may have one of
  only two possible values - True or False, Off or
  On, etc.
• Frequently used in statements which require a
  test of whether something is true or false.

18
String Variables

• Can hold any character, word, or phrase that the
  keyboard can produce.
• In VB there are two kinds of string variables
• Variable-length Stings in which the number of
  characters in the string is not specified in
  advance.
• Fixed-length Strings in which the number of
  characters in the string is specified in advance

19
Subroutines

• Procedures that perform certain sequences of
  actions. Described in module windows. Start with
  the key word Sub, then follows the name of the
  subroutine and the list of parameters in one
  line. The list of parameters may be empty.
  Terminated by End Sub statement.
• Sub Name(parameters)
• Statements
• End Sub

20
Functions

• Procedures that perform certain sequences of
  actions and return some value as a result.
  Described in module windows. Start with the key
  word Function, then follows the name of the
  function, the list of parameters, and the type of
  returned value in one line. The list of
  parameters may be empty. Terminated by End
  Function statement.
• Function Name(parameters) As Datatype
• Statements
• End Function

21
Use of subroutines and functions

• Both the subroutines and functions are described
  in the module windows.
• They are separated with straight lines.
• No statements can appear between subroutines and
  functions except comments.
• If they are in the ThisDocument module of a Word
  document, they are available in the current
  document and copied together with the current
  document.
• If they are in a separate module, they are
  available to all documents on the current
  computer, but are not copied together with the
  document.
• Excel has modules associated with particular
  sheets.

22
Use of subroutines and functions

• Both functions and subroutines can be called and
  debugged in the Immediate window.
• They can be called inside other functions and
  subroutines.
• A function is not just called. Its return value
  is used in some expression.
• A function can be used on a worksheet.
• A subroutine can represent a macro.

23
Arithmetic Operators

• () for grouping
• for exponentiation
• - for negation
• for multiplication
• / for division
• \ for integer division
• mod for modulus
• for addition
• - for subtraction

24
Hierarchy of operators

• Operations within parentheses ( )
• Exponentiation
• Negation -
• Multiplication and division ,/
• Integer division \
• Modulo arithmetic Mod
• Addition and subtraction ,-
• String concatenation

25
Arithmetic example

• 6 (Salary - Taxes)2 Bonus/Months
• 3 1 2 5
  4 (order)
• Order
• 1 Subtract Taxes from Salary
• 2 Square the result
• 3 Multiply this result by 6
• 4 Divide Bonus by Months
• 5 Add result from first expression

26
Comments

• Use comments to explain the purpose of a
  statement
• Any statement beginning with an apostrophe or REM
  (remark) is a comment
• Comments can be added to end of statements using
  apostrophe
• Example
• MonRate YrRate / 12 convert the yearly
  interest to a monthly rate

27
Formatting data

• To display information in an attractive form, we
  can use the Format function
• variable or control Format(variable, format
  expression)
• Where the format expressions are in quotes and
  include (see the next slide for an exact
  description of these formats)
• Currency - Standard
• Fixed - Scientific
• Percent
• Example
• txtTaxes.text Format(Taxes, currency)

28
Numeric Format Expressions
Format Expression Result
Currency Display number with dollar sign, thousands separator, and two digits to the right of the decimal point.
Fixed Display number with at least one digit to the left and two digits to the right of the decimal point.
Standard Display number with thousands separator and at least one digit to the left and two digits to the right of the decimal point.
Percent Display number multiplied by 100 with a percent sign () on the right and two digits to the right of the decimal point.
Scientific Use standard scientific notation.
29
Other arithmetic functions

• Other useful functions include
• Abs for absolute value Sqr for square root
• FV for future value PV for present value
• IRR for internal rate of return Pmt for payment
• Ucase/Lcase to convert to upper/lower case
• Len for length of a string
• Date for the system date
• DateValue for the date corresponding to string
  argument
• We will use Pmt to compute the monthly payment
• MonPay Pmt(rate, Nper,-LoanAmt)
• Pmt(.08/12,60, -10000) 201.42

30
The Selection Process

• One of the key operations of a computer is to
  select between two or more alternatives to make a
  decision.
• Every decision involves a comparison between a
  variable and a constant, variable, or expression
  using logical operators.
• Common logical operators AND, OR, NOT, XOR
• Decisions can involve two-alternatives or
  multiple alternatives.

31
The If-Then-Else Decision Structure

• For two alternative decisions, the If-Then-Else
  decision structure should be used
• In pseudocode, this is
• If condition is true then
• implement true alternative
• Else
• implement false alternative
• End Decision

32
Multiple Alternatives

• For multiple alternatives, the general form in
  pseudocode is
• Select one
• Condition 1 is true implement alternative 1
• Condition 2 is true implement alternative 2
• Condition 3 is true implement alternative 3
• End Selection.

33
The Two Alternative Decision Structure

• The If-Then-Else statement (Block version)
• If condition is true Then
• statements for true alternative
• Else
• statements for false alternative
• End if
• (Inline version)
• If condition is true Then statements for
  true alternative Else statements for false
  alternative
• The If-Then condition
• test expression1 comparison operator test
  expression2
• where comparison operator is one of these six
  operators
• Equal to Not equal
  to ltgt
• Greater than gt Less then lt
• Greater than or equal to gt Less than or equal
  to lt

34
Example of If-Then-ElseIf for Letter Grade
Determination

• Dim Average as Integer, LetterGrade as string
• Average CInt(txtAverage.text)
• If Average gt 90 then
• LetterGrade A
• ElseIf Average gt 80 then
• LetterGrade B
• ElseIf Average gt 70 then
• LetterGrade C
• ElseIf Average gt 60 then
• LetterGrade D
• Else
• LetterGrade F
• End If
• txtLetter.Text LetterGrade

35
Example of Select Case to Determine Letter Grade
Dim Average as Integer, LetterGrade as
String Average CInt(txtAverage.text) Select
Case Average Case Is gt 90 LetterGrade
A Case Is gt 80 LetterGrade B Case
Is gt 70 LetterGrade C Case Is gt 60
LetterGrade D Case Else LetterGrade
F End Select
36
Case Conditions

• Conditions for Case statement can be in 3 forms
• Test Condition Example
• Value or expression Case 91, 92, 93
• Range of values Case 90 to 100
• Comparison condition Case Is gt 89

37
The Repetition Process

• The capability to repeat one or more statements
  as many times as necessary is what really sets a
  computer apart from other devices
• All loops have two parts
• the body of the loops (the statements being
  repeated)
• a termination condition that terminates the loop
• Failure to have a valid termination condition can
  lead to an endless loop

38
Types of Loops

• There are three types of loops
• event-driven
• determinate
• indeterminate
• Event-driven loops are repeated by the user
  causing an event to occur
• Determinate loops repeat a known number of times
• Indeterminate loops repeat an unknown number of
  times

39
Looping statements

• For I 1 To 100 Step 0.5
• .
• Next I
• Do Until intDone True
• .
• Loop
• While intDone False
• Wend

40
Determinate Loops Using For-next statement

• Best way to create a determinate loop is to use a
  For-Next Loop
• statement
• For variable start value to end value Step
  change value
• statements that compose body of loop
• Next variable
• where variable the counter variable in the loop
• start value the beginning value of the
  counter variable
• end value the ending value of the counter
  variable
• change value the amount the counter
  variable changes each time through the loop
• Next variable the end of the For loop

41
Example of For-Next Loop
42
Indeterminate Loops

• Indeterminate loops run for an unknown number of
  repetitions until a condition is true or while a
  condition is true
• Four types of indeterminate loops
• Until loop with termination condition before body
  of loop
• While loop with termination condition before body
  of loop
• Until loop with termination condition after body
  of loop
• While loop with termination condition after body
  of loop
• Pre-Test loops have termination condition before
  loop body
• Post-test loops have termination condition after
  loop body

43
Form of Pre- and Post-Test Loops

• The form of the pre-test loops is
• Do Until (or While) condition
• body of loop
• Loop
• The form of the post-test loops is
• Do
• body of loop
• Loop Until (or While) condition

44
Pre-Test Indeterminate Loops
45
Post-Test Indeterminate Loops
Do Loop While Do Loop Until Do Do statement1
statement1 statement2
statement2 etc etc while
condition until condition next statement
next statement
46
Pre and Post-Test Indeterminate Loops

• The four types of indeterminate loops
• Do while/until condition
• Loop
• Do
• While/until condition
• Are equivalent. Choose the one that makes the
  most sense for the application.

47
Nested Loops

• A Nested loop is a loop within a loop. Must
  complete the inner loop within the outer loop.
• Nested For-Next loops have a For-Next loop within
  a For-Next loop in which the inner loop will go
  through all its values for each value of the
  outer loop.
• Three key programming rules to remember about
  using nested For-Next loops
• Always use different counter variables for the
  outer and inner For-Next loops.
• Always have the Next statement for the inner
  For-Next loop before the Next stateent for the
  outer For-Next loop.
• Always include the counter variable in the Next
  statements to distinguish between the loops.

48
Debugging Loops

• Debug a loop by inserting a debug.print command
  in the loop to print to the Immediate Window.
• Add a Quick Watch by locating the pointer on a
  variable and clicking the eyeglass icon on the
  Debug Toolbar. The values for this variable will
  be shown in the Watch Window.
• Use the Locals window to display the values of
  variables local to a procedure.
• Use the Toggle Breakpoint icon to pause execution
  at a designated line in the code and then use the
  various windows to view the values for variables.

49
Using Debugger
50
Flowchart for the MinValue function
51
Code for the MinValue function
Function MinValue(n1 As Single, n2 As Single) As
Single If n1 lt n2 Then MinValue
n1 Else MinValue n2 End If End Function
52
Flowchart for the testfunction
53
Code for the testfunction
Function testfunction(P1 As Single, P2 As Single)
As Integer If P1 lt 6 Then testfunction
90 ElseIf P2 gt 11 Then testfunction
30 Else testfunction 40 End If End Function
54
Flowchart for the simple factorial function
55
Code for the simple factorial function
Function factorial(n As Integer) As Long Dim i
As Integer factorial 1 For i 1 to
n factorial factorial i Next i End Function
56
Flowchart for the recursive factorial function
57
Code for the recursive factorial function
Function factorial(n As Integer) As Long If n gt
0 Then factorial n factorial(n
1) Else factorial 1 End If End Function