Checkboxes and assign and eval - in an array?

[MrCheese]

Hey Guys,

 

So, the functions work, where primarygui() accurately determines the evaluation of the status of the checkboxes - the msgbox picks this up.

 

However, later on, when we re-enter a 'for $i = 0 to ubound($checkbox)' loop, then in the 'batchinitial' function it doesn't picked up that the status&$i = 1, so it jumps out, then within the While 1 loop, it exits the loop in the first row, again because the status& $i= 0

The "assign" line within the primarygui funtion, is this only a local assignment? if so, how can I make it cross function?

 

Thank in adv for your help

 

 

expandcollapse popup

Func excelsheetlist()
    $i = 0
    Global $aWorkSheets = _Excel_SheetList($oWorkbook1)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1")

    ;_ArrayDisplay($aWorkSheets, "Array")
    Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10.
    Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20.
    Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional.

    MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _
            $iRows & " row(s) & " & $iCols & " column(s).")

    Dim $checkbox[$iRows]

EndFunc   ;==>excelsheetlist

Func primarygui()
    ; Create a GUI with various controls.
    Local $hGUI = GUICreate("Script Controller", 300, ($iRows * 24))

    ; Create a checkbox control.
    ;Local $idCheckbox = GUICtrlCreateCheckbox("Standard Checkbox", 10, 10, 185, 25)
    Local $Button2 = GUICtrlCreateButton("Close", 210, 200, 85, 25)
    Local $Button3 = GUICtrlCreateButton("Run", 210, 170, 85, 25)
    Local $Button1 = GUICtrlCreateButton("Discharge", 210, 140, 85, 25)

    For $i = 0 To UBound($checkbox) - 1
        $checkbox[$i] = GUICtrlCreateCheckbox($aWorkSheets[$i][0], 8, 0 + ($i * 24)) ;, 81, 17)
    Next

    ; Display the GUI.
    GUISetState(@SW_SHOW, $hGUI)

    ; Loop until the user exits.
    While 1
        $nMsg = GUIGetMsg()

        Select
            Case $nMsg = $GUI_EVENT_CLOSE
                Exit

            Case $nMsg = $Button2 ;Close

                Exit

            Case $nMsg = $Button1
                MsgBox(0, "Discharge Button not configured", "Now Exiting")
                Exit

            Case $nMsg = $Button3 ;Run

                $fSelection = False
                For $i = 0 To UBound($checkbox) - 1
                    If BitAND(GUICtrlRead($checkbox[$i]), $GUI_CHECKED) Then
                        $fSelection = True
                        ExitLoop
                    EndIf
                Next

                If $fSelection Then
                    For $i = 0 To UBound($checkbox) - 1
                        Assign("status" & $i, GUICtrlRead($checkbox[$i]))
                    Next
                    $batchcount = 0
                    For $i = 0 To UBound($checkbox) - 1
                        If Eval("status" & $i) = 1 Then
                            $batchcount = $batchcount + 1
                            ;Call ("o" & $i & "copy") ; if you want to call the functions directly, remove ; before the call and comment or delete the following DirCopy statement
                            MsgBox(0, "Checking", "Checking that: " & $checkbox[$i] & " no, with title: " & $aWorkSheets[$i][0] & " was selected, Batch count: " & $batchcount) ; if you need only the DirCopy
                        EndIf
                    Next
                    ExitLoop

                Else
                    MsgBox(48, 'No Items Selected', 'You have not selected any Patients to Load, Please select from the list')
                EndIf


        EndSelect

    WEnd


    ; Delete the previous GUI and all controls.
    GUIDelete($hGUI)
EndFunc   ;==>primarygui

Func _IsChecked($idControlID)
    Return BitAND(GUICtrlRead($idControlID), $GUI_CHECKED) = $GUI_CHECKED
EndFunc   ;==>_IsChecked


Func batchinitial()
    If Eval("status" & $i) = 1 Then
        debugbox()
        $oWorkbook1.Sheets(1).Activate
        $bigloop = $bigloop + 1
        $sheet = $aWorkSheets[$i][0]
        $oWorkbook1.Sheets($sheet).Activate
        debugbox()
    EndIf

EndFunc   ;==>batchinitial


OpenExcel()
excelsheetlist()
primarygui()

For $i = 0 To UBound($checkbox) - 1
    batchinitial()

    While 1
        If Eval("status" & $i) = 0 Then ExitLoop
            ;all the rest of my script loops etc
        
    WEnd


Next

 

Edited January 8, 2018 by MrCheese

[MrCheese]

Okay - so I added '2' at the end of "assign"

Assign("status" & $i, GUICtrlRead($checkbox[$i]), 2)

 

Now, it doesn't proceed past the 'batchinitial()' function:

Func batchinitial()
    If Eval("status" & $i) = 1 Then
        debugbox()
        $oWorkbook1.Sheets(1).Activate
        $bigloop = $bigloop + 1
        $sheet = $aWorkSheets[$i][0]
        $oWorkbook1.Sheets($sheet).Activate
        debugbox()
    EndIf
EndFunc   ;==>batchinitial

but it does proceed passed the if -> exitloop

For $i = 0 To UBound($checkbox) - 1
    batchinitial()

    While 1
        If Eval("status" & $i) = 0 Then ExitLoop

 

Which would suggest that the status$i value is neither 1 nor 0.

I inserted: Dim $status[0] at the top to see if that helped, but it didn't.

 

Any help would be great! 

Thanks

 

Edited January 8, 2018 by MrCheese

[MrCheese]

Noting - I picked up eval and assign from this thread - and that perhaps theres a better way to do what I want to do?

 

[benners]

Do you have a script that compiles without errors? One thing that stands out is in the batchinitial function you specifiy 

If Eval("status" & $i) = 1 Then

But $i is local to primarygui.  You also specifiy 

$bigloop = $bigloop + 1

but $bigloop is not declared anywhere .

Normally, if adding lots of controls I'll use an array (as suggested by others in the post above) to keep a record of the ControlIDs . Then you can loop through this array to action the controls.

Edited January 8, 2018 by benners

[MrCheese]

yes - sorry, i removed the rest of the code, as the entire script is about 1500 lines long.

but yes, it compiles fine.

would it work if I move the gui stuff outside of the function into the main script?

Or should I use a different $i?

$i is used to initially run through a spreadsheet to create all the respective checkboxes on the GUI, then its used to determine which checkboxes have been checked, then it ideally documents, stores as "status0 = 1" if the first box is checked.

I thought that this 'assign' function would be global since I marked it as such. - or am I not understanding how it works?

 

edit: I have tested moving the GUI and batch initial out of their functions. It works if and only if i select the first checkbox i.e. status0,

if any of the others are selected, it doesn't pick up on this. indicating that I'm either prasing the Ifs wrong, or I don't have the eval right.

Edited January 8, 2018 by MrCheese

[benners]

I don't know much about assign\eval\isdeclared  so my lack of knowledge makes me think they are evil , I normally find another way like an array.

Looking at the Assign statement, it just uses the default as the 3rd parameter, should this not be added to make it Global?.

That said,  with the line  "If Eval("status" & $i) = 1 Then",  if it was declared as Global such as, status0, The Eval should return an error as $i has no value so it should just be trying to return the value of "status".

I'm not experienced enough at look at code and pick out the issues, I normally need a runnable script to play with. Again I would try to use an array and condem the Assign\Eval usage to an early grave 

Edited January 8, 2018 by benners

[MrCheese]

mmmm okay.

So, I should commit these values to an array, can I add them to an exisiting array;

by, replacing assign/eval here:

If $fSelection Then
                    For $i = 0 To UBound($checkbox) - 1
                        Assign("status" & $i, GUICtrlRead($checkbox[$i]), 2)
                    Next
                    $batchcount = 0
                    For $i = 0 To UBound($checkbox) - 1
                        If Eval("status" & $i) = 1 Then
                            $batchcount = $batchcount + 1
                            ;Call ("o" & $i & "copy") ; if you want to call the functions directly, remove ; before the call and comment or delete the following DirCopy statement
                            MsgBox(0, "Checking", "Checking that: " & $checkbox[$i] & " no, with title: " & $aWorkSheets[$i][0] & " was selected, Batch count: " & $batchcount & " / state: " & $i & " = " & $status[$i]) ; if you need only the DirCopy
                        EndIf
                    Next

 

can i commit the above into the previous array here:

Func excelsheetlist()
    $i = 0
    Global $aWorkSheets = _Excel_SheetList($oWorkbook1)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1")

    ;_ArrayDisplay($aWorkSheets, "Array")
    Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10.
    Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20.
    Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional.

    MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _
            $iRows & " row(s) & " & $iCols & " column(s).")

    Dim $checkbox[$iRows]

EndFunc   ;==>excelsheetlist

Or won't that work?

[MrCheese]

Okay - I can get the status into the array:  - code is below for those who might find this useful later on.

However, I'm getting the occasional error where the script just bombs out:

 

>Running:(3.3.14.2):C:\Users\25126209\Documents\autoit-v3-SciTE\autoit-v3\install\autoit3.exe "D:\scripts\PatientConversation_v0.7.au3"   

 

--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop

 

"D:\scripts\PatientConversation_v0.7.au3" (514) : ==> The requested action with this object has failed.:

 

$oWorkbook1.Sheets($sheet).Activate

 

$oWorkbook1^ ERROR

 

->17:15:34 AutoIt3.exe ended.rc:1

 

 

This refers to the $oWorkbook1.Sheets($sheet).Activate section below.

I also attempted to use $oWorkbook1.Sheets($i + 1).Activate, as this refers to the numbered row + 1 in the array that is referred to: if $aWorkSheets[$i][2] = 1

A few questions - i) how can I msgbox up if it fails here, and ii) how can I fix this?

ConvoL()
For $i = 0 To UBound($checkbox) - 1

    If $aWorkSheets[$i][2] = 1 Then

        $bigloop += $bigloop 
        $sheet = $aWorkSheets[$i][0]
        $oWorkbook1.Sheets($sheet).Activate

        While 1
            ttipscalc()
            reset()
            If $secondgo = False Then
                $run = $run + 1
                $ptno = $ptno + 1
            EndIf

            If $run > $ttlrun Then ExitLoop
            If $ptno > $ttlrun Then ExitLoop
            $row = $ptno + $rrdif
            If $restarttrigger = True Then
                ;restartpc()
            EndIf
            While 1
                Checkstatus()
                ;funcs etc etc
            WEnd
        WEnd
    EndIf

Next

 

EDIT:

I am also getting this error:

 

--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop

"D:\scripts\PatientConversation_v0.7.au3" (663) : ==> The requested action with this object has failed.:

$iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count

$iRowCount = .Range(^ ERROR

->17:44:19 AutoIt3.exe ended.rc:1

 

 

Which relates to - which is in the primary loop.

I ended up removing this, and just calling the array where this information had previously been stored:

$ttlrun = $aWorksheets[$i][3]

Func checkstatus()
    $msg = "Checking status"
    ttips()
    $column = $ptComment1
    Local $oRangeRead = _Excel_RangeRead($oWorkbook1, Default, $column & $row)
    If $oRangeRead = "Completed" Or $oRangeRead = "Completed - existing encounter" Then
        $exitloop = True
    EndIf
    ; **SCRIPT NEEDED** insert script to determine total number of rows

    With $oWorkbook1.ActiveSheet ; process active sheet
        $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells
        $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column
        $iLastCell = .Cells($iRowCount + 1, $columnfind).End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number
        ;MsgBox(0, "", "Last used cell in column A: " & $iLastCell & @CRLF & "row count: " & $iRowCount)
    EndWith
    $ttlrun = $iRowCount - 1

EndFunc   ;==>checkstatus

 

scripts for info:

----- 

Func excelsheetlist()
    $i = 0
    Global $aWorkSheets = _Excel_SheetList($oWorkbook1)
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    ;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1")

    ;_ArrayDisplay($aWorkSheets, "Array")
    Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10.
    Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20.
    Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional.

    MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _
            $iRows & " row(s) & " & $iCols & " column(s).")

    Dim $checkbox[$iRows]
    _ArrayColInsert($aWorkSheets, 2) ; added to create a new col

EndFunc   ;==>excelsheetlist

expandcollapse popup

excelsheetlist()
;primarygui()

; Create a GUI with various controls.
Local $hGUI = GUICreate("Script Controller", 300, ($iRows * 24))

; Create a checkbox control.
;Local $idCheckbox = GUICtrlCreateCheckbox("Standard Checkbox", 10, 10, 185, 25)
Local $Button2 = GUICtrlCreateButton("Close", 210, 200, 85, 25)
Local $Button3 = GUICtrlCreateButton("Run", 210, 170, 85, 25)
Local $Button1 = GUICtrlCreateButton("Discharge", 210, 140, 85, 25)

For $i = 0 To UBound($checkbox) - 1
    $checkbox[$i] = GUICtrlCreateCheckbox($aWorkSheets[$i][0], 8, 0 + ($i * 24)) ;, 81, 17)
Next

; Display the GUI.
GUISetState(@SW_SHOW, $hGUI)

; Loop until the user exits.
While 1
    $nMsg = GUIGetMsg()

    Select
        Case $nMsg = $GUI_EVENT_CLOSE
            Exit

        Case $nMsg = $Button2 ;Close

            Exit

        Case $nMsg = $Button1
            MsgBox(0, "Discharge Button not configured", "Now Exiting")
            Exit

        Case $nMsg = $Button3 ;Run

            $fSelection = False
            For $i = 0 To UBound($checkbox) - 1
                If BitAND(GUICtrlRead($checkbox[$i]), $GUI_CHECKED) Then
                    $fSelection = True
                    ExitLoop
                EndIf
            Next

            If $fSelection Then

                $batchcount = 0
                For $i = 0 To UBound($checkbox) - 1
                    $sFill = GUICtrlRead($checkbox[$i])
                    $aWorkSheets[$i][2] = $sFill
                    $batchcount += (GUICtrlRead($i) = 1 ? 1 : 0)
                Next

                ExitLoop

            Else
                MsgBox(48, 'No Items Selected', 'You have not selected any Patients to Load, Please select from the list')
            EndIf


    EndSelect

WEnd


; Delete the previous GUI and all controls.
GUIDelete($hGUI)

 

 

 

Edited January 9, 2018 by MrCheese

[benners]

Here is one way I would add the checkboxes to an array and the find out which ones are selected (code below). As mentioned It would be better to post a minimal script that does the basics and people can test, rather than snippets of code that are of no use to anyone. 

If the code is 1500 lines long, go through it and remove the stuff you don't need/use.

One example is the excelsheetlist function, Don't declare global variables inside functions. If it's only used in the function make it Local.

Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10.
Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20.
Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional.

You only use the $iRows inside the function so make it local, and the other two aren't used anywhere I can see, (as there are only snippets posted)

expandcollapse popup

#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <Array.au3>

Opt('GUIOnEventMode', 1)

; array for ther sheetnames
; returns a 2D array, we will use the 2nd column where the Object of the worksheet should be
; to hold the checkbox checked state
Global $as_SheetNames = Excel_GetSheetList() ; returns a 2D array,
If @error Then CloseProgram()

; array for the checkbox controlIDs
Global $aid_Checkboxes[UBound($as_SheetNames)]

DrawGUI()

While 1
    Sleep(50)
WEnd

Func CloseProgram()
    Exit
EndFunc   ;==>CloseProgram

; update the array when a checkbox is selected
Func Checkbox_Clicked()
    ; search the checkbox array and get the index of the clicked checkbox
    Local $i_Index = _ArraySearch($aid_Checkboxes, @GUI_CtrlId)

    ; update the sheets array with the checked state
    $as_SheetNames[$i_Index][1] = GUICtrlRead(@GUI_CtrlId)
EndFunc   ;==>Checkbox_Clicked

; get the sheet names
Func Excel_GetSheetList()
    Local $oExcel = _Excel_Open(False)
    If @error Then Return SetError(1, MsgBox(0, '_Excel_Open', 'Error: ' & @error), 0)

    Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
    If @error Then Return SetError(2, MsgBox(0, '_Excel_BookOpen', 'Error: ' & @error), 0)

    Local $aWorkSheets = _Excel_SheetList($oWorkbook)
    If @error Then Return SetError(2, MsgBox(0, '_Excel_SheetList', 'Error: ' & @error), 0)

    _Excel_Close($oExcel)

    If Not IsArray($aWorkSheets) Then Return SetError(4, MsgBox(0, '$aWorkSheets', 'Unable to get worksheet names'), 0)

    Return $aWorkSheets
EndFunc   ;==>Excel_GetSheetList

; draw the gui
Func DrawGUI()
    GUICreate("test", 200, 200, 1026, 420)
    GUISetOnEvent($GUI_EVENT_CLOSE, 'CloseProgram')

    GUICtrlCreateButton("Open Sheets", 10, 150, 90, 25)
    GUICtrlSetOnEvent(-1, 'GetChecked_BtnClicked')

    For $i = 0 To UBound($as_SheetNames) - 1
        $aid_Checkboxes[$i] = GUICtrlCreateCheckbox($as_SheetNames[$i][0], 8, 10 + ($i * 24))
        GUICtrlSetOnEvent(-1, 'Checkbox_Clicked')
    Next

    GUISetState()
EndFunc   ;==>DrawGUI

; get the selected checkboxes
Func GetChecked_BtnClicked()
    ; find all the selected checkboxes
    Local $ai_Selected = _ArrayFindAll($as_SheetNames, $GUI_CHECKED, 0, 0, 0, 0, 1)
    If Not IsArray($ai_Selected) Then Return MsgBox(0, 'No Results', 'No check boxes were selected')

    Local $oExcel = _Excel_Open()
    If @error Then Return SetError(1, MsgBox(0, '_Excel_Open', 'Error: ' & @error), 0)

    Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx")
    If @error Then Return SetError(2, MsgBox(0, '_Excel_BookOpen', 'Error: ' & @error), 0)

    For $i = 0 To UBound($ai_Selected) - 1
        ; activate the sheet
        $oWorkbook.Sheets($as_SheetNames[$ai_Selected[$i]][0]).Activate
        Sleep(2000)
    Next

    _Excel_Close($oExcel, True, True)
EndFunc   ;==>GetChecked_BtnClicked

 

test.xlsx

[MrCheese]

Hi - thanks for your reply, I can see how you've improved the flow and the functions. I'll see what I can incorporate. 

Yeah, I see your point, Just most (~90%) of the script is utilised, and some of the content is commercially sensitive. makes pulling that out tricky.

I should be able to simulate my issues in a more simple script and then copy them here.

[benners]

No problem.  If you post a file (like a spreadsheet) it doesn't need to be the one you use, just one that mimics it so function can be tested. It makes it easier to produce errors as it might run error free on one persons machine but no on another.

[MrCheese]

In this component:

For $i = 0 To UBound($ai_Selected) - 1
        ; activate the sheet
        $oWorkbook.Sheets($as_SheetNames[$ai_Selected[$i]][0]).Activate
        Sleep(2000)
    Next

 

Compared to mine below, the primary point of difference is that you avoid the need for the if statement as you have already extracted the active checkboxes?

For $i = 0 To UBound($checkbox) - 1 ; loops through the checkboxes

    If $aWorkSheets[$i][2] = 1 Then ; reads where in the array is stored if the checkbox is checked
        $sheet = $aWorkSheets[$i][0] ; sheet name is located here
        $oWorkbook1.Sheets($sheet).Activate ; activated based on sheet name
        
        ;blar blar blar
        
    endif
  next

 

The Sheet.activate component is giving me the odd failure - but more so on one laptop then another. Same vr of WIn and Office.

[benners]

No idea why that would be. If your method is OK just use that. The sleep is only to wait so you can see the sheets being selected or Excel would just flash up and close. It wouldn't be needed in your script.

I have had a few blips with the Excel UDF as well but have never been able to reproduce enough to get info