Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by zvit » 25 May 2022, 18:10

Even after following all the tips and tricks to speed up Excel, my Excel file becomes extremely slow once it has over 30K rows. This is due to volatile formulas (formulas that recalculate every time there is a change in the workbook) that I must use.

For about 10% of the workbook that doesn't need to update in real-time, and requires VBA, I calculate via AHK by copying Excel named arrays to AHK arrays, as AHK calculates a million times faster than Excel.

This got me thinking, why not have AHK calculate ALL volatile formulas whenever a cell value is changed? In VBA, I'd use the Worksheet_Change event to call the ahk script and update cells, or via the AHK method: ComObjConnect(Sheet, Worksheet_Events).

I wonder if any of you have worked with this method? Did you run into any problems?

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by FanaticGuru » 25 May 2022, 20:23

zvit wrote:
25 May 2022, 18:10
Even after following all the tips and tricks to speed up Excel, my Excel file becomes extremely slow once it has over 30K rows. This is due to volatile formulas (formulas that recalculate every time there is a change in the workbook) that I must use.

For about 10% of the workbook that doesn't need to update in real-time, and requires VBA, I calculate via AHK by copying Excel named arrays to AHK arrays, as AHK calculates a million times faster than Excel.

This got me thinking, why not have AHK calculate ALL volatile formulas whenever a cell value is changed? In VBA, I'd use the Worksheet_Change event to call the ahk script and update cells, or via the AHK method: ComObjConnect(Sheet, Worksheet_Events).

I wonder if any of you have worked with this method? Did you run into any problems?

I have used the Worksheet_Change event before without any problems but not for a complete circumvent of Excel calculations.

I don't know what you are doing and how but it seems very unlikely that you can have everything transferred to AHK and do the calculations and back faster than Excel can do the calculations. In fact I sometimes send stuff from AHK to Excel and back to AHK just because Excel does it faster for some complicated things that are already built into Excel.

Excel is written, optimized, and compiled in some flavor of C. AHK is an interpreted scripting language where speed is not its strong suit. AHK is mostly about ease of implementation.

Maybe your VBA code is causing some type of slow down.

It would be interesting to see if you can get something to work.

Good luck!

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

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by flyingDman » 25 May 2022, 21:41

I haven't tried this either. I would be very surprised if you get a tangible benefit by doing it this way. I recall not too long ago that there was a discussion of the forum about whether you should use AHK to do the math or Excel. I thing it was about generating random numbers. Excel was, for that purpose, the better choice.
When I used to handle large spreadsheets for financial modeling, I needed to set iteration to 100 (to balance the model). I had to turn of automatic calculations because the slightest change would trigger a 100 x calculation. So, you tweak the model and only then do you push F9.
When you have AHK do the math, you better have a plan to paste those values back into excel because a 30k loop with xl.range(xxx) := yyy is likely not going to cut it. Better do it with a safearray. I believe there are several examples of that in the forum.
14.3 & 1.3.7

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by zvit » 26 May 2022, 03:53

I hear what you're all saying. I suppose there are some things that Excel might be faster, so I'd have to test.
For example, when I do a simple "by column" search in Excel (CTRL-F) for a word in a 30K range, it takes Excel 10 seconds to find it, and Excel is frozen during the search (and the range I'm searching is the first column on the sheet). But when I search via AHK with the loop below, as soon as I click my "Find" button, it shows results INSTANTLY.

Code: Select all

CheckIfInFinals:
TryAgain_CheckIfInFinals:
    InputBox, word, Check if word is in finals.
        if ErrorLevel
        Return
    if word = 
    {
        msgbox % "Enter a word."
        goto TryAgain_CheckIfInFinals
    }
    if xlApp.Range(rang_FinalWord_tblUsed).Find(word,,,,,1)
    {
        row := xlApp.Range(rang_FinalWord_tblUsed).Find(word,,,,,1).row
        puzz := Floor(xlApp.Range(rang_FinalWord_tblUsed).Rows(row).OffSet(-1,-3).Value)
        msgbox % """" word """" " EXISTS in finals, in puzz " puzz ". Exiting..."
    }
    Else
        msgbox % """" word """" " does NOT exsist in finals. Exiting..."
Return

User avatar
flyingDman
Posts: 2791
Joined: 29 Sep 2013, 19:01

Re: Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by flyingDman » 26 May 2022, 12:25

@zvit 10 seconds to find a word using Ctrl F in a 30k cell sheet is excessive. I just created a 240K cell sheet with random words (30,000 X 8). I searched for one of these words with Ctrl F and it was almost instantaneous. xl.activesheet.usedrange.Find(word,,,,,1) seemed equally fast: it took 32 ms.
14.3 & 1.3.7

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by zvit » 26 May 2022, 14:24

flyingDman wrote:
26 May 2022, 12:25
@zvit 10 seconds to find a word using Ctrl F in a 30k cell sheet is excessive. I just created a 240K cell sheet with random words (30,000 X 8). I searched for one of these words with Ctrl F and it was almost instantaneous. xl.activesheet.usedrange.Find(word,,,,,1) seemed equally fast: it took 32 ms.
I know. If I create a new Excel file and put 500K words, I also get an instant find on a search. I don't know what is wrong with my file. I don't think a CTRL-F search would be looking at formulas if I checked to search values only. So why does it take so long? Maybe that's why even regular calculations are taking longer than they should. I'll have to look into that.

User avatar
FanaticGuru
Posts: 1906
Joined: 30 Sep 2013, 22:25

Re: Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by FanaticGuru » 26 May 2022, 14:37

zvit wrote:
26 May 2022, 14:24
flyingDman wrote:
26 May 2022, 12:25
@zvit 10 seconds to find a word using Ctrl F in a 30k cell sheet is excessive. I just created a 240K cell sheet with random words (30,000 X 8). I searched for one of these words with Ctrl F and it was almost instantaneous. xl.activesheet.usedrange.Find(word,,,,,1) seemed equally fast: it took 32 ms.
I know. If I create a new Excel file and put 500K words, I also get an instant find on a search. I don't know what is wrong with my file. I don't think a CTRL-F search would be looking at formulas if I checked to search values only. So why does it take so long? Maybe that's why even regular calculations are taking longer than they should. I'll have to look into that.

If you have VBA code in the file there could be something going on in the VBA code. Also the reason you are not experiencing the problem on a fresh macro free file.

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

User avatar
zvit
Posts: 224
Joined: 07 Nov 2017, 06:15

Re: Using ahk in real-time for Excel (Worksheet_Change event) instead of formulas

Post by zvit » 26 May 2022, 18:35

This isn't an AHK issue, but I'll just update you guys.

I created the file from scratch - copying and pasting values, formatting, naming ranges, conditional formatting, etc. At each step, I tested the search's speed. If it was instant, I continued.

Even before I copied the formulas, I found the first issue. Searching 60K+ rows on any sheet was instantaneous. As soon as I put ONE conditional format, you can see what happens in the attached gif below.

However, the search is instant, even with 10 conditional formatting on a sheet, if the number of rows is less than 10K. Because I haven't even pasted any of the formulas yet, which will cause the page to slow down, even more, I don't know what to do. (I have multithreaded calculations on)

Image

Post Reply

Return to “Ask for Help (v1)”