Two issues - cell selection and turning on screen updating after stopping code

[UnsolvedCoding]

I am running into two separate issues that happen about the same time.

First I have to find a way to adjust where information is put on an excel sheet for specific sequences of a loop. For example, if the desired cell for all iterations is D24 thats fine, but if there is a specific condition that changes it to E25 I need to be able to do that.

Second - because of users being able to stop macro execution I need to find a way so that if the code is stopped screen updating becomes true.

Any ideas on either of these?

This is a sample of the coded loop

If B = 1 And INPUT_COUNT > 1 Then D = 7

            ' Put Description on worksheet
3800:       Sheets(CB_PANEL_NAME).Cells(C + 1, D).Value = DIGITAL_INPUT_ARRAY(I - 1)

            ' Put Reference on worksheet
3900:       Sheets(CB_PANEL_NAME).Cells(C + 1, D + 1).Value = DIGITAL_INPUT_ARRAY(I)

            ' Put Nickname on worksheet
4000:       Sheets(CB_PANEL_NAME).Cells(C + 1, D + 3).Value = DIGITAL_INPUT_ARRAY(I - 2) 'I=1

4100:       J = J - 1

4200:       TEST4 = TEST4 - 1

4300:       If J = 0 Then GoTo 1500

If B = 3 And INPUT_COUNT > 1 Then C = 25: D = -5

                ' If I - 6 is outside of array check if I - 3 is valid
4400:       If I - 6 <= LBound(DIGITAL_INPUT_ARRAY) Then GoTo 5700

                ' Set variable for numbering the wires
4500:       X = X + 1

4600:       If DIGITAL_INPUT_ARRAY(I - 6) = "DI" Then X = X - 1

            ' Set number of wires
4700:       TEST = "24VDC(" & X & ")"
4800:       TEST2 = "0VDC(" & X & ")"

            ' Put Nickname on worksheet
4900:       Sheets(CB_PANEL_NAME).Cells(C + 1, D + 6).Value = DIGITAL_INPUT_ARRAY(I - 8) 'I=1

            ' Put reference on worksheet
5000:       Sheets(CB_PANEL_NAME).Cells(C + 1, D + 8).Value = DIGITAL_INPUT_ARRAY(I - 6) 'I=1

            ' Put Description on worksheet
5100:       Sheets(CB_PANEL_NAME).Cells(C + 1, D + 9).Value = DIGITAL_INPUT_ARRAY(I - 7)

5200:       J = J - 1

            ' Put voltage wires on worksheet
5300:       If Not IsEmpty(TEST) Then Sheets(CB_PANEL_NAME).Cells(C + 1, D + 5).Value = TEST
5400:       If Not IsEmpty(TEST2) Then Sheets(CB_PANEL_NAME).Cells(C + 1, D + 4).Value = TEST2

5500:       TEST4 = TEST4 - 1

5600:       If J = 0 Then GoTo 1500

            ' Reset variable
5700:       X = 0

            ' If I is outside of the array count exit the sub
5800:       If I - 3 <= LBound(DIGITAL_INPUT_ARRAY) Then GoTo 2800

            ' Set variable for numbering the wires
5900:       X = X + 1

6000:       If DIGITAL_INPUT_ARRAY(I - 3) = "DI" Then X = X - 1

            ' Set number of wires
6100:       TEST = "24VDC(" & X & ")"
6200:       TEST2 = "0VDC(" & X & ")"

If B = 2 And INPUT_COUNT > 1 Then C = 24: D = 1

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[SkipVought]

Huh???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[zelgar]

1. There's a few ways of doing this. An IF-THEN-ELSE will work. Also using the Select Case functions will work.

2. The command Application.ScreenUpdating = True should be the last item before the End Sub. Why are you letting your users stop the macro execution in the middle of the run without turning ScreenUpdating back on? Also, the format of this macro reminds me of old BASIC. WHY DO YOU HAVE ALL OF THE LINE NUMBERS???? Also, what's with all of the GOTO statements???? The one for line 4600 is not needed if you coded the line like the following:
4400: If I - 6 > LBound(DIGITAL_INPUT_ARRAY) Then
.
.
.
before line 5700
End IF

Is there ever a time when J > 1? If not, it seems like each of the groupings could be a different CASE.

I think your macro needs to be rewritten. There is no reason for all of the line # and the GOTO statements. This macro reminds me of old BASIC programs from the late 70's - early 80's.

 

[UnsolvedCoding]

Zelgar, in answer to your questions -

Screen updating is the last statement, however because of the code being on the network sometimes it hangs and the only way we have found to release it is to stop the execution.

What I am dealing with is three separate things that all relate to what J holds. The first is a block which holds slices. The second is slices which holds individual items and the last is the item itself. So I have had to determine how to identify how many blocks are needed and how many slices within each block are used and finally how many items to a slice if a slice isn't filled up.

Yes it looks like old basic which is for debugging reasons. I am going to be handing this off to people who are not familiar with coding and the error handler identifies which sub routine and which line of code has the problem so that those who will be working on it later can debug it easier. Currently we are pushing 3000 lines of code and while I would like to have written it in a different language, because it gets sent to another company when we are done with it I have been instructed to work in Excel.

Finally, the GOTO statements are because of a quirk that so far we can't eliminate. It has to do with I and the way the upper bounds of the array are interacting. Several programmers have worked with me and we simply can't get it to work without the GOTO statement. So rather than spending more time, the decision was made to use GOTO.

I know the code could be more efficient, however I am doing a balancing act between what needs to be done and others without much coding experience maintaining the workbook in the future.

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[UnsolvedCoding]

Almost forgot to mention - to make this run properly I have to calculate backwards. In other words I have to count down instead of adding.

It has taken me a while to make sense of what I hear at work involving computers. There is much talk of bugs and questions about Raid.
Therefore I have come to the logical conclusion that the only way to have a properly functioning computer is to regularly spray it with Raid bug killer.

 

[zelgar]

You may wish to look into DO Loops. Additionally, in For/Next loops you can count down with the STEP command.

The second thing you may want to evaluate is the use of the variable J representing 3 different things. Without looking at the entire macro, it's hard to know what to fix.

Finally, you're indicating that your users are needing to interrupt the macro because of the server issues. Since they'll need to run the macro anyways, I really don't see what you're gaining with having all of the line # or wanting to automatically turn the Screen Updating back on. I would want the users to rerun the macro to make certain that they got the correct results.

By the way, if you users have broken out of the macro, why is the Screen Updating even an issue? If I have a macro and I break out of it, Screen Updating is back on.

 

[SkipVought]

To me, coding is fun and a worthy challenge.

It has become that way since BASIC and coding methods have evolved.

We no longer use variables like i, j, k, l, m, n (unless doing some quick n dirty code), but we're able to use meaningful names for variables. This helps with code maintenance and understandably.

We use structured coding to add another layer of maintainability.

Instead of GoTos, in some cases within a loop structure an Exit Do or Exit For can be used to force the end of a loop prematurely.

What I'd rather see is your process specification i.e. no computerese, from which you designed your coded procedure. If you never produced such a document, then that may very well be part of your basic (small b) problem.

As zelgar has stated, the ScreenUpdating is not an issue. Other states may be, such as Application.EnableEvents.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]