Making excel application more efficient

[vipuls]

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.

 

[Logit]

.
You can use the MACRO RECORDER that is built-in to Excel .... this will record your key strokes and auto-create a macro that you can
execute anytime required.

Try recording a macro that replicates your manual key strokes when returning to Excel ... then include that code run by a command button.

 

[vipuls]

Thanks for giving direction. Have this code now after record macro and some improvisation:

Application.Calculation=xlManual
Application.SendKeys ("%{TAB}")
Application.SendKeys("%{TAB}")
Application.Calculation=xlAutomatic

The first two rows are run to switch from excel to java app. Trying to figure out a way to run the next two rows from outside excel (since control shifts to java app where I need to do some stuff like buying/selling which is different eact time before reverting to excel).

Might be possible using vb script for last 2 rows and associating some keyboard shortcut to it. I know macros but have limited exposure to vb scripting. Any specific directions on how to do it is welcome. Will try figuring this out myself as well.

 

[Logit]

Untested here :

Close an application using VBScript

How to run an external program within VB Script | Remote Administration For Windows
https://www.techwalla.com/articles/how-to-run-exe-in-vbscript