Private Sub Workbook_Open()
    Call InitializeWorkbook
    Call InitDV_Click
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range) 'FilteredData worksheet change
    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
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    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
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 ws = ThisWorkbook.Worksheets("OrigData")
    lastRow = ws.Cells(ws.Rows.count, "B").End(xlUp).Row
    maxGlucose = Application.WorksheetFunction.Max(ws.Range("B2:B" & lastRow))
    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 ws = ActiveSheet
    startDate = ws.Range("A2").Value
    endDate = ws.Range("B2").Value
    startTime = ws.Range("C2").Value
    endTime = ws.Range("D2").Value
    If endTime = TimeSerial(0, 0, 0) Then
        endTime = TimeSerial(23, 59, 59)
    End If
    uniqueDates = gUniqueDates
    PlotMultiSeriesGlucoseVsTimeXYChart startDate, endDate, startTime, endTime, uniqueDates
    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
    startRow = 2
    Do While startRow <= lastRow
        If ws.Cells(startRow, "A").Value >= startDate + timeValue(startTime) Then
            Exit Do
        End If
        startRow = startRow + 1
    Loop
    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
    ReDim glucoseValues(1 To endRow - startRow)
    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
    startRow = 2
    Do While startRow <= lastRow
        If ws.Cells(startRow, "A").Value >= startDate + timeValue(startTime) Then
            Exit Do
        End If
        startRow = startRow + 1
    Loop
    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
    ReDim timeValues(1 To endRow - startRow)
    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
    For Each cht In ws.ChartObjects
        If cht.Name = "ChartGlucose" Then
            cht.Delete
        End If
    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
    chartTop = ws.Cells(3, 1).Top
    Set cht = ws.ChartObjects.Add(Left:=chartLeft, Top:=chartTop, Width:=chartWidth, Height:=chartHeight)
    cht.Name = "ChartGlucose"
    cht.Chart.ChartType = xlXYScatterSmoothNoMarkers
    Do While cht.Chart.SeriesCollection.count > 0
        cht.Chart.SeriesCollection(1).Delete
    Loop
    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
    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
    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
    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
    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
    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
    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
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
    ReDim allGlucoseValues(1 To 1)
    dataCount = 0
    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
    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
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
    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
    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
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 wsHeatMap = ThisWorkbook.Worksheets("heatmap")
    Set wsOrigData = ThisWorkbook.Worksheets("OrigData")
    wsHeatMap.Cells.Clear
    wsHeatMap.Cells.ClearFormats
    uniqueDates = gUniqueDates
    Set uniqueMonths = New Collection
    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
    wsHeatMap.Cells(3, 2).Value = "Month"
    For i = 1 To 31
        wsHeatMap.Cells(3, i + 2).Value = i
    Next i
    wsHeatMap.Rows(3).Font.Bold = True
    wsHeatMap.Rows(3).Font.Size = 12
    wsHeatMap.Columns(2).Font.Bold = True
    wsHeatMap.Columns(2).Font.Size = 12
    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
    lastRow = wsHeatMap.Cells(wsHeatMap.Rows.count, "B").End(xlUp).Row
    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
    wsHeatMap.Range("B3:AG" & lastRow).Columns.AutoFit
    Exit Sub
ErrHandler:
    MsgBox "Error in CreateHeatMap: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
End Sub
Sub CreateHeatMapMSCoPilot()
    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
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Set wsHeatMap = ThisWorkbook.Worksheets("heatmap")
    Set wsOrigData = ThisWorkbook.Worksheets("OrigData")
    wsHeatMap.Cells.Clear
    wsHeatMap.Cells.ClearFormats
    uniqueDates = gUniqueDates
    Set uniqueMonths = New Collection
    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
    wsHeatMap.Cells(3, 2).Value = "Month"
    For i = 1 To 31
        wsHeatMap.Cells(3, i + 2).Value = i
    Next i
    wsHeatMap.Rows(3).Font.Bold = True
    wsHeatMap.Rows(3).Font.Size = 12
    wsHeatMap.Columns(2).Font.Bold = True
    wsHeatMap.Columns(2).Font.Size = 12
    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
    lastRow = wsHeatMap.Cells(wsHeatMap.Rows.count, "B").End(xlUp).Row
    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
    wsHeatMap.Range("B3:AG" & lastRow).Columns.AutoFit
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Exit Sub
ErrHandler:
    MsgBox "Error in CreateHeatMap: " & Err.Description & vbNewLine & "Error Number: " & Err.Number
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub