• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Doubt on real time application on vba macro

V

vipuls

New Member
I have a situation where I switch from excel to another external app (using Alt+Tab) and then return to excel (using Alt+Tab). The excel is running in automatic mode and fetching data real time. What I want is that the excel should switch to manual calculation before moving to external app and should switch to automatic calculation when I return to excel. Want it to be seamless (no need to click on any cell etc.)

I have been able to do the first part where I have made a macro (found on googling) which makes the calculation mode manual before the switch to external app and use a different shortcut than Alt+Tab.

I need help on the reverse i.e. when I switch from external app to excel, excel calculation which is in manual mode should become automatic without any extra click etc. required. I was exploring events associated with workbook activate, windows activate but was not successful. Please help. Thanks.
 
vipuls
Please reread Forum Rules

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...
chandoo.org
Seems You have skipped few lines
and
after that ... continue as You have read.
 
Below is the exact issue. Apologies if it was incomplete in the earlier post:

I am an intraday stock trader using excel to manage my positions in real time and use a java application provided by external vendor for executing the trades manually. I run excel in automatic mode so that it refreshes and fetches data in real time.

I have to switch from excel to the java application and vice versa several times during the day. I face an issue of 4-5 second lag when I switch from java app to excel because excel is running in automatic mode and takes a few seconds to refresh. This is reducing my efficiency.

To overcome this, I want to make excel calculation mode manual before I switch to external java application. So that when I return back from java application, I do not face this lag. I have been able to do this using a macro with a shortcut I have defined (Ctrl+L):
Application.Calculation=xlCalculationManual
Application.SendKeys("%{TAB}")

The above replicates Alt+Tab and helps calculation mode become manual before switching to java app.

This is the issue I face: when I return back to excel using Alt+Tab, I have to further press Alt+MXA to make calculation automatic.

Thus, it is leading to a lot of button clicks: Ctrl+L and then Alt+Tab followed by Alt MXA. Even if I use a smaller shortcut, say Ctrl+A using macro to replicate Alt MXA, still I have to press Alt+Tab followed by Ctrl+A.

I need help on a faster way to do this. E.g. is there a predefined event which can detect control returning to excel so that calculation mode can become automatic seamlessly. Thanks.
 
vipuls
Did You skip... ?
How to get the Best Results at Chandoo.org
  • Use Relevant words in the Title and in the tag Box, This will aid future searches.
  • When drafting a question, try and lay out the question in a clear and concise way.
  • Try and tell the readers what is the situation, what have you tried and what do you want to achieve.
  • Don't leave out information more info is better
  • For the best/fastest results, Upload a Sample File using the "Upload a File" button at the bottom of the page.

Have You tested to add below codes in Your Excel-file's ThisWorkbook?
... then calculation could switch 'on' & 'off' automatic while activate or deactivate Your file
... not tested!
Code: 
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    Application.Calculation = xlCalculationManual
End Sub
 
Last edited:
Tried the above. Thanks for the guidance. This would be the ideal scenario if this could work. It is working if I switch between two excel sheets. But not working for my requirement (switching between excel and an external java application). To replicate, could be tested by switching between excel and google chrome.
 
vipuls
... okay
... but, why should You swap with calculation?
Could You add to Your secret code one line doevents?
... which could able that You can use other than programs Excel same time.
 
I am using an excel plugin provided by a third party to fetch real time data. The only thing I need to do is to keep excel in automatic mode so that it works. So, I might not have access to code to add do event. I will try adding do event to the macro that I am using to replicate Alt+Tab to switch to external app and see if it works. Will revert by tomorrow. Thanks.
 
You must log in or register to reply here.
Back
Top