Set @error with declare variable not working

[Kaarl]

Hi,

I have this piece code that works with Excel file and finds the column name where is a specific named range located.

Local $Column = Null
$Column = _Excel_ColumnToLetter($oWorkbook.Sheets("MySheetName").Range("MyRangeName").Column)
If @error Then
        $Column = Null
        SetError(0)
    Else
        ;Proceed...
EndIf

It works properly if the named range exists in the Excel file, I do get the column name and the "Proceed" part is correctly executed. If the named range is not present in the Excel file, I would expect the If @error part to set the variable to Null and then follow with the rest of the function after EndIf. Instead, the script stops and I get this error:

"C:\test\script_021.au3" (1184) : ==> The requested action with this object has failed.:
$Column = _Excel_ColumnToLetter($oWorkbook.Sheets("MySheetName").Range("MyRangeName").Column)
$Column = _Excel_ColumnToLetter($oWorkbook.Sheets("MySheetName")^ ERROR

What am I doing wrong here? Is it possible that failure to set the variable doesn't set the @error? Push in the right direction would be much appreciated. Thanks!

[Kaarl]

I also tried to capture the error before this piece of code with the _Excel_RangeRead function, but the effect is the same.

[water]

The version of AutoIt you run needs a COM error handler to handle such an error.

Example:

#include <Excel.au3>
Global $oExcel = _Excel_Open()
Global $oWorkbook = _Excel_BookNew($oExcel)
Local $oError = ObjEvent("AutoIt.Error", "__Excel_COMErrFunc")

$Column = _Excel_ColumnToLetter(2)
If @error Then
    MsgBox(0, "ColumnToLetter", "Error " & @error & " processing _Excel_ColumnToLetter!")
Else
    MsgBox(0, "ColumnToLetter", $Column)
EndIf

$Column = _Excel_ColumnToLetter($oWorkbook.Sheets("MySheetName").Range("MyRangeName").Column)
If @error Then
    MsgBox(0, "ColumnToLetter", "Error " & @error & " processing _Excel_ColumnToLetter!")
Else
    MsgBox(0, "ColumnToLetter", $Column)
EndIf

 

Edited July 24, 2018 by water

[Kaarl]

Thanks for the tip, I will try this.

[Kaarl]

Thanks again, this solution did the job.

[water]

[pixelsearch]

@water : thanks for teaching us today "__Excel_COMErrFunc" , the function that does nothing 

As this function is not in the script you presented, I entered Excel.au3 and it was there, at the very end :

Func __Excel_COMErrFunc()
    ; Do nothing special, just check @error after suspect functions.
EndFunc   ;==>__Excel_COMErrFunc

All this seems very flexible, I mean using  a COM error handler that does nothing then checking @error, compared to what I tried to do in another script : using  a COM error handler that does something... without any @error in the script, as found here :

https://www.autoitscript.com/forum/topic/195049-loading-excel-range-to-dictionary-error/

 

Edited August 2, 2018 by pixelsearch

[water]

Please have a look at my OutlookEX UDF to see how you could combine he best of all worlds

With _OL_ErrorNotify you can tell the COM error handler (__Outlook_ErrorHandler) what to do when an error arises. Means: Write the error information to the Console, MsgBox, File or do nothing.