Call Problem 1

[UnsolvedCoding]

I have a sub that calls another sub. The problem is that when the second sub is called it doesn't work properly for the final few lines but if I run it separately it corrects itself and is just fine.

Why?


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.

 

[Andrzejek]

It would be nice to see some code....

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[UnsolvedCoding]

This is the sub that calls (line 1000):

Sub AAD_IO_Choice()

' Keyboard Shortcut: Ctrl+Shift+E

On Error GoTo Error_Handler

100:    Call OptimizeCode_Begin

        ' Put the message in the status bar
300:    Application.StatusBar = "Running Panel_Choice."

        ' set the error flag to run the macros
400:    FLAG = True

        ' Load the userform
500:    UserForm3.Show

600:    CB_FLAG = 3

700:    I = UBound(FORM3_ARRAY)

        ' Create missing IO sheets or requested IO sheets
800:    Call CB_Panel_Counting_Loop: If FLAG = False Then Call OptimizeCode_End: Exit Sub

900:    Call CLEANER

1000:   Call AAC_Recreate_Info_Sheet: If FLAG = False Then Call OptimizeCode_End: Exit Sub

1100:   Call OptimizeCode_End

1200:   ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources

1400:   Exit Sub

Error_Handler:

Call OptimizeCode_End
FLAG = False: FINISH = Timer
TEST = MsgBox("AAC_Panel_Choice Malfunctioned." & vbCrLf & _
        "Error Number - " & Err.Number & vbCrLf & "Error Source - " & Err.Source & _
        vbCrLf & "Error Description - " & Err.Description & _
        vbCrLf & "Error Line: " & Erl & vbCrLf & "Help File - " & Err.HelpFile & _
        vbCrLf & "Context - " & Err.HelpContext, vbOKOnly + vbCritical)

End Sub

And this is the one that gets called:

Sub AAC_Recreate_Info_Sheet()

' Keyboard Shortcut: Ctrl+Shift+D

On Error GoTo Error_Handler

10:     FLAG = True

100:    CB_FLAG = 7

        ' Make code go faster
200:    Call OptimizeCode_Begin

        ' Check to see if info sheet exists
300:    Call Check_For_Info_Sheet: If FLAG = False Then Call OptimizeCode_End: Exit Sub

400:    Call Fresh_Info_Sheet: If FLAG = False Then Call OptimizeCode_End: Exit Sub

        ' Clear out 100 cells deep for new info and header rows
500:    Sheets("Cover").Range("J11:M113").ClearContents

        ' Because it screwed up once the number format is reset
600:    Sheets("Cover").Range("J11:M242").NumberFormat = "General"

700:    Sheets("Cover").Range("J11").Value = "Amp Usage Total"
800:    Sheets("Info").Range("B2").Value = "Amp Usage Total"

        ' Header
900:    Sheets("Cover").Range("J13").Value = "CB"
1000:   Sheets("Info").Range("B4").Value = "CB"

        ' Headers for columns
1100:   Sheets("Cover").Range("K13").Value = "Total for CB"
1200:   Sheets("Info").Range("C4").Value = "Total for CB"

1300:   Sheets("Cover").Range("L13").Value = "Input"
1400:   Sheets("Info").Range("D4").Value = "Input"

1500:   Sheets("Cover").Range("M13").Value = "Output"
1600:   Sheets("Info").Range("E4").Value = "Output"

1700:   A = 5

        ' Scan all the worksheets
1800:   For Each WS In ThisWorkbook.Worksheets

            ' Check if the worksheet is an is list
1900:       If Format(Left(Application.Proper(WS.Name), 2), ">") = "IO" Then

                ' Get the right three digits which will be the number
2000:           Sheets("Info").Range("B" & A).Value = Right(Application.Proper(WS.Name), 3)
2100:           Sheets("Cover").Range("J" & A + 9).Value = Right(Application.Proper(WS.Name), 3)

                ' For the first loop put the number in the variable
2200:           If A = 5 Then FIRST_CB_NMBR = Right(Application.Proper(WS.Name), 3)

                ' Update the number each loop through so we can get the name of the last io list
2300:           LAST_CB_NMBR = Right(Application.Proper(WS.Name), 3)

                ' Get ready in the next cell down on the Info sheet
2400:           A = A + 1

2500:       End If

2600:   Next WS

2700:   Call Create_Info_Sheet: If FLAG = False Then Call OptimizeCode_End: Exit Sub

2800:   Call Create_Formulas: If FLAG = False Then Call OptimizeCode_End: Exit Sub

2900:   Call Save_To: If FLAG = False Then Call OptimizeCode_End: Exit Sub

3000:   Call CLEANER

3100:   Call OptimizeCode_End

3200:   ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources

3300:   Exit Sub

Error_Handler:
Call OptimizeCode_End
TEST = MsgBox("Re_Create_Info_Sheet Malfunctioned." & vbCrLf & _
        "Error Number - " & Err.Number & vbCrLf & "Error Source - " & Err.Source & _
        vbCrLf & "Error Description - " & Err.Description & _
        vbCrLf & "Error Line: " & Erl & vbCrLf & "Help File - " & Err.HelpFile & _
        vbCrLf & "Context - " & Err.HelpContext, vbOKOnly + vbCritical)

End Sub

The one that is called runs just fine on its own but when it is called from the first one screws up. The second one is a stand alone or can be called depending on the users selection.

And no, I can't post all the code, that's about 3,500 lines.



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]

One more tidbit.

When I step through the code it works fine, but when I run it full speed it fails.

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.

 

[Andrzejek]

when the second sub is called it doesn't work properly for the final few lines

Which "final few lines" are you referring to?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[UnsolvedCoding]

In the second sub, AAC_Recreate_Info_Sheet, line 2800 creates formulas to tally information from other sheets and then total the information of all the sheets. The INFO worksheet holds the individual sheet tallys with the total sum below. The total is there but several rows below where it should be, however if I re-run AAC_Recreate_Info_Sheet everything is in the correct place.

So when the AAD_IO_Choice is called the totals for all the sheets are several lines below where they should be, but when AAC_Recreate_Info_Sheet runs as a stand alone procedure all the formatting and totals are correct.

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.

 

[Andrzejek]

If some problems happen around line [tt]2800:[/tt], it looks to me that the value of [tt]FLAG[/tt] may be not what you expect. And since you know what to do to make your code miss-behave, I would try this:

Call Create_Formulas
[blue]Debug.Print "After Create_Formulas the FLAG is " & FLAG
[/blue]
If FLAG = False Then 
[blue]   Debug.Print "Going to first OptimizeCode_End" [/blue]
   Call OptimizeCode_End
   Exit Sub
End If

Call Save_To
[blue]Debug.Print "After Save_To the FLAG is " & FLAG
[/blue]
If FLAG = False Then 
[blue]   Debug.Print "Going to second OptimizeCode_End" [/blue]
   Call OptimizeCode_End
   Exit Sub
End IF
[blue]Debug.Print "Going to CLEANER" [/blue]
Call CLEANER
[blue]Debug.Print "Going to last OptimizeCode_End" [/blue]
Call OptimizeCode_End

ThisWorkbook.UpdateLink Name:=ThisWorkbook.LinkSources

Exit Sub

And after the code is done, see if what you would expect to happen, did happen. And if not - what did happen when the code did run.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[UnsolvedCoding]

I figured out where the problem lies, and its in a sub a few routines away from where I was looking. This is the culprit code -

If CB_FLAG = 7 Then TEST = Cells(Rows.Count, "B").End(xlUp).Row - 4

This row doesn't return the correct information if the sub is called from AAD_IO_Choice, but if it is run as part of AAC_Recreate_Info_Sheet as a stand alone it returns properly. The crazy thing is that for both subs CB_FLAG = 7.

TEST is used to test the row number against an existing row number. So if rows 5-9 are used, then TEST checks to make sure that the total for the column goes in row 10.

When used in the sequence listed above it is off because it doesn't subtract 4, but when AAC_Recreate_Info_Sheet is not called and is run as a stand alone procedure it works properly and the 4 is subtracted.

Still haven't figured out why it happens.



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.

 

[strongm]

I'm going to blame global variables

 

[UnsolvedCoding]

This is my work around

800:    If CB_FLAG = 7 Then

            ' Loop until the first empty cell
900:        While Sheets(3).Cells(H + 4, 2) <> ""

1000:            H = H + 1

1100:       Wend

        ' Subtract one to not include the empty cell
1200:   TEST = H - 1

1300:   End If

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.

 

[combo]

In the error handlers you have [tt]TEST = MsgBox(".......", vbOKOnly + vbCritical)[/tt], the MsgBox answer can be only vbOKOnly, so you will get [tt]TEST=0[/tt] (vbOKOnly=0). Maybe the code never use TEST after error, but it can be confusing. I would use here either different variable name or MsgBox without output.

combo

 

[UnsolvedCoding]

I ran TEST through the watch window and the immediate window and it was holding information but not correctly. For some reason the subtract 4 portion wasn't taking.

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.