Успеваемость студентов
Dim Count As Integer
Dim ChartObj As ChartObject
j = 2
Index = 0
For i = 0 To 3
Stat(i) = 0
Next i
If Autumn.Value = True Then Index = 0 Else Index = 1
ActiveWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
With ActiveSheet
.Cells.Clear
Do
Value = Worksheets("Storage").Cells(j, 1).Value
If Len(Value) > 0 Then
If Worksheets("Storage").Cells(j, 2).Value + " " + Worksheets("Storage").Cells(j, 3).Value + " " + Worksheets("Storage").Cells(j, 4).Value = Student.Value Then Exit Do
End If
j = j + 1
Loop Until Len(Value) = 0
For i = 0 To 8
If Split(Worksheets("Storage").Cells(j, i + 5).Value, ":")(Index) = "5" Then Stat(0) = Stat(0) + 1
If Split(Worksheets("Storage").Cells(j, i + 5).Value, ":")(Index) = "4" Then Stat(1) = Stat(1) + 1
If Split(Worksheets("Storage").Cells(j, i + 5).Value, ":")(Index) = "3" Then Stat(2) = Stat(2) + 1
If Split(Worksheets("Storage").Cells(j, i + 5).Value, ":")(Index) = "2" Then Stat(3) = Stat(3) + 1
Next i
.Cells(1, 1).Value = "Диаграмма успеваемости студента: "
.Cells(2, 1).Value = Student.Value
.Cells(3, 1).Value = Str(Index + 1) + "-й семестр"
For i = 0 To 3
.Cells(i + 4, 1).Value = "Оценка " + Str(5 - i)
.Cells(i + 4, 2).Value = Str(Stat(i))
Next i
Set ChartObj = .ChartObjects.Add(0, 0, 400, 400)
ChartObj.Chart.ChartType = xlPie
ChartObj.Chart.SeriesCollection.Add Source:=Range(.Cells(4, 2), .Cells(7, 2))
ChartObj.Chart.SeriesCollection.Item(1).XValues = Range(.Cells(4, 1), .Cells(7, 1))
ChartObj.Chart.HasLegend = True
ChartObj.Chart.Legend.Position = xlBottom
ChartObj.Chart.HasTitle = True
ChartObj.Chart.ChartTitle.Text = "Диаграмма успеваемости студента" + Chr(10) + Student.Value + Chr(10) + Str(Index + 1) + "-й семестр"
End With
End Sub
Private Sub Group_Change()
Dim i As Integer
Dim Value As String
i = 2
Student.Clear
With ActiveWorkbook.Sheets("Storage")
Do
Value = .Cells(i, 1).Value
If Value = Group.Value Then
Student.AddItem .Cells(i, 2).Value + " " + .Cells(i, 3).Value + " " + .Cells(i, 4).Value
End If
i = i + 1
Loop Until Len(Value) = 0
End With
Student.ListIndex = 0
End Sub
Private Sub Spring_Click()
Spring.Value = True
Autumn.Value = False
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
Dim Value As String
i = 2
With ActiveWorkbook.Sheets("Storage")
Do
Value = .Cells(i, 1).Value
If Len(Value) > 0 Then
For j = 0 To Group.ListCount - 1
If Group.List(j) = Value Then Exit For
Next j
If j = Group.ListCount Then Group.AddItem Value
i = i + 1
End If
Loop Until Len(Value) = 0
End With
Group.ListIndex = 0
Autumn.Value = True
End Sub
Модули:
Модуль Main:
Public Sub QDialog1()
MainForm.Show
End Sub
Модуль Module 1:
Sub Macro1()
'' Macro1 Macro
' Macro recorded 25.12.2006 by Zeon
'Columns("A:A").ColumnWidth = 33.29
Columns("B:B").ColumnWidth = 17.14
Columns("C:C").ColumnWidth = 16.57
End Sub
Модуль Module 2:
Sub Macro2()
'' Macro2 Macro
' Macro recorded 25.12.2006 by Zeon
'Range("C17:D17").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("C16:D16").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("C19:D27").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("B16:D27").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("C16:D17").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("B16:B27").Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("C16:D17").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Range("C18:D18").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
End With
Range("C19:D27").Select
With Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
End With
End Sub