Option Explicit

Public gMaxGlucoseY As Long
Public gUniqueDates As Variant

Sub FindMaxGlucoseY()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim maxGlucose As Long

    On Error GoTo ErrHandler

    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("OrigData")

    ' Find the last row with data in column B (Glucose values)
    lastRow = ws.Cells(ws.Rows.count, "B").End(xlUp).Row

    ' Find the maximum glucose value
    maxGlucose = Application.WorksheetFunction.Max(ws.Range("B2:B" & lastRow))

    ' Round up to the nearest multiple of 20 using CEILING function
    gMaxGlucoseY = Application.WorksheetFunction.Ceiling(maxGlucose, 20)

    Exit Sub

ErrHandler:
    MsgBox "Error in FindMaxGlucoseY: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
End Sub

Sub PlotGlucoseAndAvgGlucoseClick()
    Dim ws As Worksheet
    Dim startDate As Date, endDate As Date
    Dim startTime As Date, endTime As Date
    Dim uniqueDates As Variant

    ' Set active worksheet
    Set ws = ActiveSheet

    ' Get the date and time parameters from cells
    startDate = ws.Range("A2").Value
    endDate = ws.Range("B2").Value
    startTime = ws.Range("C2").Value
    endTime = ws.Range("D2").Value

    ' Check if endTime is 12:00:00 AM and change it to 11:59:59 PM if it is
    If endTime = TimeSerial(0, 0, 0) Then
        endTime = TimeSerial(23, 59, 59)
    End If

    ' Get unique dates
    uniqueDates = gUniqueDates

    ' Call PlotMultiSeriesGlucoseVsTimeXYChart with the proper arguments
    PlotMultiSeriesGlucoseVsTimeXYChart startDate, endDate, startTime, endTime, uniqueDates

    ' Call PlotMultiSeriesAverageGlucoseByHourChart with the proper arguments

    PlotMultiSeriesAverageGlucoseByHourChart startDate, endDate, startTime, endTime, uniqueDates

    DisplayStatsForSelectedTimeFrame startDate, endDate, startTime, endTime, uniqueDates

End Sub
Function GetGlucoseValuesArrayForADateRange(startDate As Date, endDate As Date, startTime As Date, endTime As Date) As Variant
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim startRow As Long, endRow As Long
    Dim glucoseValues() As Integer
    Dim i As Long, j As Long

    On Error GoTo ErrHandler

    Set ws = ThisWorkbook.Worksheets("OrigData")

    lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
    If lastRow <= 1 Then
        Err.Raise 999, , "No data found in worksheet 'OrigData'."
    End If

    ' Find the first row matching the start date and time
    startRow = 2
    Do While startRow <= lastRow
        If ws.Cells(startRow, "A").Value >= startDate + timeValue(startTime) Then
            Exit Do
        End If
        startRow = startRow + 1
    Loop

    ' Find the last row matching the end date and time
    endRow = startRow
    Do While endRow <= lastRow
        If ws.Cells(endRow, "A").Value > endDate + timeValue(endTime) Then
            Exit Do
        End If
        endRow = endRow + 1
    Loop

    If startRow >= endRow Then
        Err.Raise 999, , "No data found for the specified criteria. Start Date: " & Format(startDate, "mm/dd/yyyy") & ", Start Time: " & Format(startTime, "h:mm AM/PM")
    End If

    ' Resize the array to hold the glucose values
    ReDim glucoseValues(1 To endRow - startRow)

    ' Populate the array with glucose values
    For i = startRow To endRow - 1
        j = i - startRow + 1
        glucoseValues(j) = CInt(ws.Cells(i, "B").Value)
    Next i

    GetGlucoseValuesArrayForADateRange = glucoseValues
    Exit Function

ErrHandler:
    MsgBox "Error in GetGlucoseValuesArrayForADateRange: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
    GetGlucoseValuesArrayForADateRange = Array()  ' Return an empty array in case of error
End Function

Function GetTimeValuesArrayForDateRange(startDate As Date, endDate As Date, startTime As Date, endTime As Date) As Variant
    On Error GoTo ErrHandler

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim startRow As Long, endRow As Long
    Dim timeValues() As Double
    Dim i As Long, j As Long

    Set ws = ThisWorkbook.Worksheets("OrigData")

    lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
    If lastRow <= 1 Then
        Err.Raise 999, , "No data found in worksheet 'OrigData'."
    End If

    ' Find the first row matching the start date and time
    startRow = 2
    Do While startRow <= lastRow
        If ws.Cells(startRow, "A").Value >= startDate + timeValue(startTime) Then
            Exit Do
        End If
        startRow = startRow + 1
    Loop

    ' Find the last row matching the end date and time
    endRow = startRow
    Do While endRow <= lastRow
        If ws.Cells(endRow, "A").Value > endDate + timeValue(endTime) Then
            Exit Do
        End If
        endRow = endRow + 1
    Loop

    If startRow >= endRow Then
        Err.Raise 999, , "No data found for the specified criteria. Start Date: " & Format(startDate, "mm/dd/yyyy") & ", Start Time: " & Format(startTime, "h:mm AM/PM")
    End If

    ' Resize the array to hold the time values
    ReDim timeValues(1 To endRow - startRow)

    ' Populate the array with time values
    For i = startRow To endRow - 1
        j = i - startRow + 1
        timeValues(j) = timeValue(ws.Cells(i, "A").Value)
    Next i

    GetTimeValuesArrayForDateRange = timeValues
    Exit Function

ErrHandler:
    MsgBox "Error in GetTimeValuesArrayForDateRange: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
    GetTimeValuesArrayForDateRange = Array()      ' Return an empty array in case of error
End Function
Option Explicit

Public gMaxGlucoseY As Long
Public gUniqueDates As Variant

Sub FindMaxGlucoseY()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim maxGlucose As Long

    On Error GoTo ErrHandler

    ' Set the worksheet
    Set ws = ThisWorkbook.Worksheets("OrigData")

    ' Find the last row with data in column B (Glucose values)
    lastRow = ws.Cells(ws.Rows.count, "B").End(xlUp).Row

    ' Find the maximum glucose value
    maxGlucose = Application.WorksheetFunction.Max(ws.Range("B2:B" & lastRow))

    ' Round up to the nearest multiple of 20 using CEILING function
    gMaxGlucoseY = Application.WorksheetFunction.Ceiling(maxGlucose, 20)

    Exit Sub

ErrHandler:
    MsgBox "Error in FindMaxGlucoseY: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
End Sub

Sub PlotGlucoseAndAvgGlucoseClick()
    Dim ws As Worksheet
    Dim startDate As Date, endDate As Date
    Dim startTime As Date, endTime As Date
    Dim uniqueDates As Variant

    ' Set active worksheet
    Set ws = ActiveSheet

    ' Get the date and time parameters from cells
    startDate = ws.Range("A2").Value
    endDate = ws.Range("B2").Value
    startTime = ws.Range("C2").Value
    endTime = ws.Range("D2").Value

    ' Check if endTime is 12:00:00 AM and change it to 11:59:59 PM if it is
    If endTime = TimeSerial(0, 0, 0) Then
        endTime = TimeSerial(23, 59, 59)
    End If

    ' Get unique dates
    uniqueDates = gUniqueDates

    ' Call PlotMultiSeriesGlucoseVsTimeXYChart with the proper arguments
    PlotMultiSeriesGlucoseVsTimeXYChart startDate, endDate, startTime, endTime, uniqueDates

    ' Call PlotMultiSeriesAverageGlucoseByHourChart with the proper arguments

    PlotMultiSeriesAverageGlucoseByHourChart startDate, endDate, startTime, endTime, uniqueDates

    DisplayStatsForSelectedTimeFrame startDate, endDate, startTime, endTime, uniqueDates

End Sub
Function GetGlucoseValuesArrayForADateRange(startDate As Date, endDate As Date, startTime As Date, endTime As Date) As Variant
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim startRow As Long, endRow As Long
    Dim glucoseValues() As Integer
    Dim i As Long, j As Long

    On Error GoTo ErrHandler

    Set ws = ThisWorkbook.Worksheets("OrigData")

    lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
    If lastRow <= 1 Then
        Err.Raise 999, , "No data found in worksheet 'OrigData'."
    End If

    ' Find the first row matching the start date and time
    startRow = 2
    Do While startRow <= lastRow
        If ws.Cells(startRow, "A").Value >= startDate + timeValue(startTime) Then
            Exit Do
        End If
        startRow = startRow + 1
    Loop

    ' Find the last row matching the end date and time
    endRow = startRow
    Do While endRow <= lastRow
        If ws.Cells(endRow, "A").Value > endDate + timeValue(endTime) Then
            Exit Do
        End If
        endRow = endRow + 1
    Loop

    If startRow >= endRow Then
        Err.Raise 999, , "No data found for the specified criteria. Start Date: " & Format(startDate, "mm/dd/yyyy") & ", Start Time: " & Format(startTime, "h:mm AM/PM")
    End If

    ' Resize the array to hold the glucose values
    ReDim glucoseValues(1 To endRow - startRow)

    ' Populate the array with glucose values
    For i = startRow To endRow - 1
        j = i - startRow + 1
        glucoseValues(j) = CInt(ws.Cells(i, "B").Value)
    Next i

    GetGlucoseValuesArrayForADateRange = glucoseValues
    Exit Function

ErrHandler:
    MsgBox "Error in GetGlucoseValuesArrayForADateRange: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
    GetGlucoseValuesArrayForADateRange = Array()  ' Return an empty array in case of error
End Function

Function GetTimeValuesArrayForDateRange(startDate As Date, endDate As Date, startTime As Date, endTime As Date) As Variant
    On Error GoTo ErrHandler

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim startRow As Long, endRow As Long
    Dim timeValues() As Double
    Dim i As Long, j As Long

    Set ws = ThisWorkbook.Worksheets("OrigData")

    lastRow = ws.Cells(ws.Rows.count, "A").End(xlUp).Row
    If lastRow <= 1 Then
        Err.Raise 999, , "No data found in worksheet 'OrigData'."
    End If

    ' Find the first row matching the start date and time
    startRow = 2
    Do While startRow <= lastRow
        If ws.Cells(startRow, "A").Value >= startDate + timeValue(startTime) Then
            Exit Do
        End If
        startRow = startRow + 1
    Loop

    ' Find the last row matching the end date and time
    endRow = startRow
    Do While endRow <= lastRow
        If ws.Cells(endRow, "A").Value > endDate + timeValue(endTime) Then
            Exit Do
        End If
        endRow = endRow + 1
    Loop

    If startRow >= endRow Then
        Err.Raise 999, , "No data found for the specified criteria. Start Date: " & Format(startDate, "mm/dd/yyyy") & ", Start Time: " & Format(startTime, "h:mm AM/PM")
    End If

    ' Resize the array to hold the time values
    ReDim timeValues(1 To endRow - startRow)

    ' Populate the array with time values
    For i = startRow To endRow - 1
        j = i - startRow + 1
        timeValues(j) = timeValue(ws.Cells(i, "A").Value)
    Next i

    GetTimeValuesArrayForDateRange = timeValues
    Exit Function

ErrHandler:
    MsgBox "Error in GetTimeValuesArrayForDateRange: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
    GetTimeValuesArrayForDateRange = Array()      ' Return an empty array in case of error
End Function
Option Explicit

Sub PlotMultiSeriesGlucoseVsTimeXYChart(startDate As Date, endDate As Date, startTime As Date, endTime As Date, uniqueDates As Variant)
    Dim currentDate As Date
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim srs As series
    Dim chartLeft As Double, chartTop As Double, chartWidth As Double, chartHeight As Double
    Dim chartTitle As String
    Dim i As Long
    Dim tempGlucoseArray As Variant
    Dim tempTimeArray As Variant
    Dim colorIndex As Long
    Dim visibleRange As Range
    Dim yValues As Variant
    Dim yMax As Double

    Set ws = ActiveSheet

    ' Delete existing chart if it exists
    For Each cht In ws.ChartObjects
        If cht.Name = "ChartGlucose" Then
            cht.Delete
        End If
    Next cht

    ' Get visible range
    Set visibleRange = ActiveWindow.ActivePane.visibleRange
    chartWidth = visibleRange.Width / 2 - 6
    chartHeight = visibleRange.Height - ws.Cells(3, 1).Top - 10
    chartLeft = ws.Cells(3, 1).Left
    chartTop = ws.Cells(3, 1).Top

    ' Create new chart
    Set cht = ws.ChartObjects.Add(Left:=chartLeft, Top:=chartTop, Width:=chartWidth, Height:=chartHeight)
    cht.Name = "ChartGlucose"
    cht.Chart.ChartType = xlXYScatterSmoothNoMarkers

    ' Clear existing series
    Do While cht.Chart.SeriesCollection.count > 0
        cht.Chart.SeriesCollection(1).Delete
    Loop

    ' Plot glucose data
    colorIndex = 1
    For i = LBound(uniqueDates) To UBound(uniqueDates)
        currentDate = uniqueDates(i)
        If currentDate >= startDate And currentDate <= endDate Then
            tempGlucoseArray = GetGlucoseValuesArrayForADateRange(currentDate, currentDate, startTime, endTime)
            tempTimeArray = GetTimeValuesArrayForDateRange(currentDate, currentDate, startTime, endTime)
            If IsArray(tempGlucoseArray) And IsArray(tempTimeArray) And UBound(tempGlucoseArray) >= 0 And UBound(tempTimeArray) >= 0 Then
                Set srs = cht.Chart.SeriesCollection.NewSeries
                With srs
                    .xValues = tempTimeArray
                    .Values = tempGlucoseArray
                    .Name = ""                    ' This removes the legend entry
                    .Format.Line.Weight = 1       ' Adjust line weight for better visibility
                    .Format.Line.Visible = True
                    .Format.Line.ForeColor.RGB = RGB(colorIndex * 25 Mod 255, colorIndex * 50 Mod 255, colorIndex * 75 Mod 255)
                End With
                colorIndex = colorIndex + 1
            End If
        End If
    Next i

    ' Add horizontal lines at y-values 70, 140, 180, and y-max
    yMax = gMaxGlucoseY
    yValues = Array(70, 140, 180, yMax)
    For i = LBound(yValues) To UBound(yValues)
        Set srs = cht.Chart.SeriesCollection.NewSeries
        With srs
            .ChartType = xlXYScatterLines
            .xValues = Array(0, 1)                ' X-values spanning the entire width of the chart
            .Values = Array(yValues(i), yValues(i))
            .Name = ""                            ' This removes the legend entry
            .Format.Line.Weight = 1.5             ' Adjust line weight for better visibility
            If yValues(i) = 70 Or yValues(i) = 140 Then
                .Format.Line.ForeColor.RGB = RGB(0, 255, 0) ' Green color for lines at 70 and 140
            Else
                .Format.Line.ForeColor.RGB = RGB(255, 0, 0) ' Red color for lines at 180 and y-max
            End If
            .MarkerStyle = xlMarkerStyleNone      ' No markers
        End With
    Next i

    ' Format axes
    With cht.Chart.Axes(xlCategory)
        .MinimumScale = 0
        .MaximumScale = 1.04166667                ' Changed as requested
        .MajorUnit = 0.04166667                   ' 1 hour interval
        .MinorUnit = 0.04166667                   ' 1 hour interval
        .HasMajorGridlines = True
        .HasMinorGridlines = True
        .TickLabels.Orientation = xlUpward        ' Rotate text 270 degrees
        .TickLabels.NumberFormat = "h AM/PM"
    End With

    With cht.Chart.Axes(xlValue)
        .MajorUnit = 20
        .MinorUnit = 10
        .HasMajorGridlines = True
        .HasMinorGridlines = True
        .MinimumScale = 0
        .MaximumScale = gMaxGlucoseY              ' Set max Y-axis bound to gMaxGlucoseY
    End With

    ' Set chart title
    If startDate = endDate Then
        chartTitle = "Glucose Values on " & Format(startDate, "ddd, mmm d, yyyy")
    Else
        chartTitle = "Glucose Values from " & Format(startDate, "mm/dd/yyyy") & " to " & Format(endDate, "mm/dd/yyyy")
    End If

    With cht.Chart
        .HasTitle = True
        .chartTitle.Text = chartTitle
        .chartTitle.Format.TextFrame2.TextRange.Font.Size = 16 ' Set font size to 16
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Time"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Glucose"
        .HasLegend = False                        ' Remove the legend
    End With

    cht.Chart.Refresh
End Sub

Sub PlotMultiSeriesAverageGlucoseByHourChart(startDate As Date, endDate As Date, startTime As Date, endTime As Date, uniqueDates As Variant)
    Dim ws As Worksheet
    Dim cht As ChartObject
    Dim srs As series
    Dim chartLeft As Double, chartTop As Double, chartWidth As Double, chartHeight As Double
    Dim chartTitle As String
    Dim hourlyTotals(0 To 23) As Long
    Dim hourCounts(0 To 23) As Long
    Dim hourlyAverages(0 To 23) As Long
    Dim i As Long, j As Long, k As Long, hourIndex As Long
    Dim currentDate As Date
    Dim glucoseValues As Variant
    Dim timeValues As Variant
    Dim xValues(0 To 23) As String
    Dim totalSum As Long
    Dim totalCount As Long
    Dim avgGlucose As Long
    Dim visibleRange As Range
    Dim startHour As Integer, endHour As Integer
    Dim gmi As Double
    On Error GoTo ErrHandler
    Set ws = ActiveSheet
    For i = 0 To 23
        hourlyTotals(i) = 0
        hourCounts(i) = 0
        xValues(i) = Format(TimeSerial(i, 0, 0), "h AM/PM")
    Next i
    For i = LBound(uniqueDates) To UBound(uniqueDates)
        currentDate = uniqueDates(i)
        If currentDate >= startDate And currentDate <= endDate Then
            glucoseValues = GetGlucoseValuesArrayForADateRange(currentDate, currentDate, startTime, endTime)
            timeValues = GetTimeValuesArrayForDateRange(currentDate, currentDate, startTime, endTime)
            If IsArray(glucoseValues) And IsArray(timeValues) Then
                For j = LBound(glucoseValues) To UBound(glucoseValues)
                    hourIndex = hour(CDate(timeValues(j)))
                    hourlyTotals(hourIndex) = hourlyTotals(hourIndex) + glucoseValues(j)
                    hourCounts(hourIndex) = hourCounts(hourIndex) + 1
                    totalSum = totalSum + glucoseValues(j)
                    totalCount = totalCount + 1
                Next j
            End If
        End If
    Next i
    For i = 0 To 23
        If hourCounts(i) > 0 Then
            hourlyAverages(i) = CLng(hourlyTotals(i) / hourCounts(i))
        Else
            hourlyAverages(i) = 0
        End If
    Next i
    If totalCount > 0 Then
        avgGlucose = CLng(totalSum / totalCount)
    Else
        avgGlucose = 0
    End If
    gmi = 3.31 + 0.02392 * avgGlucose
    For Each cht In ws.ChartObjects
        If cht.Name = "ChartAvgGlucose" Then cht.Delete
    Next cht
    Set visibleRange = ActiveWindow.ActivePane.visibleRange
    chartWidth = visibleRange.Width / 2 - 6
    chartHeight = visibleRange.Height - ws.Cells(3, 1).Top - 10
    chartLeft = ws.Cells(3, 1).Left + chartWidth
    chartTop = ws.Cells(3, 1).Top
    Set cht = ws.ChartObjects.Add(Left:=chartLeft, Top:=chartTop, Width:=chartWidth, Height:=chartHeight)
    cht.Chart.ChartType = xlColumnClustered
    cht.Name = "ChartAvgGlucose"
    Set srs = cht.Chart.SeriesCollection.NewSeries
    With srs
        .Values = hourlyAverages
        .xValues = xValues
        .Name = ""
    End With
    For i = 1 To srs.Points.count
        With srs.Points(i).Format.Fill.ForeColor
            If srs.Values(i) <= 97 Then
                .RGB = RGB(0, 255, 0)             ' Green for values 96 or less
            ElseIf srs.Values(i) >= 132 Then
                .RGB = RGB(255, 0, 0)             ' Red for values 132 or more
            ElseIf srs.Values(i) >= 98 Then
                .RGB = RGB(255, 165, 0)           ' Orange for values between 97 and 131
            End If
        End With
    Next i
    With cht.Chart.Axes(xlCategory)
        .TickLabelSpacing = 1
        .TickMarkSpacing = 1
        .HasMajorGridlines = True
        .TickLabels.Orientation = xlUpward
        .TickLabels.NumberFormat = "h AM/PM"
    End With
    With cht.Chart.Axes(xlValue)
        .MajorUnit = 20
        .MinorUnit = 10
        .HasMajorGridlines = True
        .HasMinorGridlines = True
        .MinimumScale = 0
        .MaximumScale = gMaxGlucoseY              ' Set max Y-axis bound to gMaxGlucoseY
    End With

    ' Set chart title
    If startDate = endDate Then
        chartTitle = "Average Hourly Glucose on " & Format(startDate, "ddd, mmm d, yyyy") & " from " & Format(startTime, "h AM/PM") & " to " & Format(endTime, "h AM/PM")
    Else
        chartTitle = "Average Hourly Glucose from " & Format(startDate, "mm/dd/yyyy") & " " & Format(startTime, "h:mm AM/PM") & _
                     " to " & Format(endDate, "mm/dd/yyyy") & " " & Format(endTime, "h:mm AM/PM")
    End If

    With cht.Chart
        .HasTitle = True
        .chartTitle.Text = chartTitle
        .chartTitle.Format.TextFrame2.TextRange.Font.Size = 16 ' Set font size to 16
        .Axes(xlCategory).HasTitle = True
        .Axes(xlCategory).AxisTitle.Text = "Hour"
        .Axes(xlValue).HasTitle = True
        .Axes(xlValue).AxisTitle.Text = "Average Glucose"
        .HasLegend = False
    End With
    srs.HasDataLabels = True
    With srs.DataLabels
        .ShowValue = True
        .Position = xlLabelPositionInsideEnd
        .NumberFormat = "0"
    End With

    ' Add horizontal lines at y-values 97, 132, and avgGlucose
    Dim lineSeriesNames As Variant
    Dim lineValues As Variant
    Dim lineColors As Variant
    Dim lineSrs As series
    Dim lineText As Variant

    lineSeriesNames = Array("97Normal", "132Diabetic", "AverageGlucose")
    lineValues = Array(97, 132, avgGlucose)
    lineColors = Array(RGB(0, 255, 0), RGB(255, 0, 0), RGB(0, 0, 0))

    For k = 0 To 2
        Set lineSrs = cht.Chart.SeriesCollection.NewSeries
        With lineSrs
            .ChartType = xlXYScatterLines
            .xValues = Array(0, 24)               ' X-values spanning the entire width of the chart
            .Values = Array(lineValues(k), lineValues(k))
            .Name = lineSeriesNames(k)
            .Format.Line.ForeColor.RGB = lineColors(k)
            If k = 0 Then
                .Format.Line.Weight = 3           ' Normal line width 3
            Else
                .Format.Line.Weight = 1.5         ' Other lines width 2
            End If
            .MarkerStyle = xlMarkerStyleNone      ' No markers
        End With
    Next k

    ' Add text box with line descriptions
    Dim textBox As Shape
    Set textBox = cht.Chart.Shapes.AddTextbox(msoTextOrientationHorizontal, 50, 50, 300, 100)
    With textBox.TextFrame2.TextRange
        .Text = "Average Glucose: " & avgGlucose & " (" & Format(gmi, "0.0") & "%)" & vbCrLf & "<= 97 (5.6%) Normal" & vbCrLf & ">= 98 (5.7%) & <= 132 (6.5%) (132) Pre-Diabetic" & vbCrLf & ">= 132 (6.5%) Diabetic"
        .Font.Size = 14
        .Font.Bold = msoTrue
        .ParagraphFormat.Alignment = msoAlignRight
        .Paragraphs(1).Font.Fill.ForeColor.RGB = RGB(0, 0, 0) ' Black for Average
        .Paragraphs(2).Font.Fill.ForeColor.RGB = RGB(0, 255, 0) ' Green for Normal
        .Paragraphs(3).Font.Fill.ForeColor.RGB = RGB(255, 165, 0) ' Orange for Pre Diabetic
        .Paragraphs(4).Font.Fill.ForeColor.RGB = RGB(255, 0, 0) ' Red for Diabetic
    End With

    cht.Chart.Refresh
    Exit Sub
ErrHandler:
    MsgBox "Error in PlotMultiSeriesAverageGlucoseByHourChart: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
End Sub

Sub CreateHeatMap()
    Dim wsHeatMap As Worksheet
    Dim wsOrigData As Worksheet
    Dim uniqueDates As Variant
    Dim uniqueMonths As Collection
    Dim currentDate As Date
    Dim avgGlucose As Double
    Dim glucoseValues As Variant
    Dim totalGlucose As Double
    Dim count As Long
    Dim i As Long, j As Long, k As Long
    Dim dayOfMonth As Integer
    Dim targetCell As Range
    Dim monthCell As Range
    Dim monthYear As String
    Dim lastRow As Long
    On Error GoTo ErrHandler

    ' Set worksheets
    Set wsHeatMap = ThisWorkbook.Worksheets("heatmap")
    Set wsOrigData = ThisWorkbook.Worksheets("OrigData")

    ' Clear existing content and formatting
    wsHeatMap.Cells.Clear
    wsHeatMap.Cells.ClearFormats

    ' Get unique dates
    uniqueDates = gUniqueDates

    ' Initialize unique months collection
    Set uniqueMonths = New Collection

    ' Extract unique month/year combinations
    On Error Resume Next
    For i = LBound(uniqueDates) To UBound(uniqueDates)
        monthYear = Format(uniqueDates(i), "MMM-YY")
        uniqueMonths.Add monthYear, monthYear
    Next i
    On Error GoTo 0

    ' Set up the top row with dates from 1 to 31
    wsHeatMap.Cells(3, 2).Value = "Month"
    For i = 1 To 31
        wsHeatMap.Cells(3, i + 2).Value = i
    Next i

    ' Bold and set font size for the top row and first column
    wsHeatMap.Rows(3).Font.Bold = True
    wsHeatMap.Rows(3).Font.Size = 12
    wsHeatMap.Columns(2).Font.Bold = True
    wsHeatMap.Columns(2).Font.Size = 12

    ' Loop through unique months
    For i = 1 To uniqueMonths.count
        monthYear = uniqueMonths(i)
        Set monthCell = wsHeatMap.Cells(i + 3, 2) ' Month column is column B
        monthCell.Value = monthYear
        monthCell.NumberFormat = "MMM-YY"         ' Format the cell as "MMM-YY"

        ' Loop through unique dates to find matching month/year
        For j = LBound(uniqueDates) To UBound(uniqueDates)
            If Format(uniqueDates(j), "MMM-YY") = monthYear Then
                currentDate = uniqueDates(j)
                glucoseValues = GetGlucoseValuesArrayForADateRange(currentDate, currentDate, TimeSerial(0, 0, 0), TimeSerial(23, 59, 59))

                ' Calculate average glucose for the date
                If IsArray(glucoseValues) Then
                    totalGlucose = 0
                    count = 0
                    For k = LBound(glucoseValues) To UBound(glucoseValues)
                        totalGlucose = totalGlucose + glucoseValues(k)
                        count = count + 1
                    Next k
                    If count > 0 Then
                        avgGlucose = Int(totalGlucose / count) ' Convert to integer
                    Else
                        avgGlucose = 0
                    End If
                Else
                    avgGlucose = 0
                End If

                ' Determine the day of the month and target cell
                dayOfMonth = Day(currentDate)
                Set targetCell = wsHeatMap.Cells(i + 3, dayOfMonth + 2) ' Offset by 3 rows and 2 columns

                ' Write average glucose value in the target cell
                targetCell.Value = avgGlucose

                ' Apply conditional formatting
                If avgGlucose <= 97 Then
                    targetCell.Interior.Color = RGB(0, 255, 0) ' Green
                ElseIf avgGlucose >= 98 And avgGlucose <= 132 Then
                    targetCell.Interior.Color = RGB(255, 165, 0) ' Orange
                ElseIf avgGlucose >= 132 Then
                    targetCell.Interior.Color = RGB(255, 0, 0) ' Red
                End If
            End If
        Next j
    Next i

    ' Find the last row with data
    lastRow = wsHeatMap.Cells(wsHeatMap.Rows.count, "B").End(xlUp).Row

    ' Apply borders to the entire heatmap data
    With wsHeatMap.Range("B3:AG" & lastRow)
        .Borders(xlEdgeLeft).LineStyle = xlContinuous
        .Borders(xlEdgeTop).LineStyle = xlContinuous
        .Borders(xlEdgeBottom).LineStyle = xlContinuous
        .Borders(xlEdgeRight).LineStyle = xlContinuous
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    End With

    ' AutoFit column width
    wsHeatMap.Range("B3:AG" & lastRow).Columns.AutoFit

    Exit Sub

ErrHandler:
    MsgBox "Error in CreateHeatMap: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
End Sub


Option Explicit
Sub PrevButtonClick()
    Dim ws As Worksheet
    Dim currentStartDate As Date
    Dim newStartDate As Date
    Dim startTime As Date, endTime As Date
    Dim uniqueDates As Variant

    Set ws = ActiveSheet
    currentStartDate = ws.Range("A2").Value
    startTime = ws.Range("C2").Value
    endTime = ws.Range("D2").Value

    ' Check if endTime is 12:00:00 AM and change it to 11:59:59 PM if it is
    If endTime = TimeSerial(0, 0, 0) Then
        endTime = TimeSerial(23, 59, 59)
    End If

    Debug.Print "PrevButtonClick calling GetUniqueDates"
    uniqueDates = gUniqueDates
    newStartDate = FindPreviousDate(currentStartDate, uniqueDates)

    If newStartDate <> 0 Then
        ws.Range("A2").Value = newStartDate
        'ws.Range("B2").Value = newStartDate ' Set end date same as start date

        PlotMultiSeriesGlucoseVsTimeXYChart newStartDate, newStartDate, startTime, endTime, uniqueDates
        PlotMultiSeriesAverageGlucoseByHourChart newStartDate, newStartDate, startTime, endTime, uniqueDates
        DisplayStatsForSelectedTimeFrame newStartDate, newStartDate, startTime, endTime, uniqueDates
    Else
        MsgBox "No previous date available."
    End If
End Sub

Sub NextButtonClick()
    Dim ws As Worksheet
    Dim currentStartDate As Date
    Dim newStartDate As Date
    Dim startTime As Date, endTime As Date
    Dim uniqueDates As Variant

    Set ws = ActiveSheet
    currentStartDate = ws.Range("A2").Value
    startTime = ws.Range("C2").Value
    endTime = ws.Range("D2").Value
    ' Check if endTime is 12:00:00 AM and change it to 11:59:59 PM if it is
    If endTime = TimeSerial(0, 0, 0) Then
        endTime = TimeSerial(23, 59, 59)
    End If

    uniqueDates = gUniqueDates
    newStartDate = FindNextDate(currentStartDate, uniqueDates)

    If newStartDate <> 0 Then
        ws.Range("A2").Value = newStartDate
        'ws.Range("B2").Value = newStartDate ' Set end date same as start date

        PlotMultiSeriesGlucoseVsTimeXYChart newStartDate, newStartDate, startTime, endTime, uniqueDates
        PlotMultiSeriesAverageGlucoseByHourChart newStartDate, newStartDate, startTime, endTime, uniqueDates
        DisplayStatsForSelectedTimeFrame newStartDate, newStartDate, startTime, endTime, uniqueDates
    Else
        MsgBox "No next date available."
    End If
End Sub

Function FindPreviousDate(currentDate As Date, uniqueDates As Variant) As Date
    Dim i As Long
    For i = UBound(uniqueDates) To LBound(uniqueDates) Step -1
        If uniqueDates(i) < currentDate Then
            FindPreviousDate = uniqueDates(i)
            Exit Function
        End If
    Next i
    FindPreviousDate = 0                          ' Return 0 if no previous date found
End Function

Function FindNextDate(currentDate As Date, uniqueDates As Variant) As Date
    Dim i As Long
    For i = LBound(uniqueDates) To UBound(uniqueDates)
        If uniqueDates(i) > currentDate Then
            FindNextDate = uniqueDates(i)
            Exit Function
        End If
    Next i
    FindNextDate = 0                              ' Return 0 if no next date found
End Function
Option Explicit
Sub DisplayStatsForSelectedTimeFrame(startDate As Date, endDate As Date, startTime As Date, endTime As Date, uniqueDates As Variant)
    Dim ws As Worksheet
    Dim glucoseValues As Variant
    Dim timeValues As Variant
    Dim allGlucoseValues() As Double
    Dim currentDate As Date
    Dim i As Long, j As Long, k As Long
    Dim dataCount As Long
    Dim gmi As Double

    Set ws = ActiveSheet

    ' Initialize array to store all glucose values
    ReDim allGlucoseValues(1 To 1)
    dataCount = 0

    ' Collect all glucose values within the specified date and time range
    For i = LBound(uniqueDates) To UBound(uniqueDates)
        currentDate = uniqueDates(i)
        If currentDate >= startDate And currentDate <= endDate Then
            glucoseValues = GetGlucoseValuesArrayForADateRange(currentDate, currentDate, startTime, endTime)
            timeValues = GetTimeValuesArrayForDateRange(currentDate, currentDate, startTime, endTime)

            If IsArray(glucoseValues) And IsArray(timeValues) Then
                For j = LBound(glucoseValues) To UBound(glucoseValues)
                    dataCount = dataCount + 1
                    ReDim Preserve allGlucoseValues(1 To dataCount)
                    allGlucoseValues(dataCount) = glucoseValues(j)
                Next j
            End If
        End If
    Next i

    ' Calculate statistics
    If dataCount > 0 Then
        Dim statsRange As Range
        Set statsRange = ws.Range("J1")

        ' Headers
        statsRange.Offset(0, 0).Value = "Count"
        statsRange.Offset(0, 1).Value = "Min"
        statsRange.Offset(0, 2).Value = "Avg"
        statsRange.Offset(0, 3).Value = "Median"
        statsRange.Offset(0, 4).Value = "Max"
        statsRange.Offset(0, 5).Value = "Std. Dev"
        statsRange.Offset(0, 6).Value = "GMI%"

        Dim avgGlucose As Long
        avgGlucose = Application.WorksheetFunction.Average(allGlucoseValues)
        gmi = 3.31 + 0.02392 * avgGlucose

        ' Values
        statsRange.Offset(1, 0).Value = dataCount
        statsRange.Offset(1, 1).Value = Int(Application.WorksheetFunction.Min(allGlucoseValues))
        statsRange.Offset(1, 2).Value = Int(avgGlucose)
        statsRange.Offset(1, 3).Value = Int(Application.WorksheetFunction.Median(allGlucoseValues))
        statsRange.Offset(1, 4).Value = Int(Application.WorksheetFunction.Max(allGlucoseValues))
        statsRange.Offset(1, 5).Value = Application.WorksheetFunction.StDev_P(allGlucoseValues)
        statsRange.Offset(1, 6).Value = gmi

        ' Format cells
        With statsRange.Offset(1, 0).Resize(1, 5)
            .NumberFormat = "0"
            .Font.Bold = True
        End With
        statsRange.Offset(1, 5).NumberFormat = "0.00"
        statsRange.Offset(1, 5).Font.Bold = True
        statsRange.Offset(1, 6).NumberFormat = "0.0"
        statsRange.Offset(1, 6).Font.Bold = True

        ' Add border
        With statsRange.Resize(2, 7)
            .Borders.Weight = xlThin
            .Borders(xlEdgeBottom).Weight = xlMedium
        End With
    Else
        MsgBox "No data found for the specified date and time range.", vbInformation
    End If
End Sub
Private Sub Workbook_Open()
    'Stop
    Call InitializeWorkbook
    Call InitDV_Click
    ' see Private Sub Worksheet_Change(ByVal Target As Range) in FiteredData sheet event
End Sub
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    'Debug.Print "Filtered Data Worksheet_Change event triggered for " & Target.Address

    If Not Intersect(Target, Range("A2:D2")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Address = "$A$2" Then
            Call UpdateEndDateValidation
        ElseIf Target.Address = "$C$2" Then
            Call UpdateEndTimeValidation
        ElseIf Target.Address = "$B$2" Then
            'Debug.Print "EndDate Changed"
            Call FindDatesBetweenStartAndEndDates
        End If
        Application.EnableEvents = True
    End If
End Sub