VBA code stuck in a loop

Difficult to comment, impossible to test ... the Control Panel worksheet is not present and half (?) the code is missing. It's difficult to see the flow because of the repetition of sheet names and the poor indentation. As presented, the VBA Project will not compile.

Due to the missing code, it is not possible to determine where cell values have been changed which would bring the loop to a termination.

Stepping through the code using F8 does not mean it will come to a natural conclusion. You compare (AND) ten pairs of cells to bring the loop to an end. What are the chances that one of those conditions is not met? I suspect pretty high.

I would suggest that you put Debug.Print statements in various places to determine a) how many times you are going through the loop, b) what the values of the variables are, and whether or not each of the conditions is met.

I would also build in a "get out of jail clause". That's simply a counter that you increment and test each time you go through the loop. When it reaches an arbitrary limit (100, 1,000, 10,000, whatever), you exit the loop, perhaps with a warning message.

Note that generic code, such as the LightOutputRedDec module should NOT be in a sheet class module, it should be in a standard module.

Slightly modified code:

Please Login or Register  to view this content.

Regards, TMS

the file size was too big at 2.3Mb

will it zip?

OK, same code with Get out of Jail counter and debug output for the loop.

I don't know where, or how, the values are expected to change. Apparently, they don't. And, consequently, the exit criteria are never met.

Also, the conditional statements that might prompt user input are never met, so no changes are mad as a result of that code. I haven't included debug code for that section, but you can add it if you wish. Clearly, the conditions are not met.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards, TMS

Thanks.

There is a purple button near the top left of the control panel sheet, this will unlock and unhide all the sheets in the workbook. On the RED ASPECT sheet the values in column C65:C165, has changeable values. the values are read from testing light via a photo cell and an A to D converter. these values are then automatically placed in column C. The values change according to what the light output is set to on the light source being tested.

The user sets the light output according to the limits on the chart. The VBA code should tell him if the Light Output is too high or too low without the user having to analyse the chart. The Red chart in this case has been set with the light output too high to replicate a "too high" output failure. the limit lines on the chart are changeable for testing of this workbook purposes to indicate if you want a "too High" "too low" or "passed" criteria. The limits are changeable using the percentage values on the Control Panel Sheet at cells AN24, AO24, AP24.

The same applies to the Yellow and Green test.

Hope this sheds a little light on what the workbook is supposed to do. Thanks

Regards,
Steve

OK, thanks for the clarification. There's not a lot I can do with that knowledge, unfortunately.

The point is that the loop exit is dependent on 10 cell pair comparisons. Unless they are all True, it will keep looping. I do not know where WITHIN the loop, the values will be changed such that the exit condition is met (10 x True).

Similarly, the request for input within the loop is dependent on several cell pair comparisons. If the condition is not met, you won't ask for inpit and you won't change anything.

I have added the Get out of Jail counter which does break out of the loop but, being realistic, you can put any number in there if the loop exit conditions are not met.

You might be better putting the If/And condition on the Control Panel worksheet and just testing one condition. Then pick the value up from the worksheet ... that's more flexible and you can see what's going on.

Sadly, I don't think there's a lot I more I can help you with. This is more a process issue than a code issue. The loop WILL exit provoded the right conditions are met. Trouble is, there's a lot of them and they're not being met.


Regards, TMS