Option Explicit
Dim pulse As Integer    'Varible for desired_pulse
Dim str As Variant      'Count pulse received from microcontroller
Dim speed As Double     'Real speed
Dim desired_speed As Double
Dim Error_speed As Double
Dim objExcel As Excel.Application   ' Setup for OLE graph
Dim wExcel As Excel.Workbook
Dim xlchart As Excel.Chart
Dim j As Integer

Private Sub cmdExit_Click()
    objExcel.Visible = True
    End
    'Exit the form and show the graph in Microsoft Excel
End Sub

Private Sub cmdNew_Click()
    'Clear the graph
    Workbooks("Speed2").Sheets("Sheet1").Range("A1:E1000") = ""
    j =2
    OLE_Speed.Update
    Counter1.Value = 0
    Counter2.Value = 0
    Counter3.Value = 0
End Sub

Private Sub cmdRun_Stop_Click()
    If cmdRun_Stop.Caption = "&Run" Then
        desired_speed = Val(cboSpeed.Text) ' Get desired speed from user
        pulse = Round(desired_speed / 60 * 4 * 0.39321) 'Convert the desired speed into desired pulse
    If Val(cboSpeed.Text) > 0 Then
        ' If receive desired speed from user
        MSComm1.Output = Chr$(pulse) ' Send desired count pulse to microcontroller
        lblPulse.Caption = pulse
        Counter1.TimerEnabled = True ' Start the counter as timer
        cboSpeed.Locked = True
        ' Lock the desired speed selection
        cmdRun_Stop.Caption = "&Stop"
        cmdExit.Enabled = False
        ' Disable the command of Exit and New Graph
        cmdNew.Enabled = False
    Else
        MsgBox "You must select a desired speed first!", vbOKOnly, "Invalid Data"
        ' If no desired speed receive from user
        MSComm1.Output = Chr$(0)
        cmdExit.Enabled = True
        cmdNew.Enabled = True
    End If
    ElseIf cmdRun_Stop.Caption = "&Stop" Then
        Counter1.TimerEnabled = False
        ' Stop the timer
        cmdRun_Stop.Caption = "&Run"
        pulse = 0
        MSComm1.Output = Chr$(0)
        ' Send speed = 0 rpm to stop the motor
        cboSpeed.Locked = False
        cmdExit.Enabled = True
        cmdNew.Enabled = True
    End If
End Sub

Private Sub Form_Load()
    'MSComm setup
    With MSComm1
        .CommPort = 2
        ' Use CommPort2 to communicate with microcontroller
        .Settings = "9600,N,8,1"
        ' Baud rate 9600, none parity, 8 data bits, 1 stop bit
        .InBufferSize = 1024
        ' Receiver buffer = 1024 bytes
        .OutBufferSize = 1024
        ' Transmitter buffer = 1024 bytes
        .DTREnable = True
        ' Enable the Data Terminal Ready signal
        .EOFEnable = False
        ' Disable the End of File type
        .Handshaking = comNone ' Disable all network handshaking
        .InputLen = 1
        ' Read all the charater in buffer
        .InputMode = comInputModeText ' Set the incoming messages to ve ASCII text characters
        .NullDiscard = False
        ' Discard bytes that are all zero's
        .RThreshold = 1
        ' Set the Receive Oncomm event to occur after 1 byte of data have been received
        .RTSEnable = True
        ' Enable the request to send data
        .SThreshold = 1
        ' Set the Transmitter Oncomm event to occur after 1 byte of data have been received
        .PortOpen = True
        ' Open CommPort
    End With

    'Setup for excel file
    Set objExcel = GetObject("", "Excel.Application")
    Set wExcel = objExcel.Workbooks.Open("C:\Speed2.xls") ' File Speed2 as storage
    objExcel.Visible = False
    j = 2
    With Workbooks("Speed2").Sheets("Sheet1")
        .Cells(1, 1) = 0
        .Cells(2, 1) = 0
    End With

    OLE_Speed.Update
End Sub

Private Sub MSComm1_OnComm()
    If MSComm1.CommEvent = comEvReceive Then
        ' This is used when data is received
        str = Asc(MSComm1.Input)
        'Get the counter pulse from microcontroller
        lblCount_pulse.Caption = str
        speed = Round(str / 4 / 0.39321 * 60, 2) ' Convert counter pulse into speed(rpm)
        Error_speed = Round((speed - desired_speed), 2) ' Calculate the error between detected speed with the desired speed
        lblSpeed.Caption = speed
        lblError.Caption = Error_speed
        MSComm1.Output = Chr$(pulse) 'Always send desired pulse to microcontroller
    End If

    If Counter1.Value > 0 Then
        If (Counter3.Value * 60 * 60) + (Counter2.Value * 60) + Counter1.Value > Workbooks("Speed2").Sheets("Sheet1").Cells(j - 1, 1).Value Then
            'Plotting graph
            With Workbooks("Speed2").Sheets("Sheet1")
                .Cells(j, 1) = (Counter3.Value * 60 * 60) + (Counter2.Value * 60) + Counter1.Value
                .Cells(j, 2) = Format(speed, "##.##")
                .Cells(j, 3) = desired_speed
            End With
            j = j + 1
            OLE_Speed.Update ' Update the graph
        End If
    End If
    'Timer : Counter1 as second, Counter2 as minute, and Counter3 as hour
    If Counter1.Value = 60 Then
        Counter2.Value = Counter2.Value + 1
        Counter1.Value = 0
        Counter1.TimerEnabled = True
        If Counter2.Value = 60 Then
            Counter3.Value = Counter3.Value + 1
            Counter2.Value = 0
        End If
End Sub