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