Non-Accessible Variable

[boogieoompa]

So I have spent many o' hours trying to figure this out buy I think I need to escalate it to the forum.  I have a script that basically opens up 2 excel files.  Once the 2 are open it picks a common field (found in column 1) to match, and creates 2 arrays where I can then apply logic.  Its basically like an advanced vlookup.  The problem is after the first compare I get a "Subscript used on non-accessible variable." error.  Specifically

$cancel = abs[$arry_bill[$i][5]]
$cancel = abs^ ERROR

I'm assuming this has to do with the way I'm clearing the array (by re defining them as global variables inside a function) but I do not know another way of clearing out an array so that it can be used later.

Any ideas?

Thanks

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
HotKeySet("q","Fun_End")
;Excel Books
Global $xls_billing  = _ExcelBookOpen(@ScriptDir & "\Billing.xlsx")
   WinWaitActive("Microsoft Excel - Billing") ;wait for the excel file to load
Global $xls_clinical  = _ExcelBookOpen(@ScriptDir & "\Clinical.xlsx")
   WinWaitActive("Microsoft Excel - Clinical") ;wait for the excel file to load
;Setting Arrays
Global $arry_clin[5000][4] ;the current patient's clinical events stored in an array
Global $arry_bill[5000][6] ;the current patient's billing events stored in an array
Global $arry_master_bill = _ExcelReadSheetToArray($xls_billing,1,1,0,5)
;Application Specific Variables
Global $var_current_row = 2 ;the current row.  Updated after each patient
Global $var_current_pt ;the current patient's MR
Global $var_previous_row = $var_current_row;the previous last patient's MR
Global $var_result
Sleep(5000) ;wait for all sheets to load and memory to recover... just to be safe
Fun_Start() ;begins the recursion thru the clinical data
Fun_End()
Func Fun_Start()
   While _ExcelReadCell($xls_clinical,$var_current_row) <> ""
   Fun_Read_Clinical($var_current_row)
   Fun_Read_Billing($var_current_pt)
   $i = 1
   Fun_Clean()
#cs
   While $arry_clin[$i][1] <> ""
   Fun_Analysis_1($arry_clin,$i)
   Fun_Analysis_2($arry_clin,$arry_bill,$i)
   _ExcelWriteCell($var_clinical_sheet,$var_result,$var_previous_row + $i - 1 , 9)
   $var_result = ""
   $i = $i + 1
   WEnd
   $i = 1
   While $arry_clin[$i][1] <> ""
   Fun_Analysis_3($arry_clin,$arry_bill,$i)
   _ExcelWriteCell($var_clinical_sheet,$var_result,$var_previous_row + $i - 1 , 10)
   $var_result = ""
   $i = $i + 1
   WEnd
   Fun_Analysis_5($arry_clin,$arry_bill)
   _ExcelWriteCell($var_clinical_sheet,$var_result,$var_current_row - 1, 11)
   $var_result = ""
#ce
   $var_previous_row = $var_current_row
   Global $arry_clin[5000][4] ;the current patient's clinical events stored in an array
   Global $arry_bill[5000][6] ;the current patient's billing events stored in an array
   WEnd
EndFunc
;Load the patients into 2 arrays basaed on MR
;Subtract/cancel out the negative quantities
;

Func Fun_Clean()
;   _ArrayDisplay($arry_clin)
   _ArrayDisplay($arry_bill)
   $i = 1
   While $i < 5000
   If $arry_bill[$i][5] < 0 Then
   $cancel = abs[$arry_bill[$i][5]]
   $z = 1
   While $z < $cancel + 1
   $y = 1
   While $y < 5000
      If $arry_clin[2] = $arry_bill[4] and $arry_clin[3] = $arry_bill[2] and $arry_bill[5] > 0 Then
      $arry_bill[5] = $arry_bill[5] - 1
      EndIf
      $y = $y + 1
   WEnd
   $z = $z + 1
   WEnd
   EndIf
   $i = $i + 1
   WEnd
   _ArrayDisplay($arry_clin)
   _ArrayDisplay($arry_bill)
EndFunc
Func Fun_Read_Billing($mr)
;A = Fin
;B = CDM
;C = Amount
;D = DOS
;E = Quantity
   $i = 2
   While $i < UBound($arry_master_bill)
   $z = 1
   $y = 1
   If $arry_master_bill[$i][1] = $mr Then
   While $z < 6
    $arry_bill[$y][$z] = $arry_master_bill[$i][$z]
   $z = $z + 1
   WEnd
   $y = $y + 1
   Else
   ExitLoop
   EndIf
   $i = $i + 1
   WEnd
EndFunc
Func Fun_Read_Clinical($start_row)
   ;A = Fin
   ;B = DOS
   ;C = CDM
   $y = 1
   $var_current_pt = _ExcelReadCell($xls_clinical,$var_current_row,1)
   While _ExcelReadCell($xls_clinical,$var_current_row,1) = $var_current_pt
   $z = 1
   While $z < 4
   $arry_clin[$y][$z] = _ExcelReadCell($xls_clinical,$var_current_row,$z)
   $z = $z + 1
   WEnd
   $y = $y + 1
   $var_current_row = $var_current_row + 1
   WEnd
EndFunc
Func Fun_End()
   Global $xls_billing ; Clears Memory
   Global $xls_clinical ;Clears Memory
   MsgBox(0,"Finally", "Done")
   Exit
EndFunc

[BrewManNH]

Your array $arry_bill isn't defined until after the Fun_Clean function has been called the first time.

Also $arry_bill is a 2D array, and you're referencing it, in Fun_Clean is being referenced as a 1D array here.

If $arry_clin[2] = $arry_bill[4] and $arry_clin[3] = $arry_bill[2] and $arry_bill[5] > 0 Then
      $arry_bill[5] = $arry_bill[5] - 1
      EndIf

[boogieoompa]

Thanks for the feedback!  I changed the array back to 2d and redifned $arry_bill[5000] everytime before it gets populated and I still get the same error in the same place.

expandcollapse popup

#include <Excel.au3>
#include <Array.au3>
HotKeySet("q","Fun_End")
;Excel Books
Global $xls_billing  = _ExcelBookOpen(@ScriptDir & "\Billing.xlsx")
   WinWaitActive("Microsoft Excel - Billing") ;wait for the excel file to load
Global $xls_clinical  = _ExcelBookOpen(@ScriptDir & "\Clinical.xlsx")
   WinWaitActive("Microsoft Excel - Clinical") ;wait for the excel file to load
;Setting Arrays
Global $arry_clin[5000][4] ;the current patient's clinical events stored in an array
Global $arry_bill[5000][6] ;the current patient's billing events stored in an array
Global $arry_master_bill = _ExcelReadSheetToArray($xls_billing,1,1,0,5)
;Application Specific Variables
Global $var_current_row = 2 ;the current row.  Updated after each patient
Global $var_current_pt ;the current patient's MR
Global $var_previous_row = $var_current_row;the previous last patient's MR
Global $var_result
Sleep(5000) ;wait for all sheets to load and memory to recover... just to be safe
Fun_Start() ;begins the recursion thru the clinical data
Fun_End()
Func Fun_Start()
   While _ExcelReadCell($xls_clinical,$var_current_row) <> ""
   Fun_Read_Clinical($var_current_row)
   Fun_Read_Billing($var_current_pt)
   $i = 1
   Fun_Clean()
   $var_previous_row = $var_current_row
   WEnd
EndFunc
;Load the patients into 2 arrays basaed on MR
;Subtract/cancel out the negative quantities
;

Func Fun_Clean()
;   _ArrayDisplay($arry_clin)
   _ArrayDisplay($arry_bill)
   $i = 1
   While $i < 5000
   If $arry_bill[$i][5] < 0 Then
   $cancel = abs[$arry_bill[$i][5]]
   $z = 1
   While $z < $cancel + 1
   $y = 1
   While $y < 5000
      If $arry_bill[$i][4] = $arry_bill[$y][4] and $arry_bill[$i][3] = $arry_bill[$y][3] and $arry_bill[$y][5] > 0 Then
      $arry_bill[$y][5] = $arry_bill[$y][5] - 1
      EndIf
      $y = $y + 1
   WEnd
   $z = $z + 1
   WEnd
   EndIf
   $i = $i + 1
   WEnd
EndFunc
Func Fun_Read_Billing($mr)
;A = Fin
;B = CDM
;C = Amount
;D = DOS
;E = Quantity
   $i = 2
   Global $arry_bill[5000][6] ;the current patient's billing events stored in an array

   While $i < UBound($arry_master_bill)
   $z = 1
   $y = 1
   If $arry_master_bill[$i][1] = $mr Then
   While $z < 6
    $arry_bill[$y][$z] = $arry_master_bill[$i][$z]
   $z = $z + 1
   WEnd
   $y = $y + 1
   Else
   ExitLoop
   EndIf
   $i = $i + 1
   WEnd
EndFunc
Func Fun_Read_Clinical($start_row)
   ;A = Fin
   ;B = DOS
   ;C = CDM
   Global $arry_clin[5000][4] ;the current patient's clinical events stored in an array
   $y = 1
   $var_current_pt = _ExcelReadCell($xls_clinical,$var_current_row,1)
   While _ExcelReadCell($xls_clinical,$var_current_row,1) = $var_current_pt
   $z = 1
   While $z < 4
   $arry_clin[$y][$z] = _ExcelReadCell($xls_clinical,$var_current_row,$z)
   $z = $z + 1
   WEnd
   $y = $y + 1
   $var_current_row = $var_current_row + 1
   WEnd
EndFunc
Func Fun_End()
   Global $xls_billing ; Clears Memory
   Global $xls_clinical ;Clears Memory
   MsgBox(0,"Finally", "Done")
   Exit
EndFunc