Excel 2002 Vba Form

							  Online Scoring System
& Reference Table Updating
   System for PE of NCU

  Presented by Chung-lien Tseng
        Date: 10/28, 2004
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   2
                     Introduction
 It was a one-year project for MIS students of NCU
 Objectives
     Provide an online system (for equipment & place
      management) for PE office
     Facilitate the process of scoring
   Target users
     Teachers of PE
     Students
     Administer of the system
   My job-Online Scoring System

                         Online Scoring System          3
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   4
       Requirement Discovery
 Teachers     have to calculate the average
  score and then fill in the official form
 Past grades can‟t be saved and checked
 Some test items of PE don‟t have real
  scores, such as triple jump, running
 The reference table of converting test
  grades into scores of percentage is
  difficult to be updated, and the data is old
                   Online Scoring System         5
       Requirement Analysis
 Tomaintain the reference table and take the
 ability of current students into account
  Reference Table Updating System
 Tofacilitate the process of scoring and keep
 the past student grades on file
  Online Scoring System




                 Online Scoring System       6
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   7
        Evaluation of Alternatives
 Use Internet Programming Language
 For Reference Updating System
     Internet Programming Language
         ASP
         ASP.NET

   For Online Scoring System
     Excel with VBA
     Internet Programming Language (ex: ASP, ASP.NET,
      script languages)

                          Online Scoring System          8
      Why Did I Choose Excel
 Most   of PE teachers are normal users, so
  the system must be easy to use
 The scoring system needs lots of fields to be
  filled in grades, and some fields need to be
  frozen
 The scoring system needs a mechanism to
  transform grades of test into percentage
  scores automatically
                  Online Scoring System       9
 Why Did I Choose Excel (Cont‟d))
                  Excel with VBA            ASP, Script
                                            languages…
Easy to learn     Almost all PE             With good
and use           teachers can              interface, it‟ll be
                  use Excel                 easy to use
Need many       Excel interface             Insufficient
writable fields is composed of              knowledge to
                table                       do that
Need to         Can use Excel‟s             Large volume in-
transform score formula function            time
                                            transformation
automatically   to achieve
                    Online Scoring System
                                            takes lots of         10
                                            system resource
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   11
             VBA Introduction
 VBA   stands for Visual Basic Application
  Similar to VB
  Built in every AP of Microsoft Office (Word,
   Excel…)
 What‟s   different with macro?
  A VBA project may contains several macros
  Macro: a public sub-process with no parameter
  Macro can be created by either “macro recorder”
   or “Visual Basic Editor”
                    Online Scoring System         12
         Pros and Cons of VBA
 Pros
  Facilitate the use of Microsoft Office
    Record (compile) a repetitive job as macro, so that
     we only have to load the macro every time we need
     to do that job
 Cons
  Macro may be a malicious program
    To avoid being attacked, we can set the security
     level to “High” or “Medium”
         Digital Signature

                         Online Scoring System             13
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   14
                     ER Model
                  teachclass               course
 teacher         teach
(account)
                                            include
                 score
        enroll
                           testitem                      reftable
                                                 link
 student
                                                          generate
                               contain
      take
                                                        refmeta
                     test
                         Online Scoring System                       15
                  Database Design
   Online Score System
     account
         teacherName, id, password
     teachclass
         teacherName, courseName
     course
         courseName, courseId, class
     student
         studentNo, studentName, studentGender, studentDept, studentGrade
     score
         studentNo, courseName, score
     testitem
         courseName, itemNo, item, itemPercent
     test
         courseName, studentNo, itemNo, testScore




                               Online Scoring System                         16
     Database Design (Cont‟d)
 Reference    Table Updating System
  testitem
    courseName, itemNo, item, itemPercent
  reftable
    item, result, gender, refScore
  refmeta
    item, gender, measure, refSize, refMean, refStd,
      refSizeStd2, refSizeMean2, refSizeMean

                      Online Scoring System             17
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   18
    Reference Table Algorithm
 Based on past data
 Average grades of current student have
  most of percentage of new reference table
 Use the original algorithm of old system to
  generate new reference table



                  Online Scoring System         19
Reference Table Algorithm (Cont‟d)
   Algorithm
     Input of algorithm: mean( X ), std (S) of all grades
     To calculate joint X and S of old and new data, we
      need  n ,  n X , n X and  ni S i2 for
                                        2
                i     i   i     i       i



                  newX   ni X i
                                   ni

                              n X                ni S i
                                            2            2
                                                                            2
                    newS                   i                      new X
                                                             n
                                    i

                                                              i


     i=I means the old reference table data

                              Online Scoring System                             20
Reference Table Algorithm (Cont‟d)
  Range of real score: 40~100 (40 is the lowest score of
   PE in NCU)
  Slope: relation between test grades and scores
     >0triple jump; <0running
  Set Upper Bound (UB): a range be deducting later
                 UB= 4* S (UB= X if UB> X )
  st is a decided parameter for every score range
     If the slope>0 between test grades and scores, i.e. direction
      ratio, st decreases when score increases; otherwise, st
      increases when score increases

                         Online Scoring System                        21
    Reference Table Algorithm (Cont‟d)
                                        elseif i>85 and i<89 then
   Example                                      st=0.1 * std
       slope>0, i=score                elseif i>88 and i<92 then
                                                 st=0.095 * std
for i=41 to 100                         lseif i>91 and i<95 then
     if i>40 and i<51 then                       st=0.09 * std
              st=0.135 * std            elseif i>94 and i<98 then
     elseif i>50 and i<61 then                   st=0.085 * std
              st=0.13 * std             elseif i>97 and i<101 then
     elseif i>60 and i<66 then                   st=0.08 * std
              st=0.125 * std            end if
     elseif i>65 and i<71 then                              test
              st=0.12 * std             ub=ub-st
                                                                         Triple
     elseif i>70 and i<76 then          score(i)=mean-ub
                                                            grade        jump     score(i) curve
              st=0.115 * std
     elseif i>75 and i<81then    next                        4.6m                       mean
              st=0.11 * std                                                             grade
     elseif i>80 and i<86then
              st=0.105 * std                                         0                   score
                                                                           65
                                                                                  ub curve

                                    Online Scoring System                                   22
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   23
                System Flowchart
 Online    Scoring System
                   Choose course &                  Add or delete items
  Log in
                   Scoring mode
                                                                Save
                                modify
             reset
                                                                  Print
                                     Enter Excel &
                                     start scoring               Leave
           Set test items
           and %
                                                             Leave & scoring
                                                             another course
                            Online Scoring System            (re-log in)       24
                       Problems
1.   How to transform test grade into score immediately?
2.   How to tell whether a test item needs the reference table?
3.   How to renew the scores if relative reference table has
     been updates?
4.   How to control or jump to a given field of column?
5.   How to transmit data (ex: teacher‟s name, course name,
     etc.) from webpage to Excel?
6.   Where to put the Excel program (at server or client)?




                         Online Scoring System                25
                 Solution 1
    How to transform test grade into score
     immediately?
1.   Save reference tables in database
2.   Load required reference tables at the
     beginning and when needed
3.   Use Excel‟s built-in “VLOOKUP()” formula
     to convert test grade into score

                   Online Scoring System        26
                 Solution 2
    How to tell whether a test item needs
     the reference table?
1.   Use Combo Box to list all referenced test
     items when choosing test items
2.   If the listed item is selected
     link to relative reference table
3.   If the teacher types test item himself
      a normal test item
                   Online Scoring System         27
                   Solution 3
    How to renew the scores if relative reference
     table has been updates?
1.   Only record the original test grades when saving
     file
2.   Auto-transform into percentage scores on
     scoring (i.e. open Excel)
3.   Scores can‟t be renew immediately if administer
     updates the reference while someone is scoring

                     Online Scoring System          28
                 Solution 4
    How to control or jump to a given field
     of column?
1.   Naming some special fields and every
     column
2.   A column is named after item number or
     data type (stdno, gender…) it is filled
3.   Use Excel‟s „Range(“name”)‟ function to
     control whole column
                   Online Scoring System       29
                         Solution 5
        How to transmit data (ex: teacher’s name,
         course name, etc.) from webpage to Excel?
1.       Record information needed into database while
         interface is changing from webpage to Excel
     •     Add a new table “scoreLog” with fields “ipAddress”,
           “teacherName”, “courseName”, “status”…
2.       After opening Excel, running “ipconfig” command
         by Excel function to get IP address
3.       According the IP of the teacher, retrieve required
         information from database
                           Online Scoring System                 30
                       Solution 6
     Where to put the Excel program (at server or
      client)?
1.    The Excel program is saved at server
     1.   Easy to maintain
     2.   Prevent Excel program from being destroyed
     3.   More mobility
2.    Side effect
          “Read only” dialog shows when 2 teachers score at
          the same time
                          Online Scoring System               31
                 Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                 Online Scoring System   32
  Reference Table Updating System

                 Choose update
                 mode (add item         Choose items to be
Log in           or update)             updated
                                                    just
                      add new
                                                    update
                      item
                                           Preview updated
         Set new item                      reference table
         information (ex: name,                                    Update
         measure unit, slope)                                      reference table


    Note:    This system is available only with 2 weeks after school opens.

                                  Online Scoring System                          33
Reference Table Updating System
            (Cont‟d)




            Online Scoring System   34
Reference Table Updating System
            (Cont‟d)




           Online Scoring System   35
Reference Table Updating System
            (Cont‟d)




            Online Scoring System   36
         Online Scoring System
                 Choose course &                  Add or delete items
Log in
                 Scoring mode
                                                              Save
                            modify
           reset
                                                                Print
                                   Enter Excel &
                                   start scoring               Leave
         Set test items
         and %
                                                           Leave & scoring
                                                           another course
                                                           (re-log in)

                          Online Scoring System                              37
Online Scoring System (Cont‟d)




           Online Scoring System   38
Online Scoring System (Cont‟d)




           Online Scoring System   39
Online Scoring System (Cont‟d)




           Online Scoring System   40
Online Scoring System (Cont‟d)




           Online Scoring System   41
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   42
       Limitation and Constraints
   System
     Online Scoring System can only be run in Excel 2002
      and above
     Insufficient security for student data and scores
     The VBA won‟t work if user doesn‟t agree it to run—
      need trusted digital signature
     The transfer process between webpage and Excel is
      rough
     Data of reference table are not abundant enough


                        Online Scoring System               43
     Limitation and Constraints
              (Cont‟d)
 External   Environment
  Can‟t integrate with system of Office of
   Academic Affairs
  Communication with end user is not enough
  Immature programming technique




                    Online Scoring System      44
                   Outline
 Introduction
 Requirement Analysis
 Evaluation of Alternatives
 VBA Introduction
 Database Design
 Algorithm of Reference Table
 Problems and Solutions
 System Structure and System Display
 Limitations and Constraints
 Conclusion
                   Online Scoring System   45
                 Conclusion
 Itan unfinished project
 In the future
   Move all scoring system on the Internet
   Emphasize on the security of system and
    database
   Enhance system performance




                    Online Scoring System     46
Wait a minute
  … it‟s present time!!