INFS630639

1
INFS630/639

• Fall 2004

2
Overview

• Files
• Reading and Writing
• EOF function
• Error handling
• On Error
• Debugging
• Breakpoints
• Programmer Defined Data Types
• Arrays of Structures

3
Short ReviewProcedures vs Functions

• What is main difference between procedures and
  functions?
• What is a standard module?
• What is a class module?
• What does a Static declaration do?
• What is scope?

4
File Input and Output

• Files can be used to store specific data or to
  read specific data
• File Types
• Sequential files data can only be accessed
  sequentially from first record to next (e.g song
  on a cassette tape)
• Random files data can be accessed randomly
  reading a particular record (e.g. song on a CD)
  or sequentially.

5
File Input and Output

• Basic Format
• OPEN pathname FOR mode ACCESS access lock AS
  filenumber LEN reclength
• Pathname is name of file may include path If
  path not included program looks in directory in
  which it is running.
• Mode is one of the following
• Append adds new date to end of file
• Input sequential file read only
• Output sequential file write only overwrites
• Binary random access file uses byte-by-byte
  access
• Random random access file uses record access
  reclength

6
File Input and Output

• Basic Format
• OPEN pathname FOR mode ACCESS access lock AS
  filenumber LEN reclength
• ACCESS (optional parameter) describes what can
  be done with a file (redundant for sequential
  files could be useful for RANDOM)
• LOCK (optional parameter) used in multiuser
  situations (Shared, Lock Read, Lock Write, Lock
  Read Write)
• FILENUMBER required parameter representing the
  file number or I/O channel for the reading from /
  writing to a file
• Reclength (optional parameter) tells the
  computer how big each record is (random file) or
  how much of a sequential file to read at one time

7
Commands to Read and Write

• Reading Sequential Files
• Input reads specified number of characters
• Input - reads data separated by comma into
  variables
• Line Input complete line of data (to end of
  line a carriage return (vbCrLf)
• Writing Sequential Files
• Write - Writes contents of variables, separated
  by commas and enclosed in quotes includes vbCrLf
• Print - Writes contents of variables, separated
  by TABs and enclosed in quotes includes vbCrLf
• Can remove vbCrLf by ending statement with
  semicolon

8
Commands to Read and Write

• Can remove vbCrLf by ending statement with
  semicolon

W/O Semicolon For I 1 to 5 Write 1, I Next
I 1 2 3 4 5
W Semicolon For I 1 to 5 Write 1, I Next
I 1, 2, 3, 4, 5,
Must use combination To keep from having Training
commas See page 829 in PP book
9
Commands to Read and Write

• EOF
• EOF (end of file) is a boolean function that
  returns TRUE if the Operating System (OS) is at
  end of the file There is no more data.
• DO UNTIL EOF (1) will perform the loop until
  there is no more data in the file
• Close
• CLOSE commands the OS to close the file. If
  sequential file, it places an EOF character in
  the file.
• CLOSE 1 will close the I/O channel associated
  with file number 1
• CLOSE closes all files.

10
Error Handling

• Programs contain three main types of errors
• Syntax spelling errors Option Explicit removes
  these types of errors
• Runtime environment errors e.g.cant find the
  file or printer usually hardware related
• Logic program runs but does not give correct
  answer usually based on math or formatting
  errors.

11
Error Handling

• VBA has some other Error Handling techniques
• ON ERROR statements
• The statements are executed whenever the compiler
  throws an error
• Must predict the possible errors (PP p.284)
• Disadvantages
• The ON ERROR recovers from the error
• The error must be handled
• If you did not allow for this incorrect answer,
  then error is not fixed. Could create hard to
  find logic error

12
Debugging

• The VBA IDE has a powerful debugging environment.
  When syntax or runtime error found, program goes
  into BREAK mode and shows code
• Breakpoints stops the code at this point so
  that the programmer can inspect the actual values
  in variables. Create by clicking in left margin
  of code window (create red circle)

13
Debugging

• Debug statements put the value of the variables
  in the Immediate Window
• Debug.Print intA will display the value of intA
  in the Immediate window for the programmer to see
• Watches identify what variables to track at all
  times (the debug only shows the value at that
  time)
• The Locals Window shows all local variables
• MsgBox can be used for debugging also

14
User Types

• Programmers can create their own variable types
• One such variable type is a structure
• How does one reference a portion of the structure?

Type emprecord dim name as string dim id as
integer dim rate as single End type Dim newemp
as emprecord
Name ID Rate
15
Arrays of Structures

• Why create user types (structures)?
• Remember that each element must be of the same
  type (e.g. integer, string, etc.)
• So, how do your store both the name and id number
  of 20 employees in an array?
• Use the Data structure

16
Arrays of structures
Name id
Type emp dim name as string dim id as
integer End Type Dim emp_array (5) as emp Dim
ind_emp as integer
Bob 24 James
14 Becky 35 Bobbie
45 Zachery 50 Aaron 73
0 1 2 3 4 5
How do you refer to Bobs name? Where is Bobbies
id? What value is in emp_array(4).id Write the
code to go through the array and find Zacherys
id!
17
Summary

• VBA can save data to an read data from files
• The commands can be variable or line based
• Programmer can control access mode
• Know difference between Input and Line Input
• VBA has some good built in error handling
• ON ERROR command
• MsgBox still works pretty well
• Understand how breakpoints work
• Programmers can create their own data types
  called structures. Arrays of structures can allow
  multiple primary data types (e.g. integer,
  string) to be stored together.