Making excel application more efficient

vipuls

New Member
Joined
Oct 7, 2020
Messages
2
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.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,709
.
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

New Member
Joined
Oct 7, 2020
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,019
Messages
5,545,527
Members
410,689
Latest member
ConfuzzledThomas
Top