Jump to content

Help with this error message


Sam137
 Share

Recommended Posts

I have the coding :

$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "F1:F200")  
   if @error then exit msgbox(0,"","error finding string. @error = " & @error)  
   _ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] - 1)
   if @error then exit msgbox(0,"","error writing text. @error = " & @error)
   _ExcelBookSave($oExcel)

I get the error as attached only sometimes. Not everytime.

I dont know what is going on. Please help.

post-65245-0-80945400-1318884079_thumb.j

Edited by Sam137
Link to comment
Share on other sites

It's apparent _ExcelFindInRange() is returning an array with a structure smaller than [2] times [4].

$aFound[1][3] does not exist.

Stick an _ArrayDisplay() after the _ExcelFindInRange() and see what it is returning.

Then look to your source data or your parms, or into the _ExcelFindInRange() function itself.

PS - Interesting method of executing Msgbox(), as the parameter of an exit statement...

Link to comment
Share on other sites

  • Moderators

Sam137,

As was explained above, the error happens because _ExcelFindInRange is not returning a large enough array (or perhaps not an array at all) and the elements you are trying to read are not available. Where does this function come from - it is not in the official Excel.au3 UDF which means I have no idea what it is supposed to return? :graduated:

As to $aFound[1][3] and $aFound[1][2], those are the array elements you are trying to read in the code you posted and which do not appear to exist. Surely you know what the code does and why you need those particular elements? :)

M23

P.S. You might find the Arrays tutorial in the Wiki a useful read. ;)

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

i have copied the excel.au3 UDF from this website officially. I am not getting the error frequently. Suppose i have some 20 records, say i am getting the error at 16 record or so. If you assume that i have been using the wrong excel.au3 UDF. please provide me with the correct one, i will copy and paste it.

Link to comment
Share on other sites

  • Moderators

Sam137,

There is no _ExcelFindInRange function in the officially released Excel.au3 include file - I assume you are using the ExcelCOM UDF from this thread, which I understand was the basis for the existing official UDF.

However, as the function no longer exists in the current UDF I cannot offer any opinion as to why it fails (because I do not know how it works) - I can only explain (as I did in the post above) how it fails resulting in the error message you received.

You might be able to to handle the error by adding a check to the code you posted to ensure the returned array is large enough:

$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "F1:F200")
If @error Then Exit MsgBox(0, "", "error finding string. @error = " & @error)
If UBound($aFound, 1) < 2 Or UBound($aFound, 2) < 4 Then
Exit MsgBox(0, "", "error ExcelFindInRange returned too small an array")
Else
_ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] - 1)
EndIf
If @error Then Exit MsgBox(0, "", "error writing text. @error = " & @error)
_ExcelBookSave($oExcel)

Although of course this does not address why the function failed. :graduated:

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • Moderators

Sam137,

I have been looking into the _ExcelFindInRange function for you (as you seem unwilling or unable to do it yourself) and I see the return should be:

Returns a two dimensional array with addresses of matching cells.  If no matches found, returns null string
;                       $array[0][0] - The number of found cells
;                       $array[x][0] - The address of found cell x in A1 format
;                       $array[x][1] - The address of found cell x in R1C1 format
;                       $array[x][2] - The row of found cell x as an integer
;                       $array[x][3] - The column of found cell x as an integer

From this I deduce that on occasion you are not finding a match and the function is returning a null string. Hence the error when you try to read the elements of a non-existent array. :graduated:

In the code you posted you are only checking for @error - this is only set by invalid parameters or an object failure. So I suggest that you use something like this in your code to check if there was no match returned by _ExcelFindInRange :

$aFound = _ExcelFindInRange($oExcel, $ConcatVal, "F1:F200")
If @error Then Exit MsgBox(0, "", "error finding string. @error = " & @error)
If $aFound = "" Then ; There was no match <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Exit MsgBox(0, "", "error no match found")  ; So do this <<<<<< You can change this line as required
Else
    _ExcelWriteCell($oExcel, "Yes", $aFound[1][3], $aFound[1][2] - 1)
EndIf
If @error Then Exit MsgBox(0, "", "error writing text. @error = " & @error)
_ExcelBookSave($oExcel)

Of course you can change the action to be taken if no match was found by the _ExcelFindInRange function to whatever you need. :)

Does that help? ;)

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

  • Moderators

Sam137,

Glad I could help. :graduated:

M23

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...