Search the Community

Hello! Sorry for the weekly simpleton question, but: I tried to make a part of my code be able to handle arrays of any size (Before, I used a fixed array size of 4, which is far from ideal, I just wanted it to run) I szccesfully read the array size, ReDim it and I thought that I can easly read the values I need, but the 5. version of my code is giving me the same error. I'm not sure, how should I use variabels as "array subscripts" (I mean: $Array[!This for example!][!Or this!]) I wrote the following abomination, Its job would be to read through an array of running/open excel instances and find the one with a .xls extension. Then read its filepath and name so I can make a copy of it a bit down on the line (BTW Anyother insight if you think I'm doing something wrong or not idealy is welcome!) Local $aWorkBooks = _Excel_BookList() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookList Example 2", "Error listing Workbooks." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _ArrayDisplay($aWorkBooks, "Excel UDF: _Excel_BookList Example 2 - List of workbooks of all instances") ;CONSTRUCTION----------------------------------- ;$aWorkBooks array méretezés If IsArray($aWorkBooks) Then ReDim $aWorkBooks[UBound($aWorkBooks)] ;MsgBox($MB_SYSTEMMODAL, "Értesítás", "Cucc:" & UBound($aWorkBooks) & "") Local $ArrayElement = UBound($aWorkBooks) Do Local $Int = Int(0) ;MsgBox($MB_SYSTEMMODAL, "Értesítés", "Cucc: " & $ArrayElement &" Int: " & $Int &"") Local $Array = $aWorkBooks[Int($Int)][1] Local $xls = StringRegExp(String($Array), ".xls") $Int = Int($Int + 1) Until $xls = 1 $ExcelName = $aWorkBooks[$Int][1] $ExcelPath = $aWorkBooks[$Int][2] Else MsgBox($MB_SYSTEMMODAL, "Excel Array Hiba", "Hiba a formázandó excel felismerése közben.") Exit EndIf Consol Error: (242) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: Local $Array = $aWorkBooks[Int($Int)][1] Local $Array = ^ ERROR I try to use an increasing variable to read the array until its find the .xls extension, the values of $Int is okey according to the message boxes Thanks for the help, wise users of this forum! Edit: You find the solution to this problem in the thread below, also the problem developed into an other one, _Excel_SheetList UDF returned with a "funny array" with numbers (see the pic around comment 5-8) the solution was to disable excel addons: Analysis ToolPak and Solver, not sure which one did the trick at the end. Also in this thread you can find a "bug fix" for _Excel_SheetList , in the current version, _Excel_SheetList gave 0 return to every error, master water posted a fix to that (you have to change a 0 value to @error). Best of luck!