none
Excel Multiple Instances and References - Cells not updating RRS feed

  • Question

  • Hello all


    We have 2 excel files:

    1) Setups File:
    - Have a datamodel.
    - Runs heavy calculations in real time from RTD data.
    - No VBA code, just pure excel/powerpivot/powerquery formulas.
    - When it runs alone it works flawlessly.

    2) Executions File:
    - No datamodel.
    - Runs ActiveX code to send orders to the broker.
    - Uses references from the Setups file to run the executions.
    - When it runs alone it works flawlessly.


    When we run both within the same instance, excel completely freezes.

    When we run them simultaneously but in different instances they both seem to work,
    but the references of the Setups file on the Executions file now don´t update in real time.


    How can we solve this?
    Is there a way to make the references of an open file running in another instance update in real time?


    (running in Win10 64b, Excel 2013 64b)

    • Edited by NicoPer Thursday, December 1, 2016 10:18 PM
    Thursday, December 1, 2016 6:06 PM

Answers

  • Hi NicoPer,

    Do you mean you reference the RTD Data from Setups files to Executions file? If so, it may cause the executions run all the time while the RTD data changes all the time. If you set a delay before you run the execution function, will it freezes? Or, I suggest you try Application.ScreenUpdating = False and DoEvents on execution file to check whether it will help.

    In addition, could you merge the two files into one file? In other words, achieve the functions in one workbook.

    Best Regards,

    Edward


    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.

    • Marked as answer by NicoPer Thursday, December 8, 2016 10:04 PM
    Friday, December 2, 2016 5:36 AM
  • Hi Edward

    So I ended up merging them as you suggested.

    As a result, the speed improved dramatically, but the error persisted.

    Then I added Application.EnableEvents = False at the beginning and Application.EnableEvents = True at the end of the Calculate (conflicting sub), and it seems to be working good now.

    Thank you very much. I appreciate your assistance.

     

    Thursday, December 8, 2016 10:09 PM

All replies

  • Hi NicoPer,

    Do you mean you reference the RTD Data from Setups files to Executions file? If so, it may cause the executions run all the time while the RTD data changes all the time. If you set a delay before you run the execution function, will it freezes? Or, I suggest you try Application.ScreenUpdating = False and DoEvents on execution file to check whether it will help.

    In addition, could you merge the two files into one file? In other words, achieve the functions in one workbook.

    Best Regards,

    Edward


    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.

    • Marked as answer by NicoPer Thursday, December 8, 2016 10:04 PM
    Friday, December 2, 2016 5:36 AM
  • Hi Edward, and thank you very much for stepping in and for your suggestions.


    Merging them is discarded because of version upgrades and conflicts.

    The other two options you suggested, the delay and Application.ScreenUpdating = False and DoEvents, I havent´t tried them, although I know that some people use the delay for executions (1 to 2 secs).

    But I´m practically a zero at VBA and don´t know how to do it. Maybe you could take a look at the execution file  and tell me how to add those/fix it.
    When I run both files in the same instance, Excel freeze on the subs "PlaceModifyOrder" and "Worksheet_Calculate", from the "Conditional Orders" sheet.

    http://www.mediafire.com/file/2zdigkl7ha75o7z

    (I´ve only added the Errhandler and MicroWarn lines.)



    • Edited by NicoPer Saturday, December 3, 2016 2:55 AM
    Friday, December 2, 2016 6:12 PM
  • Hi NicoPer,

    How could I reproduce your issue with this file?

    Best Regards,

    Edward


    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, December 5, 2016 8:05 AM
  • There are some programs that need to be installed for it to fully work in your end.
    (an ActiveX control needs to be registered, the brokers software needs to be open, etc)

    But for what I gather the placing of trades with conditional orders work like this:

    The conditional order data is first placed in the blue columns. When the "Statement" column is TRUE:
    1) copy the conditional order to the real order
    2) clear the conditional order
    3) place the order

    When both files are open in the same instance, it freezes in step 1 (but only in the first trade).
    Right now is coded so that it ignore errors and go on with the next. So it places all orders
    (including the one with the error), but freezes/loops on the first one.

    Maybe adding a delay would help, not sure what the other one you mentioned does
    (Application.ScreenUpdating = False and DoEvents)



    The strange thing is that it runs smoothly without any errors when we assign a unique Excel instance to each file.



    • Edited by NicoPer Wednesday, December 7, 2016 2:28 AM
    Monday, December 5, 2016 8:05 PM
  • Hi NicoPer,

    For Application.ScreenUpdating and DoEvents, I suggest you refer the links below:

    # Application.ScreenUpdating

    https://msdn.microsoft.com/en-us/library/office/ff193498.aspx?f=255&MSPPError=-2147217396

    # DoEvents Function

    https://msdn.microsoft.com/en-us/library/office/gg264522.aspx

    Best Regards,

    Edward


    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, December 6, 2016 7:08 AM
  • Hi Edward

    Cool, thanks. I´ll investigate them.


    Going back a bit to the original question, say we have 2 simple files in the same directory:

    1) Book1 
    in A1 there is: =NOW() (so it´s telling the time in seconds)

    2) Book2
    in A1 there is: =[Book1.xlsx]Sheet1!$A$1


    If we have them both open in the same instance, we could see how the A1 cell in Book2 update from the A1 cell in Book1.

    Now we want to open them in separate Excel instances and keep the A1 reference intact, updating automatically as if we have them both in the same Excel instance.

    How can we do it?

    Tuesday, December 6, 2016 7:06 PM
  • Hi NicoPer,

    >> If we have them both open in the same instance, we could see how the A1 cell in Book2 update from the A1 cell in Book1.

    How did you make A1 in Book2 change? I made a test with this, it will not change until I check Formulas->Calculation->Calculate Now.

    >> Now we want to open them in separate Excel instances and keep the A1 reference intact, updating automatically as if we have them both in the same Excel instance.

    If I open them in separate instances, the reference in Book2 will not work. It will not change if you change value in Book1. If you add formula from Book1 to Book2 now, you will find you could not add. I think you could not use the formula in separate instances.

    If you manually open the two books in separate, I am afraid you could not achieve your requirement for that you could not access the Book2 instance from Book1.

    Best Regards,

    Edward


    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, December 7, 2016 8:06 AM
  • Hi Edward

    Sorry, I made a bad example with NOW() trying to find a good replacement for an RTD cell, or a RTD cell derivative as I´m using it right now.

    I read somewhere about open/taking control of a workbook in another instance (with VBA).

    1) Could that approach help with keeping/updating the references in two instances?

    2) I´m using Win10 64b with Excel 2013 64b. Is there any way to make that work in another Excel version?


    • Edited by NicoPer Wednesday, December 7, 2016 1:37 PM
    Wednesday, December 7, 2016 1:36 PM
  • Hi NicoPer,

    >>I read somewhere about open/taking control of a workbook in another instance (with VBA)

    Could you share us how to take control of a workbook in another instance? If you mean you set the instance while you open the workbook, I think it is not worth. This way would not work if you manually open the workbook in another instance.

    >>Could that approach help with keeping/updating the references in two instances?

    With this approach, it would not update the reference, it just get the instance, and write value in another instance.

    Best Regards,

    Edward


    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.

    Thursday, December 8, 2016 1:50 AM
  • Hi Edward

    So I ended up merging them as you suggested.

    As a result, the speed improved dramatically, but the error persisted.

    Then I added Application.EnableEvents = False at the beginning and Application.EnableEvents = True at the end of the Calculate (conflicting sub), and it seems to be working good now.

    Thank you very much. I appreciate your assistance.

     

    Thursday, December 8, 2016 10:09 PM