TableOfBooks/histogram at master · Azarchaniel/TableOfBooks · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
Attribute VB_Name = "histogram"
Sub histogramVysky()
    '
    'creating histogram of dimensions of books using Excel function
    '

    Dim i As Integer
    Dim ws As String

    'activate particular sheet to work with
    If ActiveSheet.Name = "Knihy_L'uboš" Then
        Worksheets("Knihy_L'uboš").Activate
        ws = "Knihy_L'uboš"
    End If
    If ActiveSheet.Name = "Knihy_Žanetka" Then
        Worksheets("Knihy_Žanetka").Activate
        ws = "Knihy_Žanetka"
    End If

    'format of cell is TEXT
    Range("AI16:AI36").NumberFormat = "@"

    '
    'Height range text
    '
    For i = 0 To 8
      Range("AI" & 17 + i).Value = (i * 5) & " - " & (i * 5 + 5)  'creating labels
      Range("AJ" & 17 + i) = Application.WorksheetFunction.CountIfs(Worksheets(ws).Range(HEIGTH_COLUMN), "<=" & (i * 5 + 5), Worksheets(ws).Range(HEIGTH_COLUMN), ">" & (i * 5))
    Next i
    Range("AI25").Value = "<40"

    '
    'Width range text
    '
    For i = 0 To 8
      Range("AI" & 28 + i).Value = (i * 5) & " - " & (i * 5 + 5)
      Range("AJ" & 28 + i) = Application.WorksheetFunction.CountIfs(Worksheets(ws).Range(WIDTH_COLUMN), "<=" & (i * 5 + 5), Worksheets(ws).Range(WIDTH_COLUMN), ">" & (i * 5))
      'count how many values are between borders
    Next i
    Range("AI36").Value = "<40"

    Range("AI16").Value = "Výška knihy v cm"
    Range("AI27").Value = "Šírka knihy v cm"
    Range("AI16").WrapText = True
    Range("AI27").WrapText = True
    Range("AI16").Value = "Poèet kníh"
    Range("AI27").Value = "Poèet kníh"
    Range("AI16:AJ16").Font.Italic = True
    Range("AI27:AJ27").Font.Italic = True

    '
    'Drawing of borders for histogram values
    '
    Range("AI16:AJ16").Select
    Range("AI16:AJ16").HorizontalAlignment = xlLeft
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    Range("AI25:AJ25").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With

    Range("AI27:AJ27").Select
    Range("AI27:AJ27").HorizontalAlignment = xlLeft
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
    Range("AI36:AJ36").Select
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With

    'set alignment
    Range("AI17:AJ25").HorizontalAlignment = xlRight
    Range("AI28:AJ36").HorizontalAlignment = xlRight
    Range("AI17:AJ25").VerticalAlignment = xlBottom
    Range("AI28:AJ36").VerticalAlignment = xlBottom

End Sub