Essence of programming

1 / 38

# Essence of programming - PowerPoint PPT Presentation

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 &lt;= n2 Then MinValue = n1 Else MinValue = n2 End If End Function. Flowchart for the testfunction.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about 'Essence of programming' - brook

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Essence of programming
• Branching
• Repetitions
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

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

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

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

Function code

Dim i As Integer

For i = 0 to 10

Next i

End Function

Calculating the sum of a series

Calculate the sum of the first 100 elements of the series

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