Count Participation and AttendanceΒΆ

Rem Attribute VBA_ModuleType=VBAModule  'LibreOffice Basic
Option VBASupport 1  'LibreOffice Basic
Sub count_participation_attendance()
    'uncomment correct program
    'program = "Excel VBA"
    program = "LibreOffice Basic"

    'specify function to use
    function_participation = "AVERAGE"
    function_absence = "AVERAGE"

    sheet_main = "grade"  ' sheet with student identifier. Data will be written to this sheet
    sheet_seating = "master-seating" ' sheet with seating map

    If program = "Excel VBA" Then
        separator = "!"
    Else
        separator = "."
    End If
 
    Worksheets(sheet_main).Activate
    
    numRow = Range("A65536").End(xlUp).Row
 
    For i = 2 To numRow ' for each student
        Worksheets(sheet_main).Activate
        studentIdentifier = Cells(i, 1).Value
 
        If studentIdentifier <> "" Then
            'find where the seating of the student
            Worksheets(sheet_seating).Activate
         
            With Range("A1:AM100")
                ' if search for the eid, MatchCase needs not to be True
                Set found = .Find(What:=studentIdentifier, LookAt:=xlWhole, MatchCase:=False)
     
                If found Is Nothing Then
                    MsgBox ("Cannot find the student " + studentIdentifier + " in the seat map")
                    Exit For
                Else
                    found.Activate
                    address_participation = ActiveCell.Offset(1, 0).Address(RowAbsolute:=False, ColumnAbsolute:=False)

                    ' here, change offset to (-1, 0) if the name is just one row, not two
                    address_absence = ActiveCell.Offset(-2, 0).Address(RowAbsolute:=False, ColumnAbsolute:=False)

                    formula_participation = "=" + function_participation + "("
                    formula_absence = "=" + function_absence + "("

                    ifprocess = 0
                    num_days = 0
     
                    ' loop over worksheets and grab the data
                    ' For Each ws In ThisWorkbook.Worksheets  'Excel VBA
                    For Each ws In ThisComponent.getSheets()  'LibreOffice Basic
                        If ifprocess = 1 Then
                            If ws.Name = "attendance-end" Then
                                exit For
                            End If
                            
                            Worksheets(ws.Name).Activate

                            tmp_participation = "'" + ws.Name + "'" + separator + address_participation 
                            tmp_absence = "'" + ws.Name + "'" + separator + address_absence 

                            If Right(formula_participation, 1) <> "(" Then ' not first, add ,
                                tmp_participation = "," + tmp_participation
                                tmp_absence = "," + tmp_absence
                            End If

                            formula_participation = formula_participation + tmp_participation
                            formula_absence = formula_absence + tmp_absence

                            num_days = num_days + 1
                                  
                        End If
                            
                        If ws.Name = "attendance-begin" Then
                            ifprocess = 1
                        End If
                       
                    Next ws
                   
                    Worksheets(sheet_main).Activate
                    Cells(i, 4).Formula = formula_participation + ")"
                    Cells(i, 5).Formula = formula_absence + ")"
                    
                End If
            End With
        End If
    
    Next i

    Worksheets(sheet_main).Activate

End Sub

Project Versions

Previous topic

VBA

Next topic

Generate Grade Handouts

This Page