Public coursename As String
Public coursesemester As String
Public iNameCount As Integer
Public names As Variant


Public Function get_names() As String() 'this function loops through the original data and provides a 2D array of the names of the course outcomes
    Sheets("RawData").Activate 'necessary
    ActiveCell.Offset(0, 1).Select
    Dim names(1 To 50, 0 To 10) As String
    'Dim iNameCount As Integer
    iNameCount = 0
        i = 2 'initial point
        j = 2
        Do Until IsEmpty(Sheets("RawData").Cells(i, j)) 'looping through to get data
            Sheets("RawData").Cells(i, j).Select
            If IsEmpty(ActiveCell) Then
                MsgBox "Exiting Do"
                Exit Do
            Else
                title = Cells(i, j).Value 'just getting name again
                position1 = InStr(title, "(")
                position2 = InStr(title, ")")
                Length = position2 - position1 - 1
                test = Mid(title, position1 + 1, Length)
                iNameCount = iNameCount + 1 'counter for positions in array
                names(iNameCount, 0) = test 'this time we are going to store it in an array
                j = j + 3
                k = k + 1
            End If
        Loop
        For i = 1 To iNameCount 'this will be used later for the frequency calculation
            names(i, 1) = 0
            names(i, 2) = 0
            names(i, 3) = 0
            names(i, 4) = 0
            names(i, 5) = 0
        Next i
    get_names = names 'the way VBA returns the array
End Function

Public Sub create_spreadsheet(name As String) 'nice little function to create the spreadsheets
    Dim WS As Worksheet
    Set WS = Sheets.Add
    WS.name = name
    Sheets(name).Cells(1, 1).Value = coursename
    Sheets(name).Cells(2, 1).Value = coursesemester
    Set WS = Nothing
End Sub

Public Sub border(name As String, row As Integer, column As Integer, n As Integer) 'function to create a border around a entire frequency table
    here1 = Sheets(name).Cells(row, column).Address
    here2 = Sheets(name).Cells(row + n, column + iNameCount).Address
    Sheets(name).Activate
    Range(here1 & ":" & here2).Select
    Selection.borders(xlDiagonalDown).LineStyle = xlNone
    Selection.borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Public Sub response_key(name As String, row As Integer, column As Integer) 'this generates the response key
    Sheets(name).Activate
    Sheets(name).Cells(row, column).Value = "Response Key:"
    Sheets(name).Cells(row, column).Font.Bold = True
    Sheets(name).Cells(row + 1, column).Value = "1:Strongly Agree"
    Sheets(name).Cells(row + 2, column).Value = "2:Agree"
    Sheets(name).Cells(row + 3, column).Value = "3:Neither Agree Nor Disagree"
    Sheets(name).Cells(row + 4, column).Value = "4: Disagree"
    Sheets(name).Cells(row + 5, column).Value = "5:Strongly Disagree"
    here1 = Sheets(name).Cells(row, column).Address
    here2 = Sheets(name).Cells(row + 5, column + 3).Address
    Range(here1 & ":" & here2).Select

    Selection.borders(xlDiagonalDown).LineStyle = xlNone
    Selection.borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    With Selection.borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With
    Selection.borders(xlInsideVertical).LineStyle = xlNone
    Selection.borders(xlInsideHorizontal).LineStyle = xlNone

End Sub

Private Sub thick_bottom_border(name As String, row As Integer, column As Integer)
    With Sheets(name).Cells(row, column).borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    With Sheets(name).Cells(row, column + 1).borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With

End Sub

Private Sub thin_bottom_border(name As String, row As Integer, column As Integer)
    With Sheets(name).Cells(row, column).borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Sheets(name).Cells(row, column + 1).borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With

End Sub


Public Function Originalnames(name As Variant) As String
'MsgBox "inside of originalnames"
Dim thenames(1 To 20) As String
    thenames(1) = "PO#1: This course provided students with: An ability to apply knowledge of mathematics, science, and engineering."
    thenames(2) = "PO#2: This course provided students with: An ability to design and conduct experiments, as well as analyze and interpret data."
    thenames(3) = "PO#3: This course provided students with: An ability to design a system, component, or process to meet desired needs."
    thenames(4) = "PO#4: This course provided students with: An ability to function on multi-disciplinary teams."
    thenames(5) = "PO#5: This course provided students with: An ability to identify, formulate, and solve engineering problems."
    thenames(6) = "PO#6: This course provided students with: An understanding of professional and ethical responsibility."
    thenames(7) = "PO#7: This course provided students with: An ability to communicate effectively."
    thenames(8) = "PO#7W:  This course provided students with: An ability to communicate effectively through written reports."
    thenames(9) = "PO#8: This course provided students with: The broad education necessary to understand the impact of engineering solutions in a global and societal context."
    thenames(10) = "PO#9: This course provided students with: A recognition of the need for, and an ability to engage in life-long learning."
    thenames(11) = "PO#10: This course provided students with: A knowledge of contemporary engineering issues."
    thenames(12) = "PO#11: This course provided students with: An ability to use the techniques, skills, and modern engineering tools necessary for engineering practice."
    thenames(13) = "PO#12: This course provided students with: An ability to apply principles of engineering, basic science, and mathematics (including multivariate calculus and differential equations)to model, analyze, design, and realize physical systems, components or processes."
    thenames(14) = "PO#13: This course provided students with: An ability to work professionally in both thermal and mechanical systems areas."
    thenames(15) = "PO#13M:  This course provided students with: An ability to work professionally in mechanical systems areas."
    thenames(16) = "PO#13T:   This course provided students with: An ability to work professionally in thermal systems areas."

    For i = 1 To 16
        position2 = InStr(thenames(i), ":")
        'MsgBox position2
        Value = Mid(thenames(i), 1, position2 - 1)
        'MsgBox Value

        If Value = name Then
            'MsgBox "Positive match Scotty!"
            Originalnames = thenames(i)
        End If
    Next i

End Function


Public Sub Charts_Click() 'LOOK AT THIS LAST.
'this function just takes all the created charts and compiles them in one book.
    icounter = 0 'required to make the code only go two graphs across
    w = 3 'starting points, w,k
    k = 1

    For i = 1 To iNameCount
        icounter = icounter + 1 'how many have we gone through
        Sheets(names(i, 0)).Activate 'necessary as we need to copy the chart
        ActiveSheet.ChartObjects("Chart 2").Activate 'activating the chart to copy it
        ActiveChart.ChartArea.Copy 'I wonder what that .Copy does
        Sheets("All_Charts").Select 'now we have the chart, moving to the right spreadsheet
        ActiveSheet.Cells(w, k).Select 'this represents the upper-left corner of the graph
        ActiveSheet.Paste 'pasting it in
        ActiveSheet.ChartObjects("Chart " & i).Activate 'the i is the chart number, linear as we paste them
        ActiveSheet.Shapes("Chart " & i).ScaleWidth 0.7625, msoFalse, msoScaleFromTopLeft 'scaling it down
        ActiveSheet.Shapes("Chart " & i).ScaleHeight 0.6840277778, msoFalse, msoScaleFromTopLeft 'scaling it down

        If icounter < 2 Then 'if statement to make it put only two charts next to eachother
            k = k + 6 'essentially this happens once
        Else
            k = 1
            w = w + 12
            icounter = 0
        End If
    Next i

    If counter = 0 Then
        w = w - 12 'fixes a problem with the position of the stuff below
    End If

    For i = 1 To iNameCount
        Sheets("All_Charts").Cells(w + 12 + i, 1).Value = Originalnames(names(i, 0))
    Next i
    Call response_key("All_Charts", w + 13 + iNameCount, 1)

End Sub

Public Sub CommandButton1_Click()
    'gets course name and semester for archiving purposes
    coursename = InputBox(Prompt:="What is the name of this course?", title:="ENTER COURSE NAME", Default:="ME 101")
    coursesemester = InputBox(Prompt:="What is the semester of this course?", title:="ENTER COURSE SEMESTER", Default:="Spring 2011")
    ' below converts text to numerical equivalents
    ActiveSheet.name = "RawData" 'Renames Data sheet so that we can reference it later.
    Cells.Replace What:="Strongly Agree", Replacement:="1", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Agree", Replacement:="2", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Neither Agree nor Disagree", Replacement:="3", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Disagree", Replacement:="4", LookAt:=xlWhole, MatchCase:=False
    Cells.Replace What:="Strongly Disagree", Replacement:="5", LookAt:=xlWhole, MatchCase:=False

    'adds some work sheets we will need later for data storage
    Call create_spreadsheet("FormattedData")
    Call create_spreadsheet("Frequency")
    Call create_spreadsheet("All_Charts")
    names = get_names
End Sub

Public Sub CommandButton2_Click() 'this routine extracts the rawdata into the formatted data spreadsheet
'immediately below is probably not necessary. But it activates the right sheet.
Sheets("FormattedData").Activate
ActiveCell.Offset(0, 1).Select
Sheets("RawData").Activate
ActiveCell.Offset(0, 1).Select
Dim i As Integer
Dim j As Integer
Dim w As Integer
Dim k As Integer
Dim Fir As Integer
Dim Fic As Integer
Dim n As Integer

        'i,j are for the RawData sheet cells. w,k are for FormattedData Cells
        'row,column
        i = 2
        j = 2
        w = 3
        k = 3
        Sheets("RawData").Cells(i, j).Select
        For Position = 1 To iNameCount
            'Sheets("RawData").Activate
            Sheets("RawData").Cells(i, j).Select 'selecting the cell in the loop
                test = names(Position, 0)
                Sheets("FormattedData").Cells(w, k).Value = "Response to " & test 'again, just formatting stuff
                Sheets("FormattedData").Cells(w, k).Font.Bold = True
                'Sheets("formattedData").Cells(w, k).Select
                'Call border("FormattedData", w, k)
                'The following is for the inner loop, that transfers the data between the two sheets
                    Rir = i 'R = raw data, I = inner (as in loop), r = row, c = column
                    Ric = j + 1
                    Fir = w + 1 'F = Formatted data, I = inner (as in loop), r = row, c = column
                    Fic = k
                    n = 0 ' initializing counter to get average, number of entries
                    flag = 0 'in case we have an <unanswered> occurance
                    Total = 0 'initializing counter to get counter for average
                Do Until IsEmpty(Sheets("RawData").Cells(Rir, Ric)) 'Loop to transfer data
                    n = n + 1 'Counter, for number of students
                    If IsEmpty(ActiveCell) Then
                        Exit Do
                    Else
                        If Sheets("RawData").Cells(Rir, Ric).Value <> "<Unanswered>" Then '<Unanswered> has no bearance on the results
                            Select Case Sheets("RawData").Cells(Rir, Ric) 'counting the numbers.
                                Case 1
                                    names(Position, 1) = names(Position, 1) + 1
                                Case 2
                                    names(Position, 2) = names(Position, 2) + 1
                                Case 3
                                    names(Position, 3) = names(Position, 3) + 1
                                Case 4
                                    names(Position, 4) = names(Position, 4) + 1
                                Case 5
                                    names(Position, 5) = names(Position, 5) + 1
                            End Select
                            Sheets("FormattedData").Cells(Fir, Fic).Value = Sheets("RawData").Cells(Rir, Ric).Value 'moving data
                            'Call border("FormattedData", Fir, Fic)
                            Total = Total + Sheets("FormattedData").Cells(Fir, Fic).Value 'running total to calc average
                        Else
                            flag = flag + 1 'this is to fix for avg error in the event <Unanswered> was encountered
                        End If
                        Sheets("FormattedData").Cells(Fir, 2).Value = n 'formatting, counting students
                        'Call border("FormattedData", Fir, 2)
                        Rir = Rir + 1
                        Fir = Fir + 1
                    End If
                Loop
                flag = n - flag 'corrects for an <unanswered> cell
                avg = Total / flag ' calcing average
                Sheets("FormattedData").Cells(n + 4, Fic).Value = avg 'Putting in average, plus 4 is to get below the bottom of data
                Sheets("FormattedData").Cells(n + 4, Fic).Font.Bold = True
                j = j + 3 'every significant column in RawData is 3 over
                k = k + 1 'moving down.
        Next Position
        Sheets("FormattedData").Cells(n + 4, 2).Value = "Average:" 'Makes it pretty. One might say formatted
        Sheets("FormattedData").Cells(n + 4, 2).Font.Bold = True
        Sheets("FormattedData").Cells(3, 2).Value = "Student #"
        Call border("FormattedData", 3, 2, n)
        Sheets("FormattedData").Cells(3, 2).Font.Bold = True
        For i = 1 To iNameCount
            Sheets("FormattedData").Cells(n + 6, 2).Value = Originalnames(names(i, 0))
            n = n + 1
        Next i
        Call response_key("FormattedData", n + 7, 2)
End Sub

Public Sub CommandButton3_Click() ' this generates the frequency spreadsheet, as well as graphs associated with freq
Sheets("RawData").Activate 'again, might not be necessary
ActiveCell.Offset(0, 1).Select
Dim i As Integer
Dim j As Integer
Dim title
Dim MyColumn As String, Here As String
Dim test As String
Dim w As Integer, k As Integer
        'initializing the start point for the data
        i = 2
        j = 2
        w = 3
        k = 1
        Count = 0
        Sheets("RawData").Cells(i, j).Select 'selecting the first cell to start the moving.
        For Position = 1 To iNameCount 'this will loop through our array of names to get the data
            Count = Count + 1
            Sheets("RawData").Activate
            Sheets("RawData").Cells(i, j).Select
                test = names(Position, 0)
                'MsgBox test
                Call create_spreadsheet(test)
                Sheets("RawData").Activate 'this is required, else a error occurs.
                Sheets("Frequency").Cells(w, k).Value = coursename & " " & test
                Call thick_bottom_border("Frequency", w, k)
                Sheets("Frequency").Cells(w, k).Font.Bold = True
                Sheets("Frequency").Cells(w + 1, k).Value = "Response"
                Call thin_bottom_border("Frequency", w + 1, k)
                Sheets("Frequency").Cells(w + 1, k + 1).Value = "Frequency"
                Sheets("Frequency").Cells(w + 1, k).Font.Italic = True
                Sheets("Frequency").Cells(w + 1, k + 1).Font.Italic = True
                n = 1
                    'generating the numbers to make the 1 - 5 on the left for frequency bin
                    For t = 2 To 6
                        Sheets("Frequency").Cells(w + t, k).Value = n
                        n = n + 1
                    Next t
                Sheets("frequency").Cells(w + 7, k).Value = "More"
                Call thick_bottom_border("Frequency", w + 7, k)
            'places values in the proper area
            Sheets("Frequency").Cells(w + 2, k + 1).Value = names(Position, 1)
            Here = Sheets("Frequency").Cells(w + 2, k + 1).Address
            Sheets("Frequency").Cells(w + 3, k + 1).Value = names(Position, 2)
            Sheets("Frequency").Cells(w + 4, k + 1).Value = names(Position, 3)
            Sheets("Frequency").Cells(w + 5, k + 1).Value = names(Position, 4)
            Sheets("Frequency").Cells(w + 6, k + 1).Value = names(Position, 5)
            loc1 = w + 2 'this is for the generation of graphs
            loc2 = w + 6
            'j = j + 3

            If Count <> 4 Then 'this just limits how far horizontally it goes in the frequency part.
                k = k + 3
            Else
                k = 1
                w = w + 10
                Count = 0
            End If
            ' Get the address of the active cell in the current selection
            'Here = Sheets("Frequency").Cells(w + 6, k + 1).Address done above

            ' Because .Address is $<columnletter>$<rownumber>, drop the first
            ' character and the characters after the column letter(s). WE NEED THIS TO GENERATE THE GRAPH
            MyColumn = Mid(Here, InStr(Here, "$") + 1, InStr(2, Here, "$") - 2)

            'The rest will create the histogram.
            'the activates are NECESSARY because otherwise the chart-creation commands bark at you
            Sheets(test).Activate
            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.ChartType = xlColumnClustered
            ActiveChart.SeriesCollection.NewSeries
            ActiveChart.SeriesCollection(1).name = "=""Frequency"""
            ActiveChart.SeriesCollection(1).Values = "=Frequency!$" + MyColumn + "$" + CStr(loc1) + ":$" + MyColumn + "$" + CStr(loc2) 'converts the frequency data to an excel-based location for the graph
            Sheets(test).Activate
            ActiveChart.HasTitle = True
            ActiveChart.ChartTitle.Select
            chartname = coursename & " " & test
            ActiveChart.ChartTitle.Text = chartname 'chart title: PO#1 etc
            ActiveChart.Location Where:=xlLocationAsObject, name:=test 'giving the chart a title
            ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis) 'adds a horizontal axis
            Selection.Format.TextFrame2.TextRange.Characters.Text = "Response" 'name of axis
            ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated) 'adds a vertical axis
            ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "Frequency" 'name of axis
            ActiveChart.SeriesCollection(2).Delete 'Slight data error in collecting numbers
            ActiveChart.Parent.Cut
            Range("A1").Select
            ActiveSheet.Paste
            Sheets(test).Cells(16, 1).Value = Originalnames(test)
            Call response_key(test, 18, 1)
        Next Position
End Sub

Private Sub CommandButton4_Click() 'this is the easy button, runs all the buttons above for ease of use
    now1 = Timer
    CommandButton1_Click
    CommandButton2_Click
    CommandButton3_Click
    Charts_Click
    now2 = Timer
    timed = now2 - now1
    MsgBox "That was easy. It took " & timed & " seconds to execute"

End Sub