Creating Custom Report Worksheets - Automated Data Analysis Using Excel - page 228

Sheets("QuizSort").Visible = False
Call CommentDropDn

(Range(Cells(12, 17),
Cells(LastRow("SampleReport.xls", "ClassGrades"), 17)))
End Sub

function is called once for every student in the report. The
function returns a single precision number truncated to two decimal places that represents the quiz
score for that student. In order to calculate the quiz score, however, the top three quiz scores must
be known. To accomplish this, the quiz scores are first copied to a hidden Worksheet named
“QuizSort.” The

Notice that the

CalcQuizScore

subroutine then sorts all of the known quiz scores on the
“QuizSort” Worksheet (notice that this leaves the order of the Quizzes on the original report
unchanged.) The top three quiz scores are then taken from the “QuizSort” Worksheet and can be
used to calculate the quiz score for each student. Recall that, when using hidden Worksheets, they
must be made visible to select ranges and perform operations upon them.

HorizontalSort

Function CalcQuizScore(ByVal wkbook, ByVal wksht, ByVal
startcol As Integer _
, ByVal endcol As Integer, ByVal row As Long) As Single

Call CopyBlockTo(ActiveWorkbook.Name, "ClassGrades", row,
startcol, row, endcol, _
ActiveWorkbook.Name, "QuizSort", row, startcol)
Call HorizontalSort (wkbook, "QuizSort", startcol, endcol, row,

True)
'Debug.Print
Workbooks("SampleReport.xls").Worksheets("QuizSort").Cells(row,
endcol).Value
CalcQuizScore =
(Workbooks("SampleReport.xls").Worksheets("QuizSort").Cells(row,
endcol).Value + _
Workbooks("SampleReport.xls").Worksheets("QuizSort").Cells(row,
endcol - 1).Value + _
Workbooks("SampleReport.xls").Worksheets("QuizSort").Cells(row,
endcol - 2).Value) / 3
'Truncate to 2 digits
CalcQuizScore = Format(CalcQuizScore, "###.00")
End Function

Sub HorizontalSort(ByVal wkbook, ByVal wksht, ByVal startcol
As Integer _

, ByVal endcol As Integer, ByVal row As Long, SortAscending
As Boolean)

Dim Order
'Select Workbook and Worksheet
ActivateWorkbook (wkbook)
Worksheets(wksht).Select
Range(Cells(row, startcol), Cells(row, endcol)).Select
If SortAscending = True Then Order = xlAscending Else Order
= xlDescending