Stopping COM in Excel VBA

[FAM]

I have a COM application reading in real-time data into Columns A and B in Excel but i want it to stop when i press a command button, I have the code below but it comes up with an error on the Stop part.
What am i doing wrong? and how can i get it to stop?
Cheers

#########
Private Sub cmdStop_Click()
Dim I
For I = 1 To 10 ' Start For...Next loop.
Debug.Print I ' Print I to the Immediate window.
Stop ' Stop during each iteration.
Next I
End Sub
##########

[alex_read]

Not totally sure what you mean there, but in the above code, an exit for will quit the loop.

Private Sub cmdStop_Click() 
    Dim I as integer
    For I = 1 To 10 ' Start For...Next loop. 
        Debug.Print I ' Print I to the Immediate window. 
        Exit for ' Stop during each iteration. 
    Next I 
End Sub

If you've got the Excel coding in a loop in procedureA & want to exit this from a stop button, you can use something like this:

Private blnExitLoop as boolean
 
Private sub Form_Load()
    blnExitLoop = false
End Sub
 
Private sub DoStuffToExcel()
    'Create excel objects
 
    for i = 0 to 10
 
       If blnExitLoop  = true then
            Excel.worksheet.cells(1,1).value = i
       Else
            Exit for
       End If
    
    Next i
End Sub
 
Private Sub cmdStop_Click()
    blnExitLoop  = true
End Sub

[FAM]

I have the code below which reads in data from COM 1 into Excel, when you press the cmdRecord button in excel, but i would like to have a stop button to stop the process.
Any Ideas?
Thanks

#############
Dim value As Long
Dim ok As Boolean
Dim I As Integer
Dim port As Integer

Private Sub cmdRecord_Click()

port = 1
Cells(4, "I").value = "Opening ADC on COM" & port

ok = adc16_open_unit(port)
If ok Then
Cells(4, "I").value = "ADC opened"

' Specify 16-bit resolution for channels 1 and 2
ok = adc16_set_channel(port, 1, 10, True, 10)
ok = adc16_set_channel(port, 2, 10, True, 10)

' Get 50 readings from these channels
For I = 1 To 50
ticks = GetTickCount()
While GetTickCount() < ticks + 1000
' Let Excel carry on doing things
DoEvents
' Excel doesn't let timer ticks thru to adc16 driver...
' Must call the poll routine to keep data flowing
adc16_poll
Wend

ok = adc16_get_value(value, port, 1)
If ok Then
Cells(I + 17, "A").value = value * 2500 / 65535
Else
Cells(I + 17, "A").value = "****"
End If

ok = adc16_get_value(value, port, 2)
If ok Then
Cells(I + 17, "B").value = value * 2500 / 65535
Else
Cells(I + 17, "B").value = "****"
End If
Next I

' Close the driver
Call adc16_close_unit(port)
Else
Cells(4, "I").value = "Unable to open ADC"
End If
End Sub
##########

[alex_read]

Yeah okay, have a private boolean variable declared for this form like in the above. Every time your for...next loop is hit, place an if statement to check the value of this boolean variable - if it's equal to true then use the exit for statement to exit the loop.

Then in your strop buttons click event, set the value of the boolean variable to true. As the code above, your procedure will excecute happily as the boolean value will initially be false, as soon as the stop button's hit, this value will change, and the loop in the main procedure will pick this up on the next iteration & stop the loop.

[FAM]

Thanks Alex I got it working fine.