Excel... UDF error handling

[CE101]

Back in 2012 I wrote a large program that makes extensive use of the UDFs in the then-current "excel.au3" include-file. With the release of AutoIT vs3.3.12.0 (on June1/2014) the "excel.au3" include-file was rewritten. Functions and/or parameters were renamed, removed or added. Now I am experimenting with the new UDFs. I am using the current version of AutoIT -- vs3.3.14.2

Right now I am experimenting with the UDF  _Excel_RangeCopyPaste() and how to handle error conditions.

The basic format is:  

_Excel_RangeCopyPaste ( $oWorksheet, $vSourceRange [, $vTargetRange = Default [, $bCut = False [, _ 
    $iPaste = Default [, $iOperation = Default [, $bSkipBlanks = False [, $bTranspose = False]]]]]] )

The UDF documentation shows there are 7 error conditions.

After opening Excel (with ObjCreate) and creating two new workbooks (with _Excel_BookNew), the program continues something like this:

$oSourceSheet = $oExcelWB_X1.Worksheets("S01")
$oTargetSheet = $oExcelWB_X2.Worksheets("Sheet1")

_Excel_RangeCopyPaste($oSourceSheet, $oSourceSheet.Range("A2:F2"), $oTargetSheet.Range("A1"))
If @error  <> 0  Then
    If @error       = 1  Then
        $ErrorMsg3  = "$oWorksheet is not an object or not a worksheet object"
    ElseIf @error   = 2  Then
        $ErrorMsg3  = "$vSourceRange is invalid. COM error-code (@extended) = " & @extended
    ElseIf @error   = 3  Then
        $ErrorMsg3  = "$vTargetRange is invalid. COM error-code (@extended) = " & @extended
    ElseIf @error   = 4  Then
        $ErrorMsg3  = "Error occurred when pasting cells. COM error-code (@extended) = " & @extended
    ElseIf @error   = 5  Then
        $ErrorMsg3  = "Error occurred when cutting cells. COM error-code (@extended) = " & @extended
    ElseIf @error   = 6  Then
        $ErrorMsg3  = "Error occurred when copying cells. COM error-code (@extended) = " & @extended
    ElseIf @error   = 7  Then
        $ErrorMsg3  = "$vSourceRange and $vTargetRange can't be set to keyword Default at the same time"
    Else
        $ErrorMsg3  = "Unknown error???  @error = " & @error   &  ". @extended = " & @extended
    Endif
    $ErrorMsg1  = "** ABEND. Error in " & $FuncName & ", Line# " & @ScriptLineNumber
    $ErrorMsg2  = "_Excel_RangeCopyPaste() failed. @error = " & @error
    UDF104_DebugOut(@ScriptLineNumber, $ErrorMsg1)
    UDF104_DebugOut(@ScriptLineNumber, $ErrorMsg2)
    UDF104_DebugOut(@ScriptLineNumber, $ErrorMsg3) 
    Exit
Endif

The _Excel_RangeCopyPaste as coded above works fine, so there's no error message.

Then I tried triggering some of the error conditions mentioned above.

To test @error #1.
I set     $oSourceSheet = "??????????????????"
Result:  I got an AutoIT generated msg, "Variable must be of type Object", pointing at $oSourceSheet.Range
See ScreenShot01.

To test @error #2.
I changed the source range to "??????A2:F2"
Result:  I got an AutoIT generated msg, "The requested action with this object has failed", pointing at $oSourceSheet.Range.       
See ScreenShot02.

To test @error #3.
I set   $oTarget = "??????????????????"
Result:  I got an AutoIT generated msg "Variable must be of type Object", pointing at $oTargetSheet.Range
See ScreenShot03.

COMMENT:
I was surprised that AutoIT generates its own messages and abends in these cases and does not allow the calling program to handle the error conditions, as was the case with the old UDFs. I looked into the new UDFs and see that something new has been introduced that was not present in the old UDFs -- an "ON ERROR" handler, named  __Excel_COMErrFunc.  So it seems that the new UDFs have entirely taken over error-handling.

CARRYING ON WITH MY TESTS:
 
I created another instance of Excel and with it I created a 3rd workbook, named $oExcelWB_X3.
Then I used _Excel_RangeCopyPaste to go from Workbook1 to Workbook3.
    $oSourceSheet = $oExcelWB_X1.Worksheets("S01")
    $oTargetSheet = $oExcelWB_X3.Worksheets("Sheet1")
    _Excel_RangeCopyPaste($oSourceSheet, $oSourceSheet.Range("A2:F2"), $oTargetSheet.Range("A1"))

The operation failed (not sure why), however I was surprised that this time AutoIT did not abend with its own error message. Instead it allowed my program to issue the following message (for @error 6):  ""Error occurred when copying cells. COM error-code (@extended) = " & @extended"
See ScreenShot04.

This made me very curious.
Sometimes AutoIT abends and and sometimes it allows the program to do the error handling.  ?????

I don't see anything in this UDF (_Excel_RangeCopyPaste) that would explain when it does and when it doesn't.

So then I experimented with  _Excel_BookOpen  and  _Excel_BookNew.
And I found that AutoIT allows the program to do the error handling.

Then I looked at all the 32 UDFs and see that most of them reference the special "on error" UDF (__Excel_COMErrFunc). It is not referenced in the following six UDFs:  
    ; _Excel_BookList
    ; _Excel_ColumnToLetter
    ; _Excel_ColumnToNumber
    ; _Excel_Open
    ; _Excel_RangeFind
    ; _Excel_SheetList

QUESTIONS:
(1) Why is the special "on error" UDF not referenced in the six UDFs mentioned above.  
(2) Re: _Excel_RangeCopyPaste ....  Why doesn't AutoIT allow the program to do the error handling for all error conditions. Why does it step in on some and stay away on others.  
(3) Is there a certain "class" of errors that AutoIT handles and another "class" that it leaves for the program.
(4) Going forward with my programming, is there a way for me to know which errors I need handle in the program and which ones I can rely upon AutoIT to handle?
(5) Why did _Excel_RangeCopyPaste fail (error 6) in the case where I was trying to copy/paste a range from Workbook1 to Workbook3?  Is it because Workbook3 is in a different instance of Excel?

Any suggestions would be greatly appreciated.

 

Edited July 18, 2016 by CE101

[water]

#1: $oSourceSheet = "??????????????????"  is evaluated before calling the function. As there is no COM error handler in place at that time and hence the script crashes. WAD.

#2: Same as #1

#3: Same as #1

When the error occurs in a function then it is handled by the COM error handler. If it occurs while evaluating the function call then you need to add a COM error handler yourself.

The COM error handling after 3.3.12.0 is far from being perfect (I already told Jon about that). Let's see what the next version brings in this respect. For the time being we have to make the best out of it.

[CE101]

Hi Water:

Thank you for getting back to me on questions 1 to 4...

> When the error occurs in a function
> then it is handled by the COM error handler.
> If it occurs while evaluating the function call
> then you need to add a COM error handler yourself.

I've taken a closer look at the code in _Excel_RangeCopyPaste and see what you mean.
Makes sense.

How about question #5....
Why did _Excel_RangeCopyPaste fail (error 6) in the case where I was trying to copy/paste a range from Workbook1 to Workbook3?  Is it because Workbook3 is in a different instance of Excel?

FYI, I also tried copying a sheet from Workbook1 to Workbook3 (using  _Excel_SheetCopyMove).
It too failed --- with an error #5... "Error occurred when copying/moving the sheet".

 

[water]

#5: Seems to be a limitation of Excel. http://excel.tips.net/T003404_Copying_Between_Instances_of_Excel.html

[CE101]

Hi Water:

Thank you very much for looking into question #5 and for the link to
http://excel.tips.net/T003404_Copying_Between_Instances_of_Excel.html  

Actually I saw this article a few days ago. However the author (Allen Wyatt) is talking about manually copy/pasting formulas from one instance to another.

I just tried it manually.
(1) Created 2 workbooks (Book 1 and 2)  in instance #1
(2) Created 1 workbook in instance #2.
(3) In WB1 ... entered some numbers in A1 to A3 and then summed them with a formula in A4.
(4) Copied A1:A4 from WB1 to  WB2
(5) Copied A1:A4 from WB1 to  WB3

Result:
(6) In WB2 the formula remained a formula.
(7) In WB3 the formula became a value.

So you really can copy/paste manually from one instance to another (except that formulas get converted to values).  Then why can't the same thing be done through the UDF and COM???.  With the UDF, nothing gets copy/pasted.

 

 

 

 

[water]

Don't know if this is possible at all (not all methods/properties/collections are necessarily exposed as COM by Microsoft).
I'm not sure I did ever test cross-instance-copying. The rewritten Excel UDF is available since version 3.3.12.0 (June, 2014). This is the first question regarding this issue.

If you really need it for production I will spend some time to investigate.

[CE101]

Hi Water:

> If you really need it for production I will spend some time to investigate.

Thank you. However for my sake it is not necessary.
At the current time I have no specific need for having multiple instances.
My question was academic.

[water]

Thanks for the feedback. So we will wait until someone has a real problem that can only be solved with cross instance functions