VBA Nothing / _Excel_RangeFind()

[Moonscarlet]

Hello,

How can I check for the return "Nothing" from methods like "Range.Find" as using @error doesn't mean it didn't find anything in the excel sheet:
 

$CellAddress = $Workbook.ActiveSheet.UsedRange.Find("Text")

If @error Then
    ConsoleWrite("Not found!"&@CRLF)
Else
    Local $Address[2]
    $Address[0] = _Excel_ColumnToLetter($CellAddress.Column)
    $Address[1] = $CellAddress.Row
EndIf

Assuming that "Text" isn't found in my excel sheet, it goes straight to Else and the script crashes "The requested action with this object has failed." because $CellAddress isn't a range object at this point.

On a side note, using _Excel_RangeFind() always gives me an error (also the examples for it give an error):

Quote

"C:\Program Files (x86)\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR

Edit: Nevermind about the "_Excel_RangeFind()" part, I just noticed the pinned thread.

Would really appreciate help with this.

Thanks.

Edited January 24, 2018 by Moonscarlet

[JLogan3o13]

@Moonscarlet first off, it is always preferable to post running code, rather than a couple random lines. If your code is not working you can always write a short reproducer that demonstrates the problem. It saves us from having to write the whole thing out in order to help you.

As to your question, you have a couple of options. If you are wanting to simply check for the existence of data, a simple ElseIf will work:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")

Local $cellAddress = $oWorkbook.ActiveSheet.UsedRange.Find("January")

    If @error Then
        ConsoleWrite("Not Found!" & @CRLF)
    ElseIf ($cellAddress.Row = "") Or ($cellAddress.Column = "") Then
        MsgBox(0, "", "Not Found!" & @CRLF)
    Else
        MsgBox(0, "", $cellAddress.Row & ", " & $cellAddress.Column)
    EndIf

If you need more logic, such as a branch if the cell data is x and another if the cell data is y, look in the help file under Switch.

[Moonscarlet]

Thank you, I'll take that into consideration from now on.

Regarding the example you provided, after adding "January" in any random cell, I get the row and column.

However, if I leave "Test.xlsx" completely empty (or at least without "January" in any cell) I get this error on the "ElseIf" line (and not "ConsoleWrite("Not Found!" & @CRLF)"):

Quote

(10) : ==> The requested action with this object has failed.:
ElseIf ($cellAddress.Row = "") Or ($cellAddress.Column = "") Then
ElseIf ($cellAddress^ ERROR

As I see it "@error" doesn't mean that ".Find" didn't get any results, so I wanted to try checking for "Nothing" which is the return from Range.Find() found on https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-find-method-excel

Quote

Remarks

This method returns Nothing if no match is found.

Thanks.

Edited January 25, 2018 by Moonscarlet

[JLogan3o13]

@Moonscarlet look at IsObj in the help file:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")

Local $cellAddress = $oWorkbook.ActiveSheet.UsedRange.Find("January")

    If IsObj($cellAddress) Then
        If ($cellAddress.Row = "") Or ($cellAddress.Column = "") Then
            MsgBox(0, "", "Not Found!" & @CRLF)
        Else
            MsgBox(0, "", $cellAddress.Row & ", " & $cellAddress.Column)
        EndIf
    Else
        MsgBox(0, "", "Object not created!")
    EndIf

 

[Moonscarlet]

Thanks for your help, this works.

I changed this to:

#include <Excel.au3>

Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, @DesktopDir & "\Test.xlsx")

Local $cellAddress = $oWorkbook.ActiveSheet.UsedRange.Find("January")

    If IsObj($cellAddress) Then
        MsgBox(0, "", $cellAddress.Row & ", " & $cellAddress.Column)
    Else
        MsgBox(0, "", "Object not created/ String not found!")
    EndIf

As I don't know in which situation I should be getting "Not Found!", it's not showing "Not Found!" at all, just "Object not created!" or the address(row/column) if found.

 

Edited January 25, 2018 by Moonscarlet

[water]

Why don't you use _Excel_RangeFind?
When the number of rows returned in the array = 0 then nothing was found.

[Juvigy]

This is what i used in the past for VBS "nothing" needs:

Local $oVBS = ObjCreate("ScriptControl") 
$oVBS.language = "VBScript" 
Global Const $Nothing = $oVBS.eval("Nothing") 
$oVBS = $Nothing

If anyone truly needs it

[water]

IIRC the AutoIt keyword "Null" should do the same (equivalent to "Nothing").