Sam137 Posted October 17, 2011 Share Posted October 17, 2011 (edited) 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. Edited October 17, 2011 by Sam137 Link to comment Share on other sites More sharing options...
Sam137 Posted October 17, 2011 Author Share Posted October 17, 2011 Can anyone please help on this array error Link to comment Share on other sites More sharing options...
Spiff59 Posted October 17, 2011 Share Posted October 17, 2011 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 More sharing options...
Sam137 Posted October 18, 2011 Author Share Posted October 18, 2011 Its returning Row =0 and column = 0, Can anyone help me out this.Also I do not know what do you mean by $aFound[1][3], $aFound[1][2] Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted October 18, 2011 Moderators Share Posted October 18, 2011 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? 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? M23P.S. You might find the Arrays tutorial in the Wiki a useful read. 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 columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Sam137 Posted October 18, 2011 Author Share Posted October 18, 2011 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 More sharing options...
Moderators Melba23 Posted October 18, 2011 Moderators Share Posted October 18, 2011 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. M23 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 columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Sam137 Posted October 18, 2011 Author Share Posted October 18, 2011 Then for this problem do I need to increase the array limit in EXCELCOM_UDF? Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted October 18, 2011 Moderators Share Posted October 18, 2011 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 integerFrom 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. 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 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 columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Sam137 Posted October 18, 2011 Author Share Posted October 18, 2011 Thanks Melba so much for your valuable time. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted October 18, 2011 Moderators Share Posted October 18, 2011 Sam137, Glad I could help. M23 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 columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now