Chapter 9: Writing Your Own Functions and Procedures Chapter 10: Organizing Information Via Code Int

1
Chapter 9 Writing Your Own Functions and
Procedures Chapter 10 Organizing Information
Via CodeIntroduction to Database Management
Using VB
2
Two Kinds of General Procedures

• Function procedures and Sub procedures perform a
  specific task
• Functions returns a value to the program that
  called it Sub procedures may or may not return a
  value
• Can be invoked anywhere in the program.
• Advantages
• Break down large tasks into smaller ones
• Automate repeated operations
• Names a piece of code

3
Functions

• Built-in Functions
• User-Defined Function or Function Procedures
• Public Function functioname (arguments) As type
• function statements
• End Function
• To create, open the Code Window and choose Tools,
  Add Procedure or enter the entire function in
  the Code Window

4
Function Example

• Public Function Avg(firstno, secondno)
• Avg (firstno secondno) / 2
• End Function
• Create 2 textboxes - txtNo1, txtNo2
• Private Sub cmdShow_Click()
• Dim newAvg, anotherAvg As Single
• newAvg Avg(Val(txtNo1.Text),
  Val(txtNo2.Text))
• MsgBox "The average of " txtNo1.Text "
  and " txtNo2.Text " is " newAvg
• 'More lines of code...
• anotherAvg Avg(2, 4)
• Print "The average of 2 and 4 is"
  anotherAvg
• End Sub

5
Sub Procedures

• A way to run a procedure from other parts of a
  program
• Call statement
• Call procedurename (arguments)
• - or -
• procedurename arguments
• Recommend including the Call keyword in all of
  your procedure calls

6
Calling A Procedure - Example

• Sub CalcRectangle (Width, Height, Area)
  'place in module
• Area Width Height
• End Sub
• Private Sub CmdCalculate_Click()
  'create a cmd button
• Dim Wid, Hgt, A, Area As Single
• Wid InputBox("Width?")
• Hgt InputBox("Height?")
• Call CalcRectangle(Wid, Hgt, A) 'call a
  procedure by ref
• MsgBox "Area from First Call " A
• CalcRectangle 6, 3, Area 'another way to
  call a procedure
• MsgBox "Area from Second Call " Area
• End Sub

7
Passing Parameters By Reference vs. By Value

• Private Sub CmdShow_Click()
• Dim num As Integer
• num 1
• Print "The original no. is " num
• Call hello 'By Reference
• 'Call hello(num) 'By Value
• Print "The no. returned from the procedure is "
  num
• End Sub
• Public Sub hello() 'By Reference
• 'Public Sub hello(num) 'By Value
• num 2
• End Sub

8
One-Dimensional Variable Arrays

• Variable Array group of variables all referred
  to by one name (all have the same data type)
• x1, x2, x3, x4, x5, ... x(1), x(2), x(3),
  x(4), x(5),...
• Each individual variable is called an element of
  the x array also called subscripted variables

9
Advantages of Arrays

• Avoids having a unique name for every variable in
  the program
• temp1, temp2,..., temp30 -vs.- temp(1),
  temp(2), ..., temp(30)
• Using unsubscripted variables (no array)
• sum temp1 temp2 temp3 temp4 temp 5
  ... temp 10
• Avg Sum / 30
• Using subscripted variables (array)
• For i 1 to 30
• sum sum temp(i)
• Next i
• Avg Sum / 30

10
Dim and ReDim

• Dim - allocates storage space for a fixed number
  of elements ("fixed array")
• Dim temp(30)
• ReDim - allows one to resize the array on the fly
  ("dynamic array")
• ReDim temp(31)
• To preserve the contents of the array, use
• ReDim Preserve temp(31)

11
Numbering of Subscripts

• Dim days(6) As String
• The days array contains 7 elements
• day(0), day(1), day(2), day(3), day(4),
  day(5),day (6)
• To match the number of elements in an array with
  its subscripts use
• Option Base 1 'Use at module level
• -or-
• Dim day(1 to 7) As String
• Use LBound and UBound to determine the lower and
  upper bounds of the array

12
Example

• Sub cmdShow_Click()
• Dim days (1 to 7) as String
• Dim index as Integer
• days(1) "Sunday"
• days(2) "Monday"
• days(3) "Tuesday"
• days(4) "Wednesday"
• days(5) "Thursday"
• days(6) "Friday"
• days(7) "Saturday"

Print Print "The days of the week
are" Print For index 1 to 7 Print days
(index) Next index End Sub
13
Multi-Dimensional Arrays

• Useful when two or more array values are required
  to determine a value.

14
Example Multiplication Table
Sub cmdShow_Click() Dim table(1 To 5, 1 To 5) As
Integer Print , 'Skips a tab
For i 1 To 5 'Top row value Print
i, Next i Print For row 1 To 5
Print row, 'First Column value
For col 1 To 5 table(row, col)
row col Print table(row, col),
Next col Print Next row End Sub
15
Midterm Exam

• Chapters 1-10
• 50 questions - True/False, Multiple choice
• Closed book
• Maximum - 2 hours

16
Introduction to Database Management in VB

• Field basic data elements within the record
• Record collection of data pertaining to one
  entity
• Table entire collection of data about a certain
  subject
• Database entire collection of data

17
Design of Relational Database

• Relational database database consists of a
  collection of tables (row record, column
  field) Advantage - join databases to come up
  with new information
• Primary key field used to ensure that the
  record is unique (e.g., Employee ID number)

18
Building a Database Using Access

• Create the file mydata.mdb
• Create a new table with the fields
• Name, Address, Salary
• Specify table name Rolodex
• Populate the table

19
Data Control

• Used to create applications that access
  information from existing databases
• Passes the data contained in fields of the
  database to bound controls (text boxes, list
  boxes, etc.) in the VB application -- to display
  or change the values

20
Accessing Your Database via VB

• Add labels and text boxes (bound controls) used
  to display the data on the form.
• Add the data control to a form and set its
  properties to indicate the database file and
  table from which you want to get information.
• Set properties of the bound controls to indicate
  the data source (e.g. Data1) and data field to be
  displayed.

21
Example

• Add 3 sets of labels and text boxes for
  Name, Address, Salary
• Add Data control set properties
• DatabaseName (filename) c\mydata.mdb
• RecordSource (table) Rolodex
• For each text box, set these properties
• DataSource (which data control?) Data1
• DataField (field name) Name, Address, Salary
  (for each text box)