none
VBA Timer Updates RRS feed

Answers

  • Thank you both very much. 

    This was solved here:
    https://social.msdn.microsoft.com/Forums/office/en-US/1a8fa169-0484-4bbe-b7a9-5bd161578cae/update-macro-triggered-by-cell-content?forum=exceldev
    • Edited by NicoPer Wednesday, April 5, 2017 3:29 AM
    • Marked as answer by NicoPer Wednesday, April 5, 2017 3:29 AM
    Monday, January 9, 2017 10:37 PM

All replies

  • Hi NicoPer,

    please refer the example mentioned below to call the macro after every 10 seconds.

    Sub PlaceModifyOrder()
        'place your code here.....
        Call demo
    End Sub
    
    Sub demo()
        Application.OnTime Now + TimeValue("00:00:10"), "PlaceModifyOrder"
    End Sub

    Reference:

    Application.OnTime Method (Excel)

    to delay your execution for 1 second you can use Application.wait.

    Example:

    If Application.Wait(Now + TimeValue("0:00:10")) Then 
     MsgBox "Time expired" 
    End If
    

    Reference:

    Application.Wait Method (Excel)

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, January 4, 2017 12:44 AM
    Moderator
  • Thanks Deepak. I´ve added the delay to the PlaceModifyOrder macro the way you described in the first code and it seems to be working.

    Still have the main issue tough: running the macro "PlaceModifyOrder" every 10 seconds for every row that shows "Update" in the "DA" column.

    How can we do that?


    Thursday, January 5, 2017 6:01 PM
  • Hi Nicoper,

    I already suggested you a code in my last reply to call macro after every 10 seconds.

    Sub PlaceModifyOrder()
        'place your code here.....
        Call demo
    End Sub
    
    Sub demo()
        Application.OnTime Now + TimeValue("00:00:10"), "PlaceModifyOrder"
    End Sub

    here I post the same code again.

    you have to create a new sub. in that sub add "Application.ontime" and call "place modify order".

    then in the last line of "place modify order" sub call newly created sub.

    let's take a simple example. that you can run directly and understand how it works.

    Sub demo1()
        alert("Demo sub called")
        Call demo
    End Sub
    
    Sub demo()
        Application.OnTime Now + TimeValue("00:00:10"), "demo1"
    End Sub

    you can add this code in module. then run "demo1" sub.

    you will find that after every 10 seconds it will give you msgbox.

    if you have any problem to implement this suggestion then let me know about that. I will try to suggest you further. 

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 6, 2017 1:45 AM
    Moderator
  • I´ve done it, will test tomorrow.

    But for what I see, there will be a problem, as it will try to update every single row and we don´t want that.

    We want to run it *only* in the rows that have "Update" in the column "DA":


    BTW I´m getting this error now:
    Cannot run the macro "C:\test\timer.xlsm'!PlaceModifyOrder'. The macro may not be available in this workbook or all macros may be disabled.

    This is the added code:

    Public Sub PlaceModifyOrder_Click()
    [...]
    Call TradeTimerUpdate
    End Sub

    Sub TradeTimerUpdate()
        Application.OnTime Now + TimeValue("00:00:10"), "PlaceModifyOrder"
    End Sub

    • Edited by NicoPer Friday, January 6, 2017 2:47 AM
    Friday, January 6, 2017 2:17 AM
  • Hi NicoPer,

    The reason for this error is you are trying to call a Button click event.

    you can see that it is button click event.

    Public Sub PlaceModifyOrder_Click()

    if you want to call button click event by code then you can try like below.

    Sheet1.CommandButton1_Click
     or
    Sheets("Sheet Name").CommandButton1_Click

    but it will also not work here for you.

    because we are using Application.ontime.

    which take argument in string.

    so you need to create a sub like below and copy all the code from button to that sub.

    Sub PlaceModifyOrder()
        Debug.Print ("PlaceModifyOrder sub called")
        Call demo
    End Sub
    
    Sub demo()
        Application.OnTime Now + TimeValue("00:00:10"), "PlaceModifyOrder"
    End Sub

    place this code in a module and call the code from the sheet.

    other thing you had mentioned that you want to execute the code which contains text "Update".

    so for that you need to loop through that column and check for the value "Update". if text found then execute the code.

    below is the example.

    Sub demo1()
    Dim sh As Worksheet
    Dim RowCount As Long
    Dim j As Long
    Set sh = ActiveWorkbook.Worksheets("Conditional Orders")
        For j = 1 To sh.Cells(sh.Rows.Count, "DA").End(xlUp).Row
              
                If sh.Cells(j, 1).Value = "Update" Then
                    RowCount = RowCount + 1
                   'place your code here
                End If
        Next j
    End Sub

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 6, 2017 7:07 AM
    Moderator
  • Ok, here´s what I did. In the "Conditional Orders" Sheet:

    Public Sub PlaceModifyOrder_Click()
    [...]
    Call TradeDelay
    End Sub

    Sub UpdateOrders()
    Dim sh As Worksheet
    Dim RowCount As Long
    Dim j As Long
    Set sh = ActiveWorkbook.Worksheets("Conditional Orders")
        For j = 1 To sh.Cells(sh.Rows.Count, "DA").End(xlUp).row
              
                If sh.Cells(j, 1).value = "Update" Then
                    RowCount = RowCount + 1
                   Call PlaceModifyOrder_Click
                End If
        Next j
    Call TimerUpdateOrders
    End Sub
    


    Then in Module1:

    Sub TradeDelay()
    Application.Wait (Now + TimeValue("00:00:01"))
    End Sub
    
    Sub TimerUpdateOrders()
        Application.OnTime Now + TimeValue("00:00:10"), "UpdateOrders"
    End Sub


    But nothing happens.

    What´s wrong?
    Saturday, January 7, 2017 8:59 PM
  • Nico,

    In the sheets module:

    Public Sub PlaceModifyOrder_Click()
        Call UpdateOrders
    End Sub

    In module1:

    Option Explicit
    
    Sub UpdateOrders()
        Dim sh As Worksheet
        Dim RowCount As Long
        Dim j As Long
        Set sh = ActiveWorkbook.Worksheets("Conditional Orders")
        For j = 1 To sh.Cells(sh.Rows.Count, "DA").End(xlUp).Row
              
            If sh.Cells(j, "DA").Value = "Update" Then
                RowCount = RowCount + 1
                Call TradeDelay
                Debug.Print j
            End If
        Next j
        Call TimerUpdateOrders
    End Sub
    
    Sub TradeDelay()
        Application.Wait (Now + TimeValue("00:00:01"))
    End Sub
    
    Sub TimerUpdateOrders()
        Application.OnTime Now + TimeValue("00:00:10"), "UpdateOrders"
    End Sub

    Hope this helps.

    Jan


    • Edited by jgkzdl Sunday, January 8, 2017 2:48 PM
    Sunday, January 8, 2017 2:46 PM
  • Thank you very much Jan for stepping in.

    _______________

    A little more background:
    I´m trying to automate this update process:

    When the order is not completely filled or if there are any server errors, the "Update" cells appear automatically in the "DA" column.

    Right now if I place the cursor on the "Update" cell and click the "Update" button above (PlaceModifyOrder_Click), it sends the order again.
    That fixes all problems, and then the "Update" cell disappear.
    _______________


    I´ve copied the code exactly as you said and restarted and it doesn´t send the orders for the "Update" cells automatically as it should. 
    And right now the cursor is "loading" for a whole 2/3 seconds after 8 seconds or so.

    What could it be?
    Sunday, January 8, 2017 9:09 PM
  • Hi NicoPer,

    did you only call the "UpdateOrders" sub when you click on the button?

    like below?

    Public Sub PlaceModifyOrder_Click()
        Call UpdateOrders
    End Sub

    if yes, then it will only execute the code contains by the "UpdateOrders" sub.

    if you see the PlaceModifyOrder_Click() then you will find a long code in that.

    same I mentioned below.

    Public Sub PlaceModifyOrder_Click()
        If Not (objTWSControl Is Nothing) Then
            If objTWSControl.m_isConnected Then
                Dim id As Integer
                Dim orderId As Long
                
                ' create contract structure
                Set objTWSControl.m_contractInfo = objTWSControl.m_TWSControl.createContract()
                ' create order structure
                Set objTWSControl.m_orderInfo = objTWSControl.m_TWSControl.createOrder()
                
                id = ActiveCell.row
                
                ' id - place or modify order
                If Cells(id, Columns(COLUMN_ORDERID).column).value <> STR_EMPTY Then
                    orderId = Val(Cells(id, Columns(COLUMN_ORDERID).column).value)
                Else
                    orderId = objTWSControl.m_orderId
                End If
                
                ' contract info
                With objTWSControl.m_contractInfo
                    .symbol = UCase(Cells(id, Columns(COLUMN_SYMBOL).column).value)
                    .secType = UCase(Cells(id, Columns(COLUMN_SECTYPE).column).value)
                    .expiry = Cells(id, Columns(COLUMN_EXPIRY).column).value
                    .Strike = Cells(id, Columns(COLUMN_STRIKE).column).value
                    .Right = UCase(Cells(id, Columns(COLUMN_RIGHT).column).value)
                    .multiplier = UCase(Cells(id, Columns(COLUMN_MULTIPLIER).column).value)
                    .exchange = UCase(Cells(id, Columns(COLUMN_EXCH).column).value)
                    .primaryExchange = UCase(Cells(id, Columns(COLUMN_PRIMEXCH).column).value)
                    .currency = UCase(Cells(id, Columns(COLUMN_CURRENCY).column).value)
                    .localSymbol = UCase(Cells(id, Columns(COLUMN_LOCALSYMBOL).column).value)
                    .conId = Cells(id, Columns(COLUMN_CONID).column).value
                End With
            
                ' order info
                With objTWSControl.m_orderInfo
                    .action = UCase(Cells(id, Columns(COLUMN_ACTION).column).value)    ' BUY, SELL, SSHORT
                    .totalQuantity = Cells(id, Columns(COLUMN_TOTALQTY).column).value    ' the order quantity
                    .orderType = UCase(Cells(id, Columns(COLUMN_ORDERTYPE).column).value) ' MKT, MKTCLS, LMT, LMTCLS, PEGMKT, SCALE, STP, STPLMT, TRAIL, REL, VWAP, TRAILLIMIT
                    .lmtPrice = Cells(id, Columns(COLUMN_LMTPRICE).column).value         ' limit price
                    .auxPrice = Cells(id, Columns(COLUMN_AUXPRICE).column).value         ' stop price
                    
                    ' extended order attributes
                    .timeInForce = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_TIMEINFORCE).column).value, .timeInForce)
                    .ocaGroup = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_OCAGROUP).column).value, .ocaGroup)
                    .account = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_ACCOUNT).column).value, .account)
                    .openClose = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_OPENCLOSE).column).value, .openClose)
                    .origin = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_ORIGIN).column).value, .origin)
                    .orderRef = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_ORDERREF).column).value, .orderRef)
                    .transmit = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_TRANSMIT).column).value, .transmit)
                    .parentId = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_PARENTORDTID).column).value, .parentId)
                    .blockOrder = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_BLOCKORDER).column).value, .blockOrder)
                    .sweepToFill = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SWEEPTOFILL).column).value, .sweepToFill)
                    .displaySize = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DISPLAYSIZE).column).value, .displaySize)
                    .triggerMethod = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_TRIGGERMETHOD).column).value, .triggerMethod)
                    .Hidden = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_HIDDEN).column).value, .Hidden)
                    .discretionaryAmt = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DISCRAMOUNT).column).value, .discretionaryAmt)
                    .goodAfterTime = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_GOODAFTERTIME).column).value, .goodAfterTime)
                    .goodTillDate = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_GOODTILLDATE).column).value, .goodTillDate)
                    .faGroup = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_FAGROUP).column).value, .faGroup)
                    .faMethod = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_FAMETHOD).column).value, .faMethod)
                    .faPercentage = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_FAPERCENTAGE).column).value, .faPercentage)
                    .faProfile = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_FAPROFILE).column).value, .faProfile)
                    .shortSaleSlot = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SHORTSALESLOT).column).value, .shortSaleSlot)
                    .designatedLocation = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DESIGNLOC).column).value, .designatedLocation)
                    .exemptCode = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_EXEMPTCODE).column).value, .exemptCode)
                    .ocaType = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_OCATYPE).column).value, .ocaType)
                    .rule80A = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_RULE80A).column).value, .rule80A)
                    .settlingFirm = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SETTLINGFIRM).column).value, .settlingFirm)
                    .allOrNone = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_ALLORNONE).column).value, .allOrNone)
                    .minQty = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_MINQTY).column).value, .minQty)
                    .percentOffset = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_PERCENTOFFSET).column).value, .percentOffset)
                    .eTradeOnly = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_ETRADEONLY).column).value, .eTradeOnly)
                    .firmQuoteOnly = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_FIRMQUOTEONLY).column).value, .firmQuoteOnly)
                    .nbboPriceCap = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_NBBOPRICECAP).column).value, .nbboPriceCap)
                    .auctionStrategy = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_AUCTIONSTRAT).column).value, .auctionStrategy)
                    .startingPrice = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_STARTINGPRICE).column).value, .startingPrice)
                    .stockRefPrice = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_STOCKREFPRICE).column).value, .stockRefPrice)
                    .delta = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTA).column).value, .delta)
                    .stockRangeLower = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_STOCKRANGELOWER).column).value, .stockRangeLower)
                    .stockRangeUpper = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_STOCKRANGEUPPER).column).value, .stockRangeUpper)
                    .volatility = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_VOLATILITY).column).value, .volatility)
                    .volatilityType = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_VOLATILITYTYPE).column).value, .volatilityType)
                    .referencePriceType = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_REFPRICETYPE).column).value, .referencePriceType)
                    .deltaNeutralOrderType = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUORDTYPE).column).value, .deltaNeutralOrderType)
                    .continuousUpdate = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_CONTUPD).column).value, .continuousUpdate)
                    .deltaNeutralAuxPrice = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUAUXPRICE).column).value, .deltaNeutralAuxPrice)
                    .deltaNeutralConId = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUCONID).column).value, .deltaNeutralConId)
                    .deltaNeutralSettlingFirm = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUSETTLINGFIRM).column).value, .deltaNeutralSettlingFirm)
                    .deltaNeutralClearingAccount = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUCLEARINGACCOUNT).column).value, .deltaNeutralClearingAccount)
                    .deltaNeutralClearingIntent = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUCLEARINGINTENT).column).value, .deltaNeutralClearingIntent)
                    .deltaNeutralOpenClose = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUOPENCLOSE).column).value, .deltaNeutralOpenClose)
                    .deltaNeutralShortSale = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUSHORTSALE).column).value, .deltaNeutralShortSale)
                    .deltaNeutralShortSaleSlot = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUSHORTSALESLOT).column).value, .deltaNeutralShortSaleSlot)
                    .deltaNeutralDesignatedLocation = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_DELTANEUDESIGNATEDLOCATION).column).value, .deltaNeutralDesignatedLocation)
                    .trailStopPrice = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_TRAILSTOPPRICE).column).value, .trailStopPrice)
                    .trailingPercent = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_TRAILINGPERCENT).column).value, .trailingPercent)
                    .scaleInitLevelSize = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEINITLVLSIZE).column).value, .scaleInitLevelSize)
                    .scaleSubsLevelSize = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALESUBSLVLSIZE).column).value, .scaleSubsLevelSize)
                    .scalePriceIncrement = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEPRICEINCR).column).value, .scalePriceIncrement)
                    .scalePriceAdjustValue = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEPRICEADJUSTVALUE).column).value, .scalePriceAdjustValue)
                    .scalePriceAdjustInterval = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEPRICEADJUSTINTERVAL).column).value, .scalePriceAdjustInterval)
                    .scaleProfitOffset = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEPROFITOFFSET).column).value, .scaleProfitOffset)
                    .scaleAutoReset = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEAUTORESET).column).value, .scaleAutoReset)
                    .scaleInitPosition = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEINITPOSITION).column).value, .scaleInitPosition)
                    .scaleInitFillQty = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALEINITFILLQTY).column).value, .scaleInitFillQty)
                    .scaleRandomPercent = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_SCALERANDOMPERCENT).column).value, .scaleRandomPercent)
                    .outsideRth = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_OUTSIDERTH).column).value, .outsideRth)
                    .overridePercentageConstraints = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_OVERRIDEPERCONS).column).value, .overridePercentageConstraints)
                    .clearingAccount = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_CLEARACC).column).value, .clearingAccount)
                    .clearingIntent = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_CLEARINT).column).value, .clearingIntent)
                    .basisPoints = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_BASISPTS).column).value, .basisPoints)
                    .basisPointsType = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_BASISPTSTYPE).column).value, .basisPointsType)
                    .optOutSmartRouting = Util.SetNonEmptyValue(Cells(id, Columns(COLUMN_OPT_OUT_SMART_ROUTING).column).value, .optOutSmartRouting)
                End With
            
                ' combo legs
                If Cells(id, Columns(COLUMN_SECTYPE).column).value = SECTYPE_BAG And _
                Cells(id, Columns(COLUMN_COMBOLEGS).column) <> STR_EMPTY Then
                    ' create combo leg list
                    Set objTWSControl.m_contractInfo.ComboLegs = objTWSControl.m_TWSControl.createComboLegList()
                    
                    ' create order combo leg list
                    Set objTWSControl.m_orderInfo.orderComboLegs = objTWSControl.m_TWSControl.createOrderComboLegList()
                    
                    ' parse combo legs string
                    Call Util.ParseComboLegsIntoStruct(Cells(id, Columns(COLUMN_COMBOLEGS).column).value, objTWSControl.m_contractInfo.ComboLegs, objTWSControl.m_orderInfo.orderComboLegs)
                End If
            
                ' under comp
                If Cells(id, Columns(COLUMN_SECTYPE).column).value = SECTYPE_BAG And _
                Cells(id, Columns(COLUMN_UNDERCOMP).column) <> STR_EMPTY Then
                    ' create under comp
                    Set objTWSControl.m_contractInfo.underComp = objTWSControl.m_TWSControl.createUnderComp()
                    
                    ' parse under comp info
                    Call Util.ParseUnderCompIntoStruct(Cells(id, Columns(COLUMN_UNDERCOMP).column).value, objTWSControl.m_contractInfo.underComp)
                End If
                    
                ' smart combo routing params
                If Cells(id, Columns(COLUMN_SECTYPE).column).value = SECTYPE_BAG And _
                Cells(id, Columns(COLUMN_SMART_COMBO_ROUTING_PARAMS).column) <> STR_EMPTY Then
                
                    ' create smart combo routing params
                    Set objTWSControl.m_orderInfo.SmartComboRoutingParams = objTWSControl.m_TWSControl.createTagValueList()
                    
                    ' parse smart combo routing params
                    Call Util.ParseSmartComboRoutingParamsIntoStruct(Cells(id, Columns(COLUMN_SMART_COMBO_ROUTING_PARAMS).column).value, objTWSControl.m_orderInfo.SmartComboRoutingParams)
                End If
            
                ' order misc options
                Set objTWSControl.m_orderInfo.orderMiscOptions = objTWSControl.m_TWSControl.createTagValueList()
            
                'place order
                Call objTWSControl.m_TWSControl.placeOrderEx(orderId, objTWSControl.m_contractInfo, objTWSControl.m_orderInfo)
                
                ' requesting next valid id
                If Cells(id, Columns(COLUMN_ORDERID).column).value = STR_EMPTY Then
                    objTWSControl.m_orderId = objTWSControl.m_orderId + 1
                    Call objTWSControl.m_TWSControl.reqIds(1)
                End If
                
                ' update order id in table
                Cells(id, Columns(COLUMN_ORDERID).column).value = orderId
                Cells(id, Columns(COLUMN_ORDERSTATUS).column).value = STR_ORDER_SENTTOTWS
                Cells(id, Columns(COLUMN_FILLED).column).value = 0
                Cells(id, Columns(COLUMN_REMAINING).column).value = objTWSControl.m_orderInfo.totalQuantity
                Cells(id, Columns(COLUMN_AVGFILLPRICE).column).value = 0
                Cells(id, Columns(COLUMN_LASTFILLPRICE).column).value = 0
                Cells(id, Columns(COLUMN_PARENTID).column).value = 0
                    
                ActiveCell.Offset(1, 0).Activate
            Else
                MsgBox (STR_TWS_CONTROL_NOT_CONNECTED)
            End If
        Else
            MsgBox (STR_TWS_CONTROL_NOT_INITIALIZED)
        End If
        
    End Sub

    you also need to use this code.

    which you are not using currently. so it is not working as per your expectation.

    please refer the sub "demo1" in my last post.

    you will find the line "place your code here".

    so you need to call above code on that place.

    after that it will work as expected.

    you need to make the all sub properly and have decide the flow of the code.

    I understand , that when you click on "Update button" you want to loop through all the cells which contains "Update" text. Update operation taking 1 second to execute whole code. so you want to delay 1 second here. and perform this operation after every 10 seconds.

    so you need to adjust the above mentioned code. so that it executes in a flow and complete the operation.

    above mentioned code are just individual pieces. you need to adjust it to work together.

    Regards

    Deepak 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 9, 2017 7:22 AM
    Moderator
  • Nico,

    In proc Worksheet_Calculate delete both lines
     Call PlaceModifyOrder_Click

    Change the name of PlaceModifyOrder_Click (from your original code) to something like

    Sub PlaceModifyOrder(id as Long)

    and delete the lines
                Dim id As Integer
    and
                id = ActiveCell.row

    then make a new Sub PlaceModifyOrder_Click as I mentioned in my previous message in the sheets module.
    And in module1 in the code from UpdateOrders (from my previous message) change

     Debug.Print j
    in
     PlaceModifyOrder j

    Then at least something will happen with the Update cells.

    Jan

    Monday, January 9, 2017 9:24 AM
  • Thank you both very much. 

    This was solved here:
    https://social.msdn.microsoft.com/Forums/office/en-US/1a8fa169-0484-4bbe-b7a9-5bd161578cae/update-macro-triggered-by-cell-content?forum=exceldev
    • Edited by NicoPer Wednesday, April 5, 2017 3:29 AM
    • Marked as answer by NicoPer Wednesday, April 5, 2017 3:29 AM
    Monday, January 9, 2017 10:37 PM
  • Hi NicoPer,

    I understand that you got confused.

    so lets try to do each thing 1 by 1.

    first you can try to implement the loop that I shown above in my previous post.

    hope this single task will be easy for you to understand.

    place all the code of plcacemyorder with in loop and make sure that it is executing correctly.

    then we will try to implement delay and application.ontime.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 10, 2017 4:15 AM
    Moderator