big bubbles

[chad.young]

Good morning,

Someone recently requested a form with bigger bubbles.  Kaisa just created one to use for her students with disabilities.  I've uploaded it to the Forms page at www.formscanner.org.  It is an excel spreadsheet, which you can print and use.  I've also uploaded the PPT file with the bigger bubbles.  It is called BUBBLEFORM_BIG.PPTX 

Let me know if you have any problems.

Thanks,
Chad

[Vel Suminguit]

Hi Chad, 

 

1.     I am teaching an Introduction to Anthropology course with 4 sections, with each section having upto 50 students.  My midterm exams and final exams usually have 120 items (see attached MsWord bubble sheet).  After the exams, answer sheets are scanned according to the ORDER they are submitted by the students, which may not be in alphabetical order.  My scanner generates a filename sequentially, e.g., Section1_01, Section1_02, etc.  Since FormScanner does not recognize handwriting, the only identifier to whom the test score belongs is the ID number.

 

I made changes to the VB Macro Script of the latest  ProcessBubbles 2.1 to meet my need:

 

1. I deleted “E” and “Others” in the “Answer Distribution” section because my answer sheet only has A, B, C, D choices

 

2. I added Sheet1.Cells(1, 1) = “IMAGE NAME” to serve as heading for column 1

3. To generate Q1 to Q120 labels, I changed the value from 100 to 120 in the following statement:

    For i=1 To 120

     Sheet1.Cells(1, i + 3) = "Q" & i

4. I inserted a statement:

            For d=1 To 10

            Sheet1.Cells(1, d + 123) = “ID” & d to generate ID1...ID10 labels for the ten-digit ID number used in my university.

5. I recorded a macro concatenating the value from ID1…ID10 and put the combined value in cell B3 all the way to B195.  However, this macro is only good if I always have 195 items.

 

********************************************

Sub IDCapture()

'

' IDCapture Macro

' To combine ID numbers from D1 to D10 into one

'

' Keyboard Shortcut: Ctrl+d

'

    ActiveCell.FormulaR1C1 = _

        "=RC[122]&RC[123]&RC[124]&RC[125]&RC[126]&RC[127]&RC[128]&RC[129]&RC[130]&RC[131]"

    Range("B3").Select

    Selection.Copy

    Range("B4:B195").Select

    ActiveSheet.Paste

    Sheets("Sheet2").Select

    Range("C3").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=Sheet1!RC[-1]"

    Range("C3").Select

    Selection.Copy

    Range("C4:C195").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

End Sub

************************************************

 

Enhancement Request:

 

1.  Please change my Macro into a conditional statement that will concatenate ID numbers (ID1…ID10) and put the result  in cell  B3 up to the last item.  

 

2.  I want the content of IMAGE NAME, ID, and SCORE in Sheet1 to be automatically copied in Sheet2.

3.  Since answer sheets are scanned according to the order they are submitted by the students, in Sheet2 I would like to sort the Test ID in Ascending Order  by Section (the Image Name will help ensure that  a group scores still belong to the same section) and also Sort the ID from my class roll (accompanied by the student name) in ascending order so I could easily put them side by side and match them. 

4. After being sure to whom the scores belong, I could re-sort alphabetically the column of student last name by section.

[Glenn Lo]

Hi Vel,

Based on my interpretation of what you're trying to do, I think it can be set up so that they can all be done without any additional macro or additional effort after you've done it once. Once you've made the revisions mentioned listed below, you simply have to save the Excel file, then Save a copy of it under a file name that you can use for future tests.  The changes will not be affected by running any of the macros.

1. The macros will not use anything from Row 1,  Just fix it and save a copy.  Just load a copy of the file to process future data.

2. I may be wrong but I'm sensing that you are manually putting in the TestIDs?  You shouldn't have to.  If it is part of the input strings from the scanned data, the parsing code should automatically concatenate those numbers and put them in column B, with the letter "N" in front.  If all you wanted was to remove the leading N, modify the ParseAndCalculateScore macro; look for the code shown below and delete the highlighted line (or comment it out by putting an apostrophe at the beginining of the line) .

  i = 3

  While Sheet1.Cells(i, 1) <> ""

    Score = 0

    Sheet1.Cells(i, 2) = "N"

    Answers() = Split(Sheet1.Cells(i, 1), ";")

    For k = 1 To UBound(Answers)

     If Answers(k) = "" Then Answers(k) = "X"

     If k <= LastItem Then

      Sheet1.Cells(i, 3 + k) = Answers(k)

      If k > 0 Then

      Select Case Answers(k)

       Case "A", "B", "C", "D"

        Sheet1.Cells(i, 3 + k).Interior.ColorIndex = 4 'vbGreen

        If Answers(k) = Answerkey(k) Then

         Sheet1.Cells(i, 3 + k).Interior.ColorIndex = 6 ' vbYellow

         Score = Score + 1

        End If

       Case Else

        Sheet1.Cells(i, 3 + k).Interior.ColorIndex = 2 'vbRed

      End Select

      End If

     Else

      Sheet1.Cells(i, 2) = Sheet1.Cells(i, 2) & Answers(k)

     End If

    Next

   Sheet1.Cells(i, 3) = Score

   i = i + 1

  Wend

 

3. The roster in Sheet2 (columns A, B, and C) do not need t be in alphabetical order or any partiicular order.  Of course, it is best to put them in alphabetical order by section.  The macro simply matches the ID you enter in column C to the IDs in column B of Sheet1, and puts the corresponding score in column D.  Since the entries for columns A, B, and C are manually entered, you enter them one time  and load a copy of the file to process future data.  The macro only affects column D.

I suggest adding the highlighted line in the section of  code shown below for the ScoreReport procedure.  This puts the scanning sequence number in column E.  This will allow you to sort by scanning sequence, i.e., sort the Sheet2 report in the same order as Sheet1.    You can also manually add a column for section number, so that you can later sort by section. 

i = 3

While Sheet2.Cells(i, 1) <> ""

 k = 3

 While Sheet1.Cells(k, 2) <> ""

  If Sheet2.Cells(i, 3) = Sheet1.Cells(k, 2) Then

  Sheet2.Cells(i, 4) = Sheet1.Cells(k, 3)

  Sheet2.Cells(i,5) = k

 End If

 k = k + 1

 Wend

 i = i + 1

Wend

4. Removing E and Others from the answer distribution is not necessary.   For your current tests, you know that "E" is going to come up as zero anyway; keeping it will save you the the hassle of re-coding it into the macro if you happen to have E in future exams .  Keeping "Others" is a quick way of knowing if there is a potential need to examine parsed responses for invalid answers (blanks or multiple responses); e.g.  A student's intended answer may be not marked dark enough and was taken as blank.

Sana nakatulong 'to. :)

Glenn

[Glenn Lo]

Hi Vel and Chad,

Sorry if you're getting multiple copies of my response to Vel's post.  I had to revise the earlier posts.  I deleted them and just kept the most recent.

Glenn

[Vel Suminguit]

Hi Glenn,

Thanks for taking time to respond.  The original ProcessBubbles (v1.9 to v2.1) only gave me the leading N in column B.  That's why I ended up putting a formula in B3  "=RC[122]&RC[123]&RC[124]&RC[125]&RC[126]&RC[127]&RC[128]&RC[129]&RC[130]&RC[131]" then copied the formula and pasted all the way to B195, which resulted in the creation of the following Macro:  

Sub IDCapture()

'

' IDCapture Macro

' To combine ID numbers from D1 to D10 into one

'

' Keyboard Shortcut: Ctrl+d

'

    ActiveCell.FormulaR1C1 = _

        "=RC[122]&RC[123]&RC[124]&RC[125]&RC[126]&RC[127]&RC[128]&RC[129]&RC[130]&RC[131]"

    Range("B3").Select

    Selection.Copy

    Range("B4:B195").Select

    ActiveSheet.Paste

    Sheets("Sheet2").Select

    Range("C3").Select

    Application.CutCopyMode = False

    ActiveCell.FormulaR1C1 = "=Sheet1!RC[-1]"

    Range("C3").Select

    ActiveCell.Copy

    Range("C4:C195").Select

    ActiveSheet.Paste

    Application.CutCopyMode = False

    Sheets("Sheet1").Select

End Sub

************************************************************************************************

I will try your suggestion later on, and will get back to you with results. I'm glad that you have some Tagalog phrases.

Regards,

Vel

[Glenn Lo]

Hi Vel,

If you can send me your v2.1 file with just the strings to be parsed entered in column A of sheet 1, I can take a quick look to figure out what the problem might be.

Kasabot sad ko'g bisaya.

Glenn

[Vel Suminguit]

Hi Glenn,

I know the problem now. ProcessBubbles is expecting to parse a semicolon delimited CSV file.  I made a mistake by making an extra step of importing CSV file into regular Excel file before pasting the answers into the ProcessBubbles.  That's why ProcessBubbles only return the leading N in Column B because it cannot detect any ID number in Column A because I only put the the filename string. I must have been driven by the fact that Excel file arranged in columns are easier on the eyes.

Wow, you also know Bisaya.  If you wish to share a little bit about your ancestral background, that would be appreciated.  You can directly send it to my email without the public viewing it. Thank you for the great work that you, Chad, and Alberto are doing.  You made the life of thousands of underpaid teachers a lot easier.

God Bless.

Vel

[Glenn Lo]

Great.  

Once column A has been parsed, you can replace its contents; just be sure there it is not made blank.  I do like keeping it there in case I make a mistake in overriding the parsed data.  I can just re-parse if necessary.