Hello all. Using Excel 2003 with WinXP. I'm trying to run macro code automatically whenever time = 9:30:01
(or whatever time I pick) for a stock market trading program, which is why the exact time matters so much.
I've been able to get the time to update fine, but unless I click on the worksheet while the time condition is
TRUE then my code doesn't run. The time actually is sent to me from the stock data provider and it shows up
in a cell as a constantly updating value.
I've tried using the Workbook_SheetChange function and OnTime method, but without luck. In both cases,
unless I activate the sheet the code doesn't run. By activate, I mean that I have to click on the sheet when
the values that trigger the code to run would be true. If I do that it works just fine, but sitting around and
clicking on a worksheet defeats the purpose of automation. Since I'm trading stocks this has to be very exact,
so I can't trust a macro scheduler to do this.
The code further below is what I'm trying to get to run. The time value is in cell L1. In cell L2 I have the
following code:
[vba]=IF(L1="9:30:01",TRUE, FALSE)[/vba]
That turns to True at 9:30:01 and false before and after that time. The code below checks cell L2 and
then runs the code below that. It works just fine when I click on L2 and the result is True, but unless I
manually "activate" the cell the result will turn to True but not run the code.
Placed in ThisWorkbook:
'[vba]Declare order object
Dim Order As SterlingLib.STIOrder
'Declare integer variable to manage loop
Dim intLoop As Integer
'Declare integer variable to hold submit order errors
Dim intSubmit As Integer
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$L$2" Then
If Target.Value = "True" Then
'Stop any possible runtime errors and halting code
On Error Resume Next
Application.EnableEvents = False
'Initialize intLoop to the second row to account for the title row
intLoop = 2
'Set up Loop to cover all positions until there is a blank line
Do Until Range("A" & intLoop).Value = vbNullString
'Initialize Order object
Set Order = New SterlingLib.STIOrder
'Determine Side for the order based on column C, Sell if position is Long, Buy if position is Short
If Range("C" & intLoop).Value > 0 Then
Order.Side = "S"
End If
If Range("C" & intLoop).Value < 0 Then
Order.Side = "B"
End If
'Determine Symbol for the order based on column A
Order.Symbol = Range("A" & intLoop).Value
'Set Time in Force for the order to Day
Order.Tif = "D"
'Determine Account for the order based on column B
Order.Account = Range("B" & intLoop).Value
'Determine Quantity of the order, whether long or short, it will equal the absolute value of the position
Order.Quantity = Abs(Range("C" & intLoop).Value)
'Determine Destination for the order based on column F, the default is ARCA
If Range("F" & intLoop).Value = vbNullString Then
Order.Destination = "ARCA"
Else
Order.Destination = Range("F" & intLoop).Value
End If
'Determine Price for the order based on column E, If it is a number it will be a limit price,
'otherwise it will send a market order
If IsNumeric(Range("E" & intLoop).Value) Then
Order.PriceType = ptSTILmt
Order.LmtPrice = Range("E" & intLoop).Value
Else
Order.PriceType = ptSTIMkt
End If
'Send the order and capture any errors
intSubmit = Order.SubmitOrder
'Display any errors. Error code of 0 means that the order was submitted successfully.
'See the Sterling Trader ActiveX API Guide under the Support Documentation drop down box at
If intSubmit <> 0 Then
MsgBox ("Submit Order Error " & Str(intSubmit) & "._
See the Sterling Trader ActiveX API Guide for more information.")
End If
'Destroy Order object
Set Order = Nothing
'Increment intLoop
intLoop = intLoop + 1
Loop
'Turn events back on
Application.EnableEvents = True
'Allow run time errors again
On Error GoTo 0
End If
End If
End Sub
[/vba]
[vba]Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$L$2" Then
If Target.Value = "True" Then
MsgBox ("Submit Order Error. See the Sterling Trader ActiveX API Guide for more information.")
End If
End If
End Sub[/vba]
Any help on this would be greatly appreciated. Thanks.
Reply With Quote
