VBA auto restart

Question

I am creating a auto restart VBA excel program. When the condition is fulfilled(eg. "L"), this program will try to restart and try to fulfill the opposite condition (eg. "S")

So, that i want to create is: if i start with L fulfill L==>restart==> fulfill S==>restart==> fulfill L==>restart......... and similarly if i start with S fulfill S==>restart==> fulfill L==>restart==> fulfill S==>restart.........

I have the following code:

    Sub Start() 'start the program
        continue = True
        Call SPTrader.setBasicValue
        Call auto_open
    End Sub

    Sub auto_open() 'only use this will auto open
        If (continue = True) Then
          Call ScheduleStartProgram
        End If
    End Sub

    Private Sub ScheduleStartProgram() 'method inside here will be looping until stop
     t = 6 / 10 'set time to 0.6s
          TimeToRun = now() + TimeSerial(0, 0, t)
          Call DayTrade.findCurrenyClosestValue
    End Sub


Sub findCurrenyClosestValue() 'dynamic find the closest value
         Call findClosestMarketPrice
End Sub

Private Sub findClosestMarketPrice() 'find next price
   currentMarketPrice = ThisWorkbook.Sheets("TradingPage").Cells(6, 11).Value 'set Market Price
    If (ThisWorkbook.Sheets("TradingPage").Range("U6") <> 0 And currentMarketPrice >= ThisWorkbook.Sheets("TradingPage").Range("U6") And ThisWorkbook.Sheets("TradingPage").Range("U5").Text = "S" Or ThisWorkbook.Sheets("TradingPage").Range("U5").Text = "s" And ThisWorkbook.Sheets("TradingPage").Range("U6") <> 0 And currentMarketPrice >= ThisWorkbook.Sheets("TradingPage").Range("U6")) Then  'test stop poin
        Call TimeOut.TimeOut(3) 'time out 3s
        ThisWorkbook.Sheets("TradingPage").Range("S3") = ThisWorkbook.Sheets("TradingPage").Range("U6").Text
        ThisWorkbook.Sheets("TradingPage").Range("U5") = "L"
        ThisWorkbook.Sheets("SP trader").Range("C5") = 0  'set amount=0
        ThisWorkbook.Sheets("SP trader").Range("C6") = 0 'set amount=0
        ThisWorkbook.Sheets("TradingPage").Range("U6") = 0
        Call StartAndStop.auto_close
        'End
        Call StartAndStop.Start  'restart
         Exit Sub


    ElseIf (ThisWorkbook.Sheets("TradingPage").Range("U6") <> 0 And currentMarketPrice <= ThisWorkbook.Sheets("TradingPage").Range("U6") And ThisWorkbook.Sheets("TradingPage").Range("U5").Text = "L" Or ThisWorkbook.Sheets("TradingPage").Range("U5").Text = "l" And ThisWorkbook.Sheets("TradingPage").Range("U6") <> 0 And currentMarketPrice <= ThisWorkbook.Sheets("TradingPage").Range("U6")) Then 'test stop point
        Call TimeOut.TimeOut(3) 'time out 3s
        ThisWorkbook.Sheets("TradingPage").Range("S3") = ThisWorkbook.Sheets("TradingPage").Range("U6").Text
        ThisWorkbook.Sheets("TradingPage").Range("U5") = "S"
        ThisWorkbook.Sheets("SP trader").Range("C5") = 0  'set amount=0
        ThisWorkbook.Sheets("SP trader").Range("C6") = 0 'set amount=0
        ThisWorkbook.Sheets("TradingPage").Range("U6") = 0
        Call StartAndStop.auto_close
        Call StartAndStop.Start  'restart
        Exit Sub
  Else 'mainly run here
      If (currentMarketPrice >= currentClosestPrice And ThisWorkbook.Sheets("TradingPage").Range("U5") = "L" Or ThisWorkbook.Sheets("TradingPage").Range("U5") = "l" And currentMarketPrice >= currentClosestPrice) Then
       nextPriceYPosition = nextPriceYPosition - 1  
       nextPriceXPosition = 17
       currentClosestPrice = ThisWorkbook.Sheets("TradingPage").Cells(nextPriceYPosition, nextPriceXPosition)
       If (stopPointMethodPosition = 1 And counter = 0) Then ' in order to ensure the stopPointMethodPosition no equal 0
                 stopPointMethodPosition = 0
                 counter = counter + 1
       End If
       stopPointMethodPosition = stopPointMethodPosition + 1
       End If
      If (currentMarketPrice <= currentClosestPrice And ThisWorkbook.Sheets("TradingPage").Range("U5") = "S" Or ThisWorkbook.Sheets("TradingPage").Range("U5") = "s" And currentMarketPrice <= currentClosestPrice) Then
            nextPriceYPosition = nextPriceYPosition + 1
            If nextPriceYPosition = 40 Then   ' if next price at the boundary, show message
                Call StartAndStop.auto_close
                End
            End If
            nextPriceXPosition = 17
            currentClosestPrice = ThisWorkbook.Sheets("TradingPage").Cells(nextPriceYPosition, nextPriceXPosition)
            If (stopPointMethodPosition = 1 And counter = 0) Then   ' in order to ensure the stopPointMethodPosition no equal 0
                 stopPointMethodPosition = 0
                 counter = counter + 1
            End If
            stopPointMethodPosition = stopPointMethodPosition + 1
      End If
       If (ThisWorkbook.Sheets("TradingPage").Range("U5").Value <> "NA") Then  'for safety
                 Call FindMethodPosition.runAllFindMethodPosition
       End If
      If (ThisWorkbook.Sheets("TradingPage").Range("U5").Value = "L" Or ThisWorkbook.Sheets("TradingPage").Range("U5").Value = "l") Then
           ThisWorkbook.Sheets("TradingPage").Cells(nextPriceYPosition + 1, nextPriceXPosition).Font.Color = RGB(0, 0, 255) 'set now price blue
      ElseIf (ThisWorkbook.Sheets("TradingPage").Range("U5").Value = "S" Or ThisWorkbook.Sheets("TradingPage").Range("U5").Value = "s") Then
           ThisWorkbook.Sheets("TradingPage").Cells(nextPriceYPosition - 1, nextPriceXPosition).Font.Color = RGB(0, 0, 255) 'set now price blue
      End If
   End If
End Sub

For example, when i start with L and currentMarketPrice <= ThisWorkbook.Sheets("TradingPage").Range("U6"), it go into findClosestMarketPrice() and successfully return "s" and Call StartAndStop.Start 'restart

But after "Call StartAndStop.Start" when it start with S and currentMarketPrice >= ThisWorkbook.Sheets("TradingPage").Range("U6") it automatually go into findClosestMarketPrice() twice times. Like: if i start with L fulfill L==>restart==> fulfill S==>restart==> fulfill L==>fulfill L==>restart==>fulfill S==>fulfill S==>restart......... and similarly if i start with S fulfill S==>restart==> fulfill L==>restart==> fulfill S==>fulfill S==>restart==>fulfill L==>fulfill L==>restart.........

and this is wrong!

how can i create the program like this: if i start with L fulfill L==>restart==> fulfill S==>restart==> fulfill L==>restart......... and similarly if i start with S fulfill S==>restart==> fulfill L==>restart==> fulfill S==>restart.........

score 0 · Answer 1 · answered Nov 30 '16 at 03:41

Create a boolean variable to track the state if the schedule and pass the next state to the reopen macro as a parameter.

Note: TimeSerial only takes integers as a parameters

Public ScheduleS As Boolean

Sub ReStart()    'method inside here will be looping until stop
    Application.OnTime Now + 0.000001, "'ReOpen" & Chr(34) & (Not ScheduleS) & Chr(34) & "'"
    ThisWorkbook.Close SaveChanges:=True
End Sub

Sub ReOpen(bSchedule As Boolean)
    ScheduleS = bSchedule
    MsgBox ScheduleS
End Sub

VBA auto restart

1 Answers1