Sample VB Code... Based on a set of routines for allocating students and teachers to advocate groups.

The programme expects to find a file on driveA: called "students.csv". The data is in "comma separated values" (CSV) format consisting of 11 fields on each line

Inserting comments - You should ensure your code is well documented with comments explaining what each section of code does. Any line beginning with a single quotation mark ( ' ) will be ignored by the VB interpreter. Text on a comment he will appear green.

' ******************************************************************************************
' The Database array in which data from the file is stored:

Dim Database(1 To NumberOfStudents, 1 To 12) As String
' Database(<Records in Database>, <Fields in each record>)
' Holds the data from the file.
' Field 1 - FirstName
' Field 2 - Surname
' Field 3 - Year Group
' Field 4 - Form
' Field 5 - 11 Teachers
' Field 12 - matched/unmatched

' ******************************************************************************************
Const NumberOfStudents As Integer = 224
Const NumberOfTeachers As Integer = 200
' ******************************************************************************************

' ************* Opens disk file and reads data into the "database" array ***********
Open "a:\students.csv" For Input As #1
' Reads data from disk file into the Database
For RecordCount = 1 To NumberOfStudents
   For FieldCount = 1 To 11
      Input #1, Database(RecordCount, FieldCount)
   Next FieldCount
Next RecordCount

' NOTE: You must close the file when exiting the program with: Close#1
' ******************************************************************************************

' ******************************************************************************************
' Read student information into the array StudentList from the array Database

Dim StudentList(1 To 1000, 1 To 7) As String
' Individual student information extracted from file

For RecordCount = 1 To NumberOfStudents ' Count NumberOfStudents records
   For FieldCount = 1 To 4         ' Read student information from database
      StudentList(RecordCount, FieldCount) = database(RecordCount, FieldCount)
   Next FieldCount
Next RecordCount                     ' Count NumberOfStudents records

' ******************************************************************************************

' ******************************************************************************************
' Sort teachers by student load - lowest load at the top
' Based on code from: "Using Visual Basic 4", Published by Que, p449
' NOTE: "CInt" converts a string to an integer

Dim sortedTeacher(1 To 200, 1 To 3) As String
' Holds sorted teachers and loads.
' Fields are: Teacher Name, Load, "matched", or "unmatched"

For i = 1 To NumberOfTeachers
   For j = i To NumberOfTeachers
      If CInt(sortedTeacher(i, 2)) > CInt(sortedTeacher(j, 2)) Then
         temp(i, 1) = sortedTeacher
         temp(i, 2) = sortedTeacher(i, 2)
         temp(i, 3) = sortedTeacher(i, 3)
         sortedTeacher(i, 1) = sortedTeacher(j, 1)
         sortedTeacher(i, 2) = sortedTeacher(j, 2)
         sortedTeacher(i, 3) = sortedTeacher(j, 3)
         sortedTeacher(j, 1) = temp(i, 1)
         sortedTeacher(j, 2) = temp(i, 2)
         sortedTeacher(j, 3) = temp(i, 3)
      End If
   Next j
Next i

' ******************************************************************************************

' *********** Prints sorted list of Teachers and Loads to a Worksheet **************
' NOTE: This routine assumes the VB code is written using VBA in the Worksheet
' used to display the information

For teacherName = 1 To NumberOfTeachers ' Load first NumberOfTeachers teachers
   For teacherLoad = 1 To 2
      Worksheets("Sheet1").Range("a4:b134").Cells(teacherName, teacherLoad) = sortedTeacher(teacherName, teacherLoad)
   Next teacherLoad
Next teacherName

' ******************************************************************************************

' *************** Write "unmatched" students to a disk file  **************************

Open "a:\unmatched.txt" For Output As #5
For RecordCount = 1 To NumberOfStudents
   If database(RecordCount, 12) = "unmatched" Then
       Write #5, database(RecordCount, 2); database(RecordCount, 12)
   End If
Next RecordCount

' NOTE: You must close the file when exiting the program with: Close#5
' ******************************************************************************************

'******************************* Select Individual Teacher ******************************
' Selects an individual teacher and matches 15 students to that teacher.

Dim advocateGroup(1 To NumberOfTeachers, 1 To 15) As String
' Holds the final Advocate Group information as records consisting of:
' 1 teacher and 14 students.
' This routine demonstrates the use of "ANDing" and "concatenation" of text using "&".

oneTeacher = "Mr J FULLER"

For teacherName = 1 To NumberOfTeachers
   student = 2
   If CInt(sortedTeacher(TeacherName, 2)) > 15 Then  ' Main If condition
      For RecordCount = 1 To NumberOfStudents
         For FieldCount = 5 To 11
              If (student < 16) And (database(RecordCount, FieldCount) = oneTeacher) And (database(RecordCount, 12) = "unmatched") Then
                  advocateGroup(teacherName, 1) = sortedTeacher(teacherName, 1)
                  advocateGroup(teacherName, student) = database(RecordCount, 1) & " " & database(RecordCount, 2) & " " & "Yr" & database(RecordCount, 3) & " " & database(RecordCount, 4)
                  database(RecordCount, 12) = "matched"
                  student = student + 1
              End If
          Next FieldCount
       Next RecordCount
   End If  ' Main If condition
Next teacherName

' ******************************************************************************************

<- Back