Excel scroll issue

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
leihu
Posts: 51
Joined: 19 Mar 2020, 22:08

Excel scroll issue

Post by leihu » 15 Sep 2021, 00:40

Dear Expert,

I implemented Ctrl+Up and Ctrl+Down to scroll Excel spreadsheets.

Code: Select all

^Up::
	App := ComObjActive("Excel.Application")
	App.ActiveWindow.SmallScroll(0,1,0,0)
	return
^Down::
	App := ComObjActive("Excel.Application")
	App.ActiveWindow.SmallScroll(1,0,0,0)
	return
This works as expected. Only sometimes, instead of scrolling, Excel jumps to the very top or bottom if I press and hold the arrow key. Because jumping to the top or bottom is Excel's default behaviour of Ctrl+Up and Ctrl+Down, I think AHK must have missed some catch. The similar behavious also happens to Word.

If you'd like to test, simply copy & paste the above script, open an empty Excel spreadsheet, press and hold Ctrl+Down, Excel scrolls as normal for a while, then directly jumps to the bottom.

Can anyone help? Thanks in advnace!

HL
User avatar
boiler
Posts: 16706
Joined: 21 Dec 2014, 02:44

Re: Excel scroll issue

Post by boiler » 15 Sep 2021, 00:51

Does it do the same thing if you (for testing purposes) changed the hotkeys to not use the Ctrl key modifier? That could help confirm that the Ctrl key being held down is causing the problem.

Untested, but maybe try adding this line as the first line under each hotkey:

Code: Select all

Send, {Ctrl up}
leihu
Posts: 51
Joined: 19 Mar 2020, 22:08

Re: Excel scroll issue

Post by leihu » 15 Sep 2021, 08:43

boiler wrote:
15 Sep 2021, 00:51
Does it do the same thing if you (for testing purposes) changed the hotkeys to not use the Ctrl key modifier? That could help confirm that the Ctrl key being held down is causing the problem.

Untested, but maybe try adding this line as the first line under each hotkey:

Code: Select all

Send, {Ctrl up}
Hi boiler,

Thanks for your prompty reply.

I changed from Ctrl to Shift, and got the same problem. If I changed to Alt, Excel didn't jump but AHK created an error (0x800AC472). I think that was caused by the same reason as Ctrl.

Adding Send, {Ctrl Up} does help.

Do you have any other solution?

Or anyone else has got an idea?

Thanks very much!
alancantor
Posts: 76
Joined: 11 Jun 2019, 11:28

Re: Excel scroll issue

Post by alancantor » 15 Sep 2021, 09:26

The similar behavious also happens to Word.
Not sure if this is a good solution for you, but I use four macros to scroll in Word documents that are written in VBA. I've assigned the hotkeys to Ctrl + Alt + [the four arrow keys].

Sub ScrollUpNew()
ActiveWindow.ActivePane.SmallScroll Up:=1
End Sub
Sub ScrollDownNew()
ActiveWindow.ActivePane.SmallScroll Down:=1
End Sub
Sub ScrollLeftNew()
ActiveWindow.ActivePane.SmallScroll ToLeft:=1
End Sub
Sub ScrollRightNew()
ActiveWindow.ActivePane.SmallScroll ToRight:=1
End Sub
leihu
Posts: 51
Joined: 19 Mar 2020, 22:08

Re: Excel scroll issue

Post by leihu » 15 Sep 2021, 10:03

alancantor wrote:
15 Sep 2021, 09:26
The similar behavious also happens to Word.
Not sure if this is a good solution for you, but I use four macros to scroll in Word documents that are written in VBA. I've assigned the hotkeys to Ctrl + Alt + [the four arrow keys].

Sub ScrollUpNew()
ActiveWindow.ActivePane.SmallScroll Up:=1
End Sub
Sub ScrollDownNew()
ActiveWindow.ActivePane.SmallScroll Down:=1
End Sub
Sub ScrollLeftNew()
ActiveWindow.ActivePane.SmallScroll ToLeft:=1
End Sub
Sub ScrollRightNew()
ActiveWindow.ActivePane.SmallScroll ToRight:=1
End Sub
Does this macro need to be inserted into Word? Sorry, I'm reluctant to use this solution. I want to achieve this in AHK, or at least I know why my solution doesn't work properly. But thank you very much for your solution. I'm sure other people will like your solution. Really appreciate it.
alancantor
Posts: 76
Joined: 11 Jun 2019, 11:28

Re: Excel scroll issue

Post by alancantor » 15 Sep 2021, 11:29

I can appreciate your unwillingness to mess with Word. It adds an extra layer of complication to an already bloated program.

Yet the Word-centric approach has advantages, the main one being that the four VBA scripts become "baked" into Word. The new command will work as reliably as built-in Word commands. The new commands are stored in a file (normal.dotx) that can be easily transferred to other computers (which may or may not have AHK), new installations of Word, etc. And although the commands become integral to Word, they can be disabled or deleted; and restoring Word to its default command set is straightforward.

If the AHK approach doesn't work, the VBA solution might be worth a go!
leihu
Posts: 51
Joined: 19 Mar 2020, 22:08

Re: Excel scroll issue

Post by leihu » 17 Sep 2021, 02:03

alancantor wrote:
15 Sep 2021, 11:29
I can appreciate your unwillingness to mess with Word. It adds an extra layer of complication to an already bloated program.

Yet the Word-centric approach has advantages, the main one being that the four VBA scripts become "baked" into Word. The new command will work as reliably as built-in Word commands. The new commands are stored in a file (normal.dotx) that can be easily transferred to other computers (which may or may not have AHK), new installations of Word, etc. And although the commands become integral to Word, they can be disabled or deleted; and restoring Word to its default command set is straightforward.

If the AHK approach doesn't work, the VBA solution might be worth a go!
Hi alancantor,

Thank you once again. And yes, I agree! But the problem is that I often need to turn the feature on and off using another hotkey, so that when I want Excel's default behaviour I turn it off, and when I want to scroll I turn it on. I'm afriad that this cannot be done with macros. BTW, other key combinations (such as Ctrl + Alt +) have all been used by something else.

I really want to fix this problem coz I use it every single day, and this jumping issue really annoying me.

Can any Expert on the forum please spend some time to investigate? Thank you very much.

HL
ludamo
Posts: 44
Joined: 25 Mar 2015, 02:21

Re: Excel scroll issue

Post by ludamo » 19 Sep 2021, 01:27

What you might try is the following:

Code: Select all

^Up::
	if not IsObject(App)
		App := ComObjActive("Excel.Application")
	App.ActiveWindow.SmallScroll(0,1,0,0)
	return
^Down::
	if not IsObject(App)
		App := ComObjActive("Excel.Application")
	App.ActiveWindow.SmallScroll(1,0,0,0)
	return
You are probably correct in implying that AHK is missing some of the HotKeys because if it is instantiating the Object each time perhaps it is running out of space or memory. Try just instantiating it once and then checking for the object. I think also the object may need to be released i.e. set App := "" after you have finished using it. I have done this in other code by using SetTimer to run code that checks for when you release e.g. the Control Key and then sets App := "".
leihu
Posts: 51
Joined: 19 Mar 2020, 22:08

Re: Excel scroll issue

Post by leihu » 22 Sep 2021, 21:42

ludamo wrote:
19 Sep 2021, 01:27
What you might try is the following:

Code: Select all

^Up::
	if not IsObject(App)
		App := ComObjActive("Excel.Application")
	App.ActiveWindow.SmallScroll(0,1,0,0)
	return
^Down::
	if not IsObject(App)
		App := ComObjActive("Excel.Application")
	App.ActiveWindow.SmallScroll(1,0,0,0)
	return
You are probably correct in implying that AHK is missing some of the HotKeys because if it is instantiating the Object each time perhaps it is running out of space or memory. Try just instantiating it once and then checking for the object. I think also the object may need to be released i.e. set App := "" after you have finished using it. I have done this in other code by using SetTimer to run code that checks for when you release e.g. the Control Key and then sets App := "".

Hi ludamo,
Thanks for your reply and sorry for the delay. Unfortunately, this solution doesn't solve the problem, and also it creates new problems. After I quit Excel and wait for some time and reopen an Excel spreadsheet, Ctrl + Arrow will generate an error, because App is still an object, but the real object has been changed.

I'm afraid I'll give up until AHK devolopers solve this bug in AHK.

Thank you guys for answering my question.
User avatar
flyingDman
Posts: 2776
Joined: 29 Sep 2013, 19:01

Re: Excel scroll issue

Post by flyingDman » 22 Sep 2021, 22:49

Your original script works fine here. I cannot reproduce the error. Unlikely it is a bug with AHK.
14.3 & 1.3.7
User avatar
FanaticGuru
Posts: 1905
Joined: 30 Sep 2013, 22:25

Re: Excel scroll issue

Post by FanaticGuru » 23 Sep 2021, 17:32

flyingDman wrote:
22 Sep 2021, 22:49
Your original script works fine here. I cannot reproduce the error. Unlikely it is a bug with AHK.

I also cannot replicate the problem. Script works fine for me.

I can also change to Shift or Alt with no problem. No Error (0x800AC472) even if I try. It is also really unusual that changing to Shift could still causes the sheet to jump to the top or bottom as Shift-Arrow is not the native action to move to the top or bottom.

You say you turn it off and on when you need to scroll. Maybe that code is not working properly or as expected. Any other AHK code you have running could play a part in the behavior.

Maybe you have another script, macro, add-on, or program running that is interfering.

Also Error (0x800AC472) is pretty unusual. That generally means that Excel has shut down communication with other programs. Mostly pretty weird stuff can cause that. Licensing problems, security issues, certificates, etc. Or if the COM object table is overloaded by something like having many many versions of Excel running when ComObjCreate("Excel.Application") is used repeatedly without closing each process or anything that could create an unreasonable number of COM object table entries.

Long story short, it is unlikely a bug with AHK.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts
AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon
Hotstring Manager - Create and Manage Hotstrings
[Class] WinHook - Create Window Shell Hooks and Window Event Hooks
leihu
Posts: 51
Joined: 19 Mar 2020, 22:08

Re: Excel scroll issue

Post by leihu » 26 Sep 2021, 08:12

FanaticGuru wrote:
23 Sep 2021, 17:32
flyingDman wrote:
22 Sep 2021, 22:49
Your original script works fine here. I cannot reproduce the error. Unlikely it is a bug with AHK.

I also cannot replicate the problem. Script works fine for me.

I can also change to Shift or Alt with no problem. No Error (0x800AC472) even if I try. It is also really unusual that changing to Shift could still causes the sheet to jump to the top or bottom as Shift-Arrow is not the native action to move to the top or bottom.

You say you turn it off and on when you need to scroll. Maybe that code is not working properly or as expected. Any other AHK code you have running could play a part in the behavior.

Maybe you have another script, macro, add-on, or program running that is interfering.

Also Error (0x800AC472) is pretty unusual. That generally means that Excel has shut down communication with other programs. Mostly pretty weird stuff can cause that. Licensing problems, security issues, certificates, etc. Or if the COM object table is overloaded by something like having many many versions of Excel running when ComObjCreate("Excel.Application") is used repeatedly without closing each process or anything that could create an unreasonable number of COM object table entries.

Long story short, it is unlikely a bug with AHK.

FG

Hi flyingDman and FanaticGuru,

Thanks both for testing my script. If you both cannot reproduce the problem, the problem should be in my script. Shouldn't be the piece that I'm focused on, but somewhere else. I'll do more investigations. Thanks once again.

HL
Post Reply

Return to “Ask for Help (v1)”