Essence of programming

1. Essence of programming • Branching • Repetitions

2. Flowchart for the MinValue function

3. Code for the MinValue function Function MinValue(n1 As Single, n2 As Single) As Single If n1 <= n2 Then MinValue = n1 Else MinValue = n2 End If End Function

4. Flowchart for the testfunction

5. Code for the testfunction Function testfunction(P1 As Single, P2 As Single) As Integer If P1 <= 6 Then testfunction = 90 ElseIf P2 > 11 Then testfunction = 30 Else testfunction = 40 End If End Function

6. Flowchart for the simple factorial function

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

8. Steps of calculating 5! Begin: factorial = 1 Step 1: factorial = 1 * 1 = 1 Step 2: factorial = 2 * 1 = 2 Step 3: factorial = 3 * 2 = 6 Step 4: factorial = 4 * 6 = 24 Step 5: factorial = 5 * 24 = 120 End

9. Flowchart for the recursive factorial function

10. Code for the recursive factorial function Function factorial(n As Integer) As Long If n > 0 Then factorial = n * factorial(n – 1) Else factorial = 1 End If End Function

11. Steps of calculation 5! (recursive) Begin Step 1: factorial(5) = 5 * factorial(4) Step 2: factorial(4) = 4 * factorial(3) Step 3: factorial(3) = 3 * factorial(2) Step 4: factorial(2) = 2 * factorial(1) Step 5: factorial(1) = 1 * factorial(0) Step 6: factorial(0) = 1 End

12. Calculating number e as the sum of a series

13. Use already defined factorial function

14. Function code Function handmadeE() As Double Dim i As Integer handmadeE = 0 For i = 0 to 10 handmadeE = handmadeE + 1/ factorial(i) Next i End Function

15. Flowchart for the fibonacci function

16. Calculating the sum of a series Calculate the sum of the first 100 elements of the series

17. Calculating the sum of a series: flowchart.Parameter P does not depend on index n; therefore, it can be put as the factor.

18. Subroutine code to print the result in the immediate window Sub calculateseries(P As Single) Dim Sum As Double Dim n As Integer Sum = 0 For n = 1 To 100 Sum = Sum + 1/(1 + n)^(1/n) Next n Sum = Sum * P debug.print “Sum = “; Sum End Sub

19. Function code to display the result on the worksheet Function calculatesrs(P As Single) As Double Dim n As Integer calculatesrs = 0 For n = 1 To 100 calculatesrs = calculatesrs + 1/(1 + n)^(1/n) Next n calculatesrs = calculatesrs * P End Function

20. Exploring Microsoft Office Visual Basic in Macros

21. VBA, Microsoft Office, Macro • VBA is the key to customize Word and Excel • Macro • A sequence of instructions executed as one • automate a procedure frequently performed • Can be invoked by a single command/action • It’s essentially a program • Can be programmed • Better yet, can be recorded…

22. Macros: • In a Labsession you have already seen how to write a subroutine (Macro) using the VBA editor. • Alternatively you can also create them with the Macro recorder. In this way you do not need to know any VBA commands. Recording a Macro: • 1) open a worksheet • 2) select Tools  Macro  Record New Macro  •  the record Macro dialog box opens up • 3) enter Macro Name, e.g. “SumA1toA30“ • - not all names are allowed, such as function names, special signs in the name as !,?, blank,... are also not possible • 4) enter a letter for the shortcut key, e.g. “s“ • 5) store the macro somewhere, e.g. “This workbook“ • 6) fill in the decription box, e.g. “sum up the cells A1:A30“ • 7) Ok  , the recording is on. Now all actions you carry out on the worksheet will be recorded and its code will be produced.

23. 8) For example: • Carry out an action which sums up the cells A1:A30 • - select a cell in your worksheet different from column A • - write: “ The sum of the cells A1:A30 is: “ • - select the adjacent cell and write: “=Sum(A1:A30)“ • - the effect of this is that in the cell in which you wrote • “=Sum(A1:A30)“ this sum will be displayed • · if a cell is empty its value contributes zero to the sum • · you can now change the content of A1:A30 and the sum • will be updated automatically • 9) - select Tools  Macro  Stop Recording  • - alternatively in the window on the worksheet • select Stop Recording  • - if that window is now invisible, you can make it appear by • selecting Edit  Toolbars  Stop Recording 

24. Record Macros • Tools Menu • Macro Command • Record Macro

25. Recording a Macro • In the Tools menu choose Macros – Record new macro • Give a name to the macro • Start recording • Do some actions • Stop recording • Edit the macro • Use the macro

26. Viewing the code: • The recording has produced a VB code, which alternatively we could have programmed by hand: • Let‘s see what we have just produced: • - Select Tools  Macro  Macros  •  a window called Macros opens up • - the window “Macro name“ shows the name of the Macro • in case you have many Macros: select Options  • to see the details of it (in case you do not remember) • Select Edit  •  the code we have just produced will show up

27. Sub SumA1toA30() ' ' SumA1toA30 Macro ' sum up the cells A1:A30 ' ' Keyboard Shortcut: Ctrl+s ' Range("F12").Select ActiveCell.FormulaR1C1 = "The sum of the cells A1:A30 is:" Range("I12").Select ActiveCell.FormulaR1C1 = "=SUM(R[-11]C[-8]:R[18]C[-8])" End Sub

28. Activating the Macro: • i) Select Tools  Macro  Macros  •  a window called Macros opens up • the macro‘s name appears in the window “Macro name:“ • · in case you have more than one, select the one you want • Select Run  •  what you have recorded before will be executed now • ii) Use the shortcut: • - our example just: Ctl + s • iii) If you were editing the code: • Select   •  a window called Macros opens up  i) • iv) Using customized buttons or other objects

29. Running a Macro • ToolsMacroRun • or use shortcut • Attach a macro to a button on the Forms toolbar : easy • right ClickAssign Macro • Attach a macro to a (command) button on the Control Toolbox toolbar : need VB editor • Call macro name from click method of the button

30. Run Macros • Tools Menu • Macro Command • Macros • Keyboard • Tool buttons

31. • Example: We calculate once more - first you have to fill in: 1A1 , 2A2, 3A3 ... 30A30 - you can do this by hand, but the faster way is to use “Series“: • - put 1 into cell A1: • - select Edit  Fill  Series  •  a window called Series opens up • - Fill in: Series:  Column • Type:  Linear • Step value: 1 • Stop value: 30 • - Ok  • - activate the Macro  The sum of the cells A1:A30 is 465

32. Macro doing repetitive steps KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP KIMEP

33. Macro code • Sub kimep200() • ' • ' kimep200 Macro • ' Macro recorded 2/2/2003 by dvm • ' • Dim counter As Integer • For counter = 1 To 200 • Selection.TypeText Text:="KIMEP " • Next counter • End Sub

34. Formatting macro code • Sub kformat() • ' • ' kformat Macro • ' Macro recorded 2/4/2003 by dvm • ' • Dim counter As Integer • For counter = 1 To 200 • Selection.MoveRight Unit:=wdCharacter, Count:=5, Extend:=wdExtend • Selection.Font.Color = wdColorRed • Selection.Font.Bold = wdToggle • Selection.Font.Italic = wdToggle • If Selection.Font.Underline = wdUnderlineNone Then • Selection.Font.Underline = wdUnderlineSingle • Else • Selection.Font.Underline = wdUnderlineNone • End If • Selection.MoveRight Unit:=wdCharacter, Count:=3 • Selection.MoveRight Unit:=wdCharacter, Count:=5, Extend:=wdExtend • Selection.Font.Color = wdColorBlue • Selection.MoveRight Unit:=wdCharacter, Count:=3 • Next counter • End Sub

35. Use Controls on a Worksheet • Two types of Controls • Forms toolbar: Simple, compatible with Excel earlier version • Control Toolbox toolbar: complex, can do everything Forms controls do, and much more (We’ll focus on this) • buttons, checkboxes, etc. • Get Forms toolbar: ViewToolbarsForms • Get Control Toolbox toolbar: ViewToolbarsControl Toolbox • Focus on Command Button in Control Toolbox • design mode

36. A control(e.g. a Command Button) as an object • A control is an object • Objects have identity, properties, methods • Identity (Name): • Is also a property (called Name), but each control’s name has to be unique on the worksheet • Each control has properties associated with it • each property has a value • e.g. Font = Arial ; Height = 24 • See the list of properties by right clicking on it (in design mode) • Each control also has methods associated with it • e.g. Click(what will happen if clicking on it), DblClick • double click on the control in design mode to see list of methods(upper right drop down list) in VB editor

37. iv) Changing the button design: • attach a better text to the button: - select the right mouse button (moving first over the button)  a variety of commands opens up: Cut, Copy, Paste,...  select Edit text   type a meaningfull text onto the button, e.g. Sum A1 to A30 • change the size of the button: - select the right mouse button (moving first over the button)  select Format Control   Alignment   Automatic size  Ok  • similarly you can change the writing direction, the text fonts, the text and button size, the margins of the button, the colour, the status of the protection, etc.

38. •You can also assign Macros to other objects: · the  symbol from the forms toolbar · a text label Aa on the forms toolbar · other symbols from the forms toolbar · a picture you have imported before such as (Select Insert  Picture  From File or Clip Art  choose a picture) · etc.