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

10. Data types

11. Data types

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

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: <> Greater than: > Less then: < Greater than or equal to: >= Less than or equal to: <=

34. Example of If-Then-ElseIf for Letter Grade Determination Dim Average as Integer, LetterGrade as string Average = CInt(txtAverage.text) If Average >= 90 then LetterGrade = “A” ElseIf Average >= 80 then LetterGrade = “B” ElseIf Average >= 70 then LetterGrade = “C” ElseIf Average >= 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 >= 90 LetterGrade = “A” Case Is >= 80 LetterGrade = “B” Case Is >= 70 LetterGrade = “C” Case Is >= 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 > 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