Imports Microsoft.Office.Tools.Ribbon

Public Class Ribbon1

    Private Sub Ribbon1_Load(ByVal sender As System.Object, ByVal e As RibbonUIEventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(sender As Object, e As RibbonControlEventArgs) Handles Button1.Click
        Dim baseRange As Excel.Range = Globals.Sheet1.Range("C2")
        Dim ind = 0, RTL_char = ChrW(8207)

        While True
            Dim selectedRange = baseRange.Offset(ind, 0)
            Dim daysTotalRange As Excel.Range = selectedRange.Offset(0, 2)
            Dim totalDateStringRange As Excel.Range = selectedRange.Offset(0, 3)
            Try
                Dim daysTotal = CInt(ThisWorkbook.calcTotalDays(selectedRange))
                If daysTotal.ToString.Trim() = "" Then Exit While


                totalDateStringRange.NumberFormat = "@"

                daysTotalRange.Value2 = daysTotal
                totalDateStringRange.Value2 = ThisWorkbook.calcTotalDateString(daysTotal)
                selectedRange.Previous.Previous.Value2 = ind + 1


                selectedRange.ClearComments()
                selectedRange.Offset(0, 1).ClearComments()
                daysTotalRange.ClearComments()
                totalDateStringRange.ClearComments()
            Catch nx As NullReferenceException
                Exit While
            Catch invalidCast As InvalidExpressionException
                Dim msg2 = RTL_char + "در این سطر خطایی رخ داده است" + "."
                selectedRange.ClearComments()
                daysTotalRange.ClearComments()
                totalDateStringRange.ClearComments()
                selectedRange.AddComment(msg2)
                daysTotalRange.AddComment(msg2)
                totalDateStringRange.AddComment(msg2)
                daysTotalRange.Value2 = ""
                totalDateStringRange.Value2 = ""
            Catch argEx As ArgumentOutOfRangeException
                Dim msg3 = RTL_char + "لطفاً در وارد کردن تاریخها دقت فرمایید."
                selectedRange.ClearComments()
                selectedRange.Offset(0, 1).ClearComments()

                selectedRange.AddComment(msg3)
                selectedRange.Offset(0, 1).AddComment(msg3)

                daysTotalRange.Value2 = ""
                totalDateStringRange.Value2 = ""
            Catch ex As Exception
                MessageBox.Show(ex.Message) 'ex.GetType.ToString)
            End Try
            ind += 1
        End While

        Dim msg As String
        If ind = 0 Then
            msg = RTL_char + "لطفاً ابتدا مقادیر را وارد کنید."
        Else
            msg = RTL_char + "تعداد" + " " + ind.ToString + " " + RTL_char + "سطر با موفقیت پردازش شد."
        End If

        MessageBox.Show(msg)
    End Sub

    Private Sub Button2_Click(sender As Object, e As RibbonControlEventArgs) Handles Button2.Click
        Try
            Dim tmpRangeString As String = Globals.Sheet1.UsedRange.AddressLocal.ToString
            tmpRangeString = tmpRangeString.Replace("$A$1", "$A$2")
            Dim UsedRangeWithoutHeader As Excel.Range = Globals.Sheet1.Range(tmpRangeString)
            UsedRangeWithoutHeader.ClearContents()
            UsedRangeWithoutHeader.ClearComments()
        Catch ex As Exception
        End Try
    End Sub
End Class
