VBA Code Too Long

[appleglaze28]

Hi! I'm not really good with VBA Script but I'm familiar with the logic of how it works. I'm kinda troubleshooting something in Excel VBA and something was not working right.

Apparently whoever created that VBA Script had a very long conditional statement...too long that he had to cut it. He wrote another SUB process where the rest of the condition are written. But the main process has this condition where it checks the value and if it is meant a certain value is suppose to be linked to a variable. Then that variable goes through another conditional statement.

The programmer who created the script cut the script during the first condition. And I assume since the rest of the condition is in another SUB process. That the variable which is still found in the main process is equivalent to 0 cause the SUB process can't have the value be an output in the main process. Is my assumption right? If so? What's the best way I can remedy this.

And in case someone would recommend cutting the 1st condition short...there's no way I can do that cause it needs to be that specific.

Thanks in advance.

 

[bensonsearch]

if the variable is defined globally then no worries, otherwise it needs to be referance through the main sub.

eg sub 1 has a variable = 2

sub 2 wants to know what variable in sub1 is

sub1.variablename.value

 

[appleglaze28]

How do I have the variable in the 2nd sub to the main sub?

Below is the code
**********************************************************
Sub EvaluateTotal()
'Dim mec, phase, endorse, i
'Dim total As Boolean
total = True
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
For i = 13 To 139
<o> </o>
Dim char, cc, desc
char = Left(Sheet1.Range("B" & i).Value, 3)
cc = Sheet2.Range("C5").Value

endorse = Run("DBR", Sheet2.Range("C4").Value, Sheet2.Range("D1").Value, Sheet2.Range("D2").Value, Sheet2.Range("D3").Value, Sheet2.Range("D4").Value, Sheet2.Range("M7").Value, Sheet2.Range("M8").Value, Sheet2.Range("C5").Value, Sheet2.Range("M6").Value, Sheet2.Range("D5").Value, char, Sheet2.Range("M9").Value)

If char = 416 Or char = 420 Or char = 421 Then
mec = Run("DBR", Sheet2.Range("C20").Value, Sheet2.Range("D1").Value, Sheet2.Range("D2").Value, Sheet2.Range("D3").Value, Sheet2.Range("D4").Value, Sheet2.Range("M7").Value, Sheet2.Range("M6").Value, Sheet2.Range("M8").Value, Sheet2.Range("F1").Value, Sheet2.Range("F2").Value, Sheet2.Range("F3").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "Total Line", Sheet2.Range("C13").Value)
phase = Run("DBR", Sheet2.Range("C20").Value, Sheet2.Range("D1").Value, Sheet2.Range("D2").Value, Sheet2.Range("D3").Value, Sheet2.Range("D4").Value, Sheet2.Range("M7").Value, Sheet2.Range("A13").Value, Sheet2.Range("M8").Value, Sheet2.Range("F1").Value, Sheet2.Range("F2").Value, Sheet2.Range("F3").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "Total Line", Sheet2.Range("A14").Value)
Run "DBSW", mec, "ASC:Monitor Status", Sheet2.Range("M7").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "MEC"
Else

continue

End If
<o> </o>
If Round(endorse, 2) = Round(mec, 2) And Round(endorse, 2) = Round(phase, 2) Then
'If Round(endorse, 2) = Round(mec, 2) Then 'And Round(endorse, 2) = Round(phase, 2) Then
Sheet1.Range("V" & i).Borders.ColorIndex = 2
Run "DBSS", "True", "ASC:Monitor Status", Sheet2.Range("M7").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "Endorsed = MEC"
Else
Sheet1.Range("V" & i).Borders.ColorIndex = 3
Run "DBSS", "False", "ASC:Monitor Status", Sheet2.Range("M7").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "Endorsed = MEC"
Run "DBSS", "False", "ASC:Monitor Status", Sheet2.Range("M7").Value, "1", "NET EXPENSES", "Endorsed = MEC"
total = False
End If

Next i
<o> </o>
If total = False Then
Run "DBSS", "False", "ASC:Monitor Status", Sheet2.Range("M7").Value, Sheet2.Range("C5").Value, "NET EXPENSES", "Endorsed = MEC"
MsgBox "New Endorsement not tallied.", vbCritical, "ASC"
Else
Run "DBSS", "True", "ASC:Monitor Status", Trim(str(Sheet2.Range("M7").Value)), Sheet2.Range("C5").Value, "NET EXPENSES", "Endorsed = MEC"
MsgBox "New Endorsement equal to Proposed.", vbInformation, "ASC"
End If
<o> </o>
End Sub

**********************************************************

CONTINUE is the name of the 2nd SUB or secondary sub...it has the 1st conditional statement format for different "char" value that couldnt fit in the main sub. I put the 2nd conditional statement after CONTINUE for the formatting inside continue. Since base on what I tested out...any variable like "mec & phase" from the 2nd SUB is not being put in the main sub.

Also the Message box doesn't work the correctly...how do I fix it. Cause I experience where the looping doesn't bring the right messagebox message.

I think this code derive the message box during the last loop. Am I right?

 

[bensonsearch]

thats A LOT of code. let me ponder it over maybe someone else could help in the meantime

 

[appleglaze28]

I know...took me awhile to figure out what was wrong with it...for some reason the things that are suppose to happen for the Main Sub isnt working for the secondary sub in it. Thanks for the help!

 

[bensonsearch]

do you have the code for continue?

is there a particular line thats not working? or maybe an example of something that doesnt come out right

 

[appleglaze28]

Actually its the same as the main....its just that it variable "char" could be a lot of different number that is why its that long...its like char is a list of specific code.

**********************************************************
If char = 416 Or char = 420 Or char = 421 Then
mec = Run("DBR", Sheet2.Range("C20").Value, Sheet2.Range("D1").Value, Sheet2.Range("D2").Value, Sheet2.Range("D3").Value, Sheet2.Range("D4").Value, Sheet2.Range("M7").Value, Sheet2.Range("M6").Value, Sheet2.Range("M8").Value, Sheet2.Range("F1").Value, Sheet2.Range("F2").Value, Sheet2.Range("F3").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "Total Line", Sheet2.Range("C13").Value)
phase = Run("DBR", Sheet2.Range("C20").Value, Sheet2.Range("D1").Value, Sheet2.Range("D2").Value, Sheet2.Range("D3").Value, Sheet2.Range("D4").Value, Sheet2.Range("M7").Value, Sheet2.Range("A13").Value, Sheet2.Range("M8").Value, Sheet2.Range("F1").Value, Sheet2.Range("F2").Value, Sheet2.Range("F3").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "Total Line", Sheet2.Range("A14").Value)
Run "DBSW", mec, "ASC:Monitor Status", Sheet2.Range("M7").Value, Sheet2.Range("C5").Value, Sheet1.Range("B" & i).Value, "MEC"
*******************************************************

Specifically the message box. Cause I can't for some reason bring the value of the secondary out to the main. But even that I'm not sure if the message box will work correct.

Cause I'm suppose to make sure the value in the columns match for everyline and if it doesn't it should have the else statement as the message box.