Function in external file with try/catch/Com Interface MUCH SLOWER

[aircooled]

I had problems with a series of COM Interface calls. They trigged an error:

Code: Select all

"Error: 0x80010001 - Call was rejected by callee"

which I worked around by inserting ridiculously large delays between transactions, up to 1200-1500ms.

So try/catch for each and every Excel COM Interface was implemented in the code and it solved the problem allowing to reduce the delays to 10ms. Impressive!

Code: Select all

Sleep, 10
    loop
    {
	try (X1.Range("A1").Value := "=ROW()", error:=0)
	catch error
	sleep 10
	if (!error)
	break
	}  ; INSERT ROW NUMBER FORMULA IN CELL A1 
Sleep, 10
    loop
    {
	try (X1.Range("A1").Copy, error:=0)
	catch error
	sleep 10
	if (!error)
	break
	}
  ; COPY FORMULA IN FIRST CELL
Sleep, 10
    loop
    {
	try (X1.Range("A2:A" . SHEETS_COUNT).Select, error:=0)
	catch error
	sleep 10
	if (!error)
	break
	}  ; SELECT REGION TO COPY TO

This works perfect and NEVER fails. I can loop 10 times a loop with 50 calls in total 500 Excel COM Interface calls - each of them with try/catch - without problem in few seconds.

However, I don't want to write all that code for each Excel COM Interface call and would like to have a function in an external .ahk file with the try/catch functionality.
And here come the bad news. The external function file requires once again to have delays of 500-600ms or more or it will fail way too often.

Script code with function calls:

Code: Select all

X1 := ComObjCreate(("Excel.Application"))  ; CONNECT TO THE EXCEL WORKBOOK
X1.Workbooks.Open("C:\Users\aircooled\Private\My_Book.xlsx")
Sleep, 500
X1.Visible:=True  ; OPENS THE EXCEL WORKBOOK
Sleep, 500
Loop, 4
{
    Sleep, 500
    TTL := getFreeMem(X1.Sheets("Sheet2").Range("C" . A_Index).Select)
    Sleep, 500
    TTL := UpdateCell(X1.Sheets("Sheet2").Range("C" . A_Index).Value := A_Index)
} 

#Include C:\Users\aircooled\Private\\My_Function2.ahk

Function definitions in external file My_Function2.ahk:

Code: Select all

getFreeMem(ByRef THISLINK)
{
sleep 200
loop
	{
		try (A_VAR := THISLINK)
		catch error

		sleep 500
		if (!error)
			break
	}
	return 0
}

UpdateCell(ByRef THISLINK)
{
sleep 200
loop
	{
		try (THISLINK, error:=0)
		catch error
			sleep 500
		if (!error)
			break
	}
	return 0
}

The error code now if I reduce the delays, is the same I had in the beginning, and it can, of course be solved with ridiculously long delays.

Code: Select all

"Error: 0x80010001 - Call was rejected by callee"

[Xeo786]

Every time you change single cell content in excel and is part of some function which makes excel to calculate and update results
normally it does not take time but .. two conditions with I am familiar

1) your sheet might be huge
2) sheet consist function doing enormous calculation,

with above condition sometime processor take long because of so many cells has functions and excel has to check every function and update the result, or one context excel function can hang the excel app

with your code every cell excel trigger calculation and cell update, which mean `4 loops = 4x wait` but you can turn it into `1x wait` by following code

Code: Select all

X1 := ComObjCreate(("Excel.Application"))
X1.Workbooks.Open("C:\Users\aircooled\Private\My_Book.xlsx")
X1.Visible:=True  ; make it visible 
X1.ScreenUpdating := False
X1.Calculation := -4135  ; xlManual = -4135
Loop, 4
	X1.Sheets("Sheet2").Range("C" . A_Index).Value := A_Index
X1.Calculation := -4105  ; xlAutomatic = -4105
X1.ScreenUpdating := True

or you can simply excel sheet and remove non required functions coz now you have AHK to deal with few kind of calculation

[aircooled]

Hi Xeo786 nad thanks for your proposal.

At the moment I am testing with a fresh Excel sheet with no formulas on it only one sheet with a manually inserted index 1....50 in A1:A50 and some random numbers B1:B50
This is just to prove that the issue is not from the Excel being heavy.

I will study and test your suggestion, just wanted to clarify the above.

So the issue here can be summarized:
1) The Excel update performed with try/catch inside the code in the same file updates extremely fast in the order of 10 ms tops per Excel COM Interface update.
2) The same exact code executed from an external .ahk function file (only difference) same amount of loops, same statements, is much slower.

[Xeo786]

So the issue here can be summarized:
1) The Excel update performed with try/catch inside the code in the same file updates extremely fast in the order of 10 ms tops per Excel COM Interface update.
2) The same exact code executed from an external .ahk function file (only difference) same amount of loops, same statements, is much slower.

have you tried removing all `sleep`, `sleep, 200` simply mean wait 200 ms and your both function in external .ahk is waiting for 700 ms for no purpose

edit:
oooh your function are way ahead for my understanding by `byref` in function `getFreeMem(ByRef THISLINK)` and why you putting `select` as input to some byref

[aircooled]

Yes I have tried to remove the sleeps and then the error is trigged. The error pops up when too many COM Interface calls are done in too short period of time.
Also it doesn't matter if I have sleep in the code AND in the external file. What counts is the total amount of sleep before a transaction or statement.
As soon as I slow down the process with sleeps I avoid the error:

Code: Select all

"Error: 0x80010001 - Call was rejected by callee"

Byref passes the pointer to the function : https://www.autohotkey.com/docs/Functions.htm#param

"Select" focuses on the given cell and don't do any calculation. This statement trigs an error if not enough sleep is given proving that it is not the calculations that trig an error.

[miracle]

too be honest. I don't think that error has something to do with calling the COM methods to fast.

Look at https://stackoverflow.com/questions/20514548/how-to-solve-exceptioncall-was-rejected-by-callee-exception-from-hresult-0x8

I use Excel COM daily and sometimes there are errors but i never encountered this so often as you do. I even don't use as many sleeps as yours. Maybe you can try the ready state of excel.

edit: Or try the automation when Excel is visible and see what happens.

[aircooled]

Here are my findings. First I implement a very simple function in an external file and perform a simple addition.
It executes fast and without errors so that rules out external function per se as the culprit in the errors I get:

External function:

Code: Select all

Add(x, y)
{
    return x + y   ; "Return" expects an expression.
}

Source code. Works perfect. Can have sleeps down to 1 ms and never fails. Executed many many times:

Code: Select all

Loop, 70
{
    Random, VAR1, 1, 100
    Random, VAR2, 1, 100
    TTL := Add(VAR1, VAR2)
    ToolTip, >%VAR1%<>%VAR2%<>%TTL%<
}

Now I will write the 3 variables above in a fresh Excel sheet with no formulas or data, just write 70 rows with simple numbers, 3 numbers in each row different columns.
Start with: 10ms sleep. Gives huge amount of errors.

Code: Select all

"Error: 0x80010001 - Call was rejected by callee"

Change then all sleep to 1000ms and start decreasing until errors start appearing. Stable version with the shortest possible sleep shown below.
Observe that the sleeps are MANY MANY times larger than the simple sum above and also much larger than when coding the try/catch directly in the code as the last example at the bottom:

External function with try /catch to use with Excel COM Interface calls:

Code: Select all

UpdateCell(ByRef THISLINK)
{
sleep 250
loop
	{
		try (THISLINK, error:=0)
		catch error
			sleep 200
		
		if (!error)
			break
	}
}

Source code with Excel COM Interface calls:

Code: Select all

X1 := ComObjCreate(("Excel.Application"))  ; CONNECT TO THE EXCEL WORKBOOK
X1.Workbooks.Open("C:\Users\aircooled\Private\My_Book.xlsx")
Sleep, 500
X1.Visible:=True  ; OPENS THE EXCEL WORKBOOK
Sleep, 500
Loop, 70
{
    Random, VAR1, 1, 100
    Random, VAR2, 1, 100
    TTL := Add(VAR1, VAR2)
    TTL1 := UpdateCell(X1.Sheets("Sheet2").Range("C" . A_Index).Value := TTL)
    Sleep, 250
    TTL := UpdateCell(X1.Sheets("Sheet2").Range("B" . A_Index).Value := VAR2)
    Sleep, 250
    TTL := UpdateCell(X1.Sheets("Sheet2").Range("A" . A_Index).Value := VAR1)
    Sleep, 250
}

If the sleep above are decreased then errors start coming up, the sleep above are the limit for stable execution.

Still the fact remains that when I put the try/catch directly in the code repeating the same try/catch block for each Excel COM Interface call, then I can decrease the sleep to 10ms and it NEVER fails.
So it seems like the try/catch is NOT WORKING in the external function since I still get the errors. I shouldn't get the errors with the external function. It should loop until the COM Interface call succeeds as it does when the try/catch is directly in the code. As coded below I can see during execution in the update that it flickers and stays for some ms in a cell, that's when the try/catch trigs an error and it loops a few times. That flickering or loop never happens in the external function. It simply trigs the error at the first fail and crashes.

This is how it looks like when I code the try/catch blocks directly in the code, only that I must write such a block for each call. But it executes extremely fast and NEVER fails.

Code: Select all

loop
    {
	try (X1.Range("J" . A_Index).Value := A_Now, error:=0)
	catch error
	sleep 10
	if (!error)
	break
     }

[flyingDman]

If this exact code:

Code: Select all

Xl := ComObjActive("Excel.Application") 				          
Loop, 70
	{
    Random, VAR1, 1, 100
    Random, VAR2, 1, 100
    TTL := Add(VAR1, VAR2)
	xl.range("A" a_index).value := Var1
	xl.range("B" a_index).value := Var2
	xl.range("C" a_index).value := TTL
	}
return

Add(x, y){
    return x + y   									; "Return" expects an expression.
}

Creates errors, then there is something wrong with your PC or with the Excel install. There should be absolutely no reason to include "sleeps" or to use a "try / catch" construct. I have scripts that are 100 x more complicated than this and they run smoothly without sleeps or try/catch.

[aircooled]

If this exact code:

Code: Select all

Xl := ComObjActive("Excel.Application") 				          
Loop, 70
	{
    Random, VAR1, 1, 100
    Random, VAR2, 1, 100
    TTL := Add(VAR1, VAR2)
	xl.range("A" a_index).value := Var1
	xl.range("B" a_index).value := Var2
	xl.range("C" a_index).value := TTL
	}
return

Add(x, y){
    return x + y   									; "Return" expects an expression.
}

Creates errors, then there is something wrong with your PC or with the Excel install. There should be absolutely no reason to include "sleeps" or to use a "try / catch" construct. I have scripts that are 100 x more complicated than this and they run smoothly without sleeps or try/catch.

OK, I understand that. I tried your code and without "sleeps" of some ms it fails. With "sleeps" it runs smoothly. Also removing the COM Interface calls from your last code suggestion and running only the Add function call it runs flawlessly even if I execute 1000 loops, so the issue only shows up when adding COM Interface calls to the external function.

Could there be more root causes involved than just something wrong with my PC or the Excel installation? like how powerful the cpu is or how much ram there is or the ahk version or a combination of these and other parameters? IMHO we have too little information and it is too early at this point in time to blame the PC or Excel installation.

The real issue now is: why code that works perfectly in the script itself does not work when transferred to an external function. Same PC, same Excel, same data, etc..

Inspired in your "no-sleep" setup I tested this code in the script itself. It works perfect and performs 1000 selects and 1000 updates in a few seconds, tested a total of 9 times.

Code: Select all

X1 := ComObjCreate(("Excel.Application"))
X1.Workbooks.Open("C:\Users\aircooled\Private\My_Book.xlsx")
X1.Visible:=True
My_Index := 1
Loop, 1000
{
    loop
    {
	try (X1.Sheets("Sheet2").Range("C" My_Index).Select, error:=0)
	catch error
	if (!error)
	break
	}

    loop
    {
	try (X1.Sheets("Sheet2").Range("C" My_Index).Value := My_Index, error:=0)
	catch error
	if (!error)
	break
	}
My_Index += 1 
}

I move the same exact code to an external .ahk function file and it fails.

In the external function I see no difference having the try/catch or not, it is as if the try/catch wasn't there, with try/catch or without it fails after a split second in the first COM Interface call. It never goes beyond the first COM Interface call. Theoretically it should loop until error := FALSE since it is the only break criteria. But that is not happening.

I tested the following scenarios:
https://postimg.cc/gryjypBD

1) NO try/catch, no "sleeps", in script: ERROR
2) NO try/catch, no "sleeps", in external function: ERROR

3) NO try/catch, long "sleeps" in script: SUCCESS
4) NO try/catch, long "sleeps" in external function: SUCCESS (*)

5) try/catch, no "sleeps", in script: SUCCESS
6) try/catch, no "sleeps", in external function: ERROR

7) try/catch, long "sleeps" in script: SUCCESS
8) try/catch, long "sleeps" in external function: SUCCESS (*)

(*) These are very unstable solutions since I have to guesstimate the minimum necessary "sleep". If another day the process is slower then the run will fail.

The issue I want to understand if why a perfectly working code (as I actually have in 5) above) in the script does not work when implemented in an external function.

[flyingDman]

Now that you have established that the script that I posted creates errors, the conclusion must be that there is something interfering with the execution of your code. That is the real issue. Not whether it works / does not work if used in an external function (which I have not tested because, at this stage, it is not relevant). Google the error code. Confirm that you have a valid Excel installation / license. The issue you have is IMHO beyond the scope of this forum as it is not related to the code.

[aircooled]

Thanks for your response.

At this point when we don't know what version of Excel you and I are using, or AHK version, or OS or other parameters I find a little too early to agree with your conclusion.

I appreciate your effort, thank you for your time. Now I would gladly like to hear a third opinion if there is one.

Again. Thank you.

The issue I would like to solve is why a perfectly working code (as I actually have in 5) above) in the script does not work when implemented in an external function.

[just me]

So it's time to show us your complete 'perfectly working' code and your complete 'not perfectly working' code, and to tell us which Excel, AHK, and OS versions you are running.

[flyingDman]

For the sake of completeness: I am running Excel 365 version 1912, build 12325_20344, latest monthly updated. I am running on a 4 year old i5-5200U laptop with 8gb of RAM and Windows 10 1909. AHK version is 1.1.32.00 Unicode 64 bit. I wrote my first script involving COM for Excel 10 years ago.

[aircooled]

Yes, sure, no problem.

This script executes in a few sec never fails, have now tested 20 times.

Code: Select all

X1 := ComObjCreate(("Excel.Application"))
X1.Workbooks.Open("C:\Users\aircooled\Private\My_Book.xlsx")
X1.Visible:=True
My_Index := 1
Loop, 1000
{
    loop
    {
	try (X1.Sheets("Sheet2").Range("C" My_Index).Select, error:=0)
	catch error
	if (!error)
	break
	}

    loop
    {
	try (X1.Sheets("Sheet2").Range("C" My_Index).Value := My_Index, error:=0)
	catch error
	if (!error)
	break
	}
My_Index += 1 
}

Try/catch functionality is now moved to an .ahk file to make an external function call. Blank Excel worksheet, no formulas, only this data in the whole workbook.

The script:

Code: Select all

#NoEnv
SetWorkingDir %A_ScriptDir%
CoordMode, Mouse, Window
SendMode Input
#SingleInstance Force
SetTitleMatchMode 2
#WinActivateForce
SetControlDelay 1
SetWinDelay 0
SetKeyDelay -1
SetMouseDelay -1
SetBatchLines -1

Macro3:
X1 := ComObjCreate(("Excel.Application"))
X1.Workbooks.Open("C:\Users\aircooled\Private\My_Book.xlsx")
X1.Visible:=True
Sleep, 500
My_Index := 1
Loop, 4
{
    TTL1 := UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Select)
    TTL1 := UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Value := My_Index)
    My_Index += 1
}
Sleep, 400
Return

#Include C:\Users\aircooled\Private\External_Functions.ahk

And the external function file: (Fails after a split second in the first COM Interface call. Never passes by the first call. "Error: 0x80010001 - Call was rejected by callee")

Code: Select all

UpdateCell(ByRef THISLINK)
{
loop
	{
		try (THISLINK, error:=0)
		catch error
		if (!error)
			break
	}
}

With sleep never fails, no issues, the loop is executed once, it succeeds the first time always:

Code: Select all

UpdateCell(ByRef THISLINK)
{
loop
	{
		try (THISLINK, error:=0)
		catch error
			sleep 500
		if (!error)
			break
	}
}

Windows 10 Enterprise
Excel Office 365 v. 1908 build 11929.20562
AHK v. 1.1.30.03
Thinkpad 8GB RAM Intel(R) Core(TM) i5-8350U CPU @ 1.90GHz 64bit

All my equipment is updated, checked and verified weekly via intranet.
Office 365 was reinstalled when issue was seen 4 weeks ago.

[just me]

Code: Select all

    TTL1 := UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Select)
    TTL1 := UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Value := My_Index)
...
UpdateCell(ByRef THISLINK)
{
loop
	{
		try (THISLINK, error:=0)
		catch error
		if (!error)
			break
	}
}

What do you expect the function will do?

Code: Select all

UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Select)

will pass the result of

Code: Select all

X1.Sheets("Sheet2").Range("C" My_Index).Select)

to the function, if at all.

Code: Select all

UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Value := My_Index)

will pass the result of

Code: Select all

X1.Sheets("Sheet2").Range("C" My_Index).Value := My_Index

as well.

[aircooled]

I expect the function to perform the COM Interface call sent as parameter.

This COM Interface call will just highlight the cell:

Code: Select all

UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Select)

[Don't care about the function name. I've been reducing the script more and more to circle in the issue, the names are a legacy of the original script.]

This call will just insert a value in cell "C" My_Index which happens to be highligthed, could be any other cell, no connection between statements:

Code: Select all

UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Value := My_Index)

I am trying to figure out why the code works just as expected directly in the script but not in the extern function unless I add a sleep.
Those simple statements are enough to reveal the issue.

It is not that the script is not doing what I want in terms of logic.
It is just that the ahk functionality (with COM Interface calls) works just fine directly in the script but the same code does not work when called in an external .ahk function.

I have already proved (can do it again) that an external function that does a simple sum works just fine. The issue is only when COM Interface calls are involved.

Summary:
External functions with other than COM Interface calls work fine.
COM Interface calls with try/catch work just fine and FAST (no sleep) when directly in the code. When the exact same logic is put in an external function it fails, unless sleep are added.

[just me]

Code: Select all

UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Select)

Again, this will perform the COM interface call an pass the result to the function UpdateCell().

[aircooled]

Code: Select all

UpdateCell(X1.Sheets("Sheet2").Range("C" My_Index).Select)

Again, this will perform the COM interface call an pass the result to the function UpdateCell().

No.

Since the parameter is passed byRef the parameter is a reference or pointer to the COM Interface call that is executed inside the function by the function.

The function will not return anything just execute the COM Interface call.

[just me]

The param is the result (return value) of a COM interface call.

Code: Select all

UpdateCell(X1)

would pass the interface pointer.

[aircooled]

The param is the result (return value) of a COM interface call.

Code: Select all

UpdateCell(X1)

would pass the interface pointer.

It performs the COM Interface calls without ByRef but nothing changes. Still the sleep are necessary or it fails.

This is the least amount of sleep it succeeded all the way through otherwise it fails after some calls:

Code: Select all

 UpdateCell(THISLINK)
{
sleep 200
loop
	{
		try (THISLINK, error:=0)
		catch error
			sleep 200	
		if (!error)
			break
	}
}

Also it still is as if the catch wasn't there. This version made no difference, executed ok with the same sleep as above and failed as soon as the sleep were decreased:

Code: Select all

UpdateCell(THISLINK)
{
sleep 200
		try (THISLINK, error:=0)
			sleep 200
}

As I see it the function should loop until the only exit condition "(!error)" is true. This is not happening in the function.

With the try/catch in the program it is possible to observe how it momentarily stops for a couple of ms in a cell and flickers. It is repeating a call that failed.