Databases Reference
In-Depth Information
a new header by copying it from one of two hidden Worksheets named “HdrVisible” or “HdrHid-
den,” depending upon if the Quiz grades are visible or hidden, respectively.
Sub CopySection(ByVal frmwkbook, ByVal frmwksht, ByVal r1,
ByVal c1, ByVal r2, ByVal c2, ByVal towkbook, _
ByVal towksht, ByVal row, ByVal col)
'Copies the "Chunk" Bounded by r1,c1,r2,c2 in frmwkbook to
towkbook starting at row,col
'Select "from" Workbook and Worksheet
ActivateWorkbook (frmwkbook)
Sheets(frmwksht).Visible = True
Worksheets(frmwksht).Select
Range(Cells(r1, c1), Cells(r2, c2)).Select
Selection.Copy
'Select "to" Workbook and Worksheet
ActivateWorkbook (towkbook)
Worksheets(towksht).Select
Range(Cells(row, col), Cells(row, col)).Select
ActiveSheet.Paste
Range("A1").Select
Sheets(frmwksht).Visible = False
End Sub
subroutine must perform one trick in order for it to work. When a
Worksheet, chart, column, or row is hidden, it cannot be selected from VBA code! So, in order for
the above subroutine to work, the Worksheets that hold the headers to be copied must be made
visible (just for an instant) in order to select and copy the preformatted headers. Although this
produces a quick flash on the screen, it can save a lot of coding and a great deal of headaches.
The
CopySection
6.4
EXECUTING CALCULATIONS UPON CHANGING VIEWS
The
subroutine is what calculates the quiz grades. It also adds a group of stan-
dardized comments to the final report that the user may select by means of a drop-down box.
Calculating the quiz scores is problematic here because only the top three quiz grades are to be
utilized. If only the top quiz score were sought, the Max function could be utilized. This leaves
the developer with two options. The first is to simply sort the quiz scores and select the highest
three. The second would be to use the Max function on the data set, then eliminate the selected
value from the data set, and take the Max function again two more times. The former shall be
utilized because it is simpler.
CalcQuizAvg
Sub CalcQuizAvg()
Sheets("QuizSort").Visible = True
For ii = 12 To LastRow("SampleReport.xls", "ClassGrades")
Workbooks("SampleReport.xls").Worksheets("ClassGrades").Cell
s(ii, 12) = _
CalcQuizScore
("SampleReport.xls", "ClassGrades", 6, 11, ii)
Next ii
Worksheets("ClassGrades").Activate
