Programming in Visual Basic

1. Programming in Visual Basic Visual Basic Applications

2. Class Objectives • Recognize when programming is required to solve a problem • Understand basic structure of a program • Understand function vs. subroutine • Know how to create a program • Know rules for naming variables • Know precedence of arithmetic and comparison operators • Understand integer vs real number

3. Class Objectives (con’t) • Understand comments • Know what to do if a line is too long • Know how to protect against spelling errors • Selection structures: If, If-Else, If-ElseIf • Repetition structures: Do Until, For

4. What is a program? Program– An algorithm implemented within a computer.

5. When do you use a program? • Spreadsheets can perform about 95% of the computing tasks; however, they are not suitable for the following: • Highly repetitive tasks • Decision making

6. What are some programming languages? • Basic • Cobal • Fortran • Pascal • Ada • Java • C • LISP We will use Visual Basic for Applications (VBA).

7. What is Visual Basic for Applications (VBA)? It is a version of Visual Basic that is embedded within Excel.

8. Why are we learning VBA, rather than another programming language? • Simple, but powerful • Easy to learn • Quickly learned • Capitalizes on your knowledge of Excel

9. Excel 0, 1, 2, 3 … numbers 1 number VBA Function VBA Interaction with Excel Excel 0, 1, 2, 3 … numbers 1, 2, 3 … numbers VBA Subroutine Our first focus

10. Sample Function Function name Identifies program as a function Numbers passed into function through arguments Function my_adder(my_a, my_b) my_adder = my_a + my_b End Function Action taken by the function Identifies the end of the function The single number is returned through the function name

11. Creating a VBA program

12. Step 1 – Open Excel

13. Step 2 – Save as… Save as … Example_1.xls

14. Step 3 – Open VBA Editor Press… Alt F11

15. Step 4 – Identify where to file your program Left-click here

16. Step 5 – Insert module Left click Insert Left click Module

17. A blank form will appear This is where the program goes

18. Step 6 – Type in program Here is the program

19. Use your program

20. Step 1 – Go to Excel spreadsheet Left click Excel button

21. Step 2 – Enter numbers into cells Enter numbers 4 & 8

22. Step 3 – Call the function = my_adder(A1,B1)

23. A B C 1 2 4 8 12 What happened? = my_adder(A1,B1) Function my_adder(my_a, my_b) my_adder = my_a + my_b End Function

24. Rules for naming variables • Must begin with letter • Use only letters, numbers, and underscores • Do not use space, period (.), exclamation mark (!), or the characters @, &, $, # • Do not exceed 255 characters in length • Avoid names already used by Excel and VBA • Start each variable with my_

25. Operator Precedence in Formulas • Parenthesis • Exponentiation (^) • 3. Negation (–) • 4. Multiplication and division (*, /) • 5. Addition and subtraction (+, –) My_answer = (5 + 7^2*3)/4 – 1 Evaluates as… My_answer = (5 + 49*3)/4 – 1 My_answer = (5 + 147)/4 – 1 My_answer = (152)/4 – 1 My_answer = 38 – 1 My_answer = 37

26. In algebra, the following formula has no solution… A = A +3 In computers, the above formula means … Take the current value of A, add 3 to it, and make the result the new value of A. Odd features of computer formulas… In algebra, either of the following two formulas has meaning… y = x +3 or x + 3 = y In computers, only the first formula is valid.

27. Integers and Real Numbers Integer – Any positive or negative counting number, or zero. Examples: 4, –7, 0 Real Number – Any rational or irrational number Examples: 0.75, 3.14159, – 0.5 Note: Rational number – can be expressed as m/n where m and n are integers and n is not zero Irrational number – cannot be expressed as m/n where m and n are integers and n is not zero

28. Declaring Variables When introducing a new variable into a computer program, you should tell the computer what kind it is. Some common options… Increasing memory requirements • Integer • Single – single-precision real number • Double – double-precision real number

29. Modify you program as follows… Declared as integers Function my_adder(my_a As Integer, my_b As Integer) Dim my_c As Integer my_c = 5 my_adder = my_a + my_b + my_c End Function What happens when you introduce real numbers into the Excel cells?

30. Function my_adder(my_a As Integer, my_b As Integer) ‘ This program adds two numbers together and adds 5 Dim my_c As Integer my_c = 5 my_adder = my_a + my_b + my_c End Function The apostrophe signals the computer to ignore what follows Comments Comments can be added to help humans understand the program. Comments are ignored by the computer. Indentation is also ignored my computer, but helps humans to understand.

31. What if a line is too long? Example… My_var = 1 + 2 + 3 + 4 + _ 5 + 6 + 7 + 8 Is evaluated as My_var = 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 The underscore joins lines together.

32. Types of Errors • Syntax errors – simple errors that do not follow the required format • Compiler errors – errors that occur when the computer program is converted to machine code • Run-time errors – a program may compile fine, but may fail during a run • Logic errors – a program may run fine, but have mistakes in logic that give incorrect answers • Spelling error – if you misspell a variable, the computer interprets it as an additional variable and likely will give incorrect answers

33. How to protect against spelling errors This statement requires that all variables be declared Option Explicit Function my_adder(my_a As Integer, my_b As Integer) 'This program adds two numbers together and adds 5 Dim my_c As Integer my_c = 5 my_adder = my_a + my_b + my_c End Function • Do the following: • Add Option Explicit to your program • Misspell my_c = 5 as my_d = 5 • Go to the Excel spreadsheet and try to use my_adder

34. Your function failed to run and you got an error message. • How to recover… • Press OK in the error message • Correct the spelling • Press F5 It is good programming practice to use Option Explicit so that spelling errors are caught.

35. 1. Equality (=) 2. Inequality (<>) 3. Less than (<) 4. Greater than (>) 5. Less than or equal to (<=) 6. Greater than or equal to (>=) Comparison Operators Evaluated in this order of precedence

36. Y ? N Calc A Selection: If Function my_comparison(my_a, my_b) my_comparison = 0 If my_a > my_b Then my_comparison = 1 End If End Function Enter the above program and test it in Excel. (Note: Option Explicit can appear only once on a sheet, so it is omitted here.)

37. Calc A Selection: If-Else Function my_comparison(my_a, my_b) If my_a > my_b Then my_comparison = 1 Else my_comparison = 2 End If End Function Y ? N Calc B Enter the above program and test it in Excel. (Note: Option Explicit can appear only once on a sheet, so it is omitted here.)

38. Calc A Calc A Calc B Selection: If-ElseIf Y ? Function my_comparison(my_a, my_b) If my_a > my_b Then my_comparison = 1 ElseIf my_a = my_b Then my_comparison = 2 Else my_comparison = 3 End If End Function N Y ? N Enter the above program and test it in Excel. (Note: Option Explicit can appear only once on a sheet, so it is omitted here.)

39. Repetition: Do Until Input Function my_do_until_loop(my_a As Integer) Dim my_i As Integer Dim my_j As Integer my_i = 1 my_j = 1 Do Until my_i = my_a my_i = my_i + 1 my_j = my_j * 2 Loop my_do_until_loop = my_j End Function Title Declare variables Initialize variables Calculate Output Enter the above program and test it in Excel.

40. my_i = 1 my_j = 1 N my_i = my_a? my_i = my_i +1 my_j = my_j *2 Repetition: Do Until As a flowchart… Function my_do_until_loop(my_a As Integer) Dim my_i As Integer Dim my_j As Integer my_i = 1 my_j = 1 Do Until my_i = my_a my_i = my_i + 1 my_j = my_j * 2 Loop my_do_until_loop = my_j End Function Y

41. my_i = 1 my_j = 1 N my_i = my_a? my_i = my_i +1 my_j = my_j *2 Repetition: For As a flowchart… Function my_for_loop(my_a As Integer) Dim my_i As Integer Dim my_j As Integer my_j = 1 For my_i = 1 To my_a my_j = my_j * 2 Next my_i my_for_loop = my_j End Function Default: Counts by +1 Y Enter the above program and test it in Excel. Note: The code is shorter, but the flowchart is the same.

42. my_i = 1? Repetition: For Function my_for_loop(my_a As Integer) Dim my_i As Integer Dim my_j As Integer my_j = 1 For my_i = my_a To 1 Step -1 my_j = my_j * 2 Next my_i my_for_loop = my_j End Function As a flowchart… my_i = my_a my_j = 1 Overrides default: Counts by -1 Y N Enter the above program and test it in Excel. my_i = my_i – 1 my_j = my_j *2