Transcript Document

Essence of programming
Branching
Repetitions
Flowchart for the MinValue function
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
Flowchart for the testfunction
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
Flowchart for the simple factorial function
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
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
Flowchart for the recursive factorial function
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
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
Calculating number e as the sum of a series

1
e
i
!
i 0
Use already defined factorial function
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
Flowchart for the fibonacci function
Calculating the sum of a series
Calculate the sum of the first 100 elements of the series
Sn 
P
(1  n)
1
n
Calculating the sum of a series: flowchart.
Parameter P does not depend on index n;
therefore, it can be put as the factor.
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
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
Exploring Microsoft Office
Visual Basic in Macros
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…
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.
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 
Record Macros
• Tools Menu
• Macro Command
– Record Macro
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
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
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
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
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
Run Macros
 Tools Menu
 Macro Command
 Macros
 Keyboard
 Tool buttons
• 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
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
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
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
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
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
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.
• 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.