0

As I receive "PROCEDURE TOO LARGE" compile error I divided my code in multiple smaller subroutines, but I still receive this error and I don`t know how to divide it more. Here is the part of code that I didnt know how to divide more (as variable "I" doesnt update in main code as it should - it value is reset to 0 same as other variables included:

X7 = 109
Z7 = 0

For Q7 = 92 + Z6 To X6
  If Cells(Q7, I + 4).Value = "" Then GoTo OPERATOR7I
  If Cells(Q7, I + 4).Value >= TARGET - 0.01 Then GoTo OPERATOR7H
  If Cells(Q7, I + 4).Value < TARGET - 0.01 Then
    Cells(X7, I + 1).Value = Cells(Q7, I + 1).Value
    Cells(X7, I + 4).Value = TARGET - Cells(Q7, I + 4).Value
    Z7 = Z7 + 1
  End If

  If Cells(123, I + 5).Value < 60 + KK - 0.5 Then GoTo OPERATOR7F
  If Cells(123, I + 5).Value > 60 + KK - 0.5 And Cells(123, I + 5).Value < 60 + KK + 0.5 Then GoTo OPERATOR7D
  If Cells(123, I + 5).Value > 60 + KK + 0.5 Then GoTo OPERATOR7G

OPERATOR7G:
  Cells(X7, I + 4).Value = Cells(X7, I + 4).Value - 1
  If Cells(123, I + 5).Value > 60 + KK + 0.5 Then GoTo OPERATOR7G
  If Cells(123, I + 5).Value > 60 + KK - 0.5 And Cells(123, I + 5).Value < 60 + KK + 0.5 Then GoTo OPERATOR7D

OPERATOR7I:
OPERATOR7F:
  If Z7 = 0 Then GoTo LOOP0EDIT7
  X7 = X7 + 1

LOOP0EDIT7:
OPERATOR7H:
Next Q7

If Application.Sum(Range(Cells(7, I + 15), Cells(Y1, I + 15))) < 0.01 Then                  GoTo LOOPRECALC7B
For XOXO7 = 7 To Y1
  If Cells(XOXO7, I + 15).Value > 0.01 Then
    Cells(X7, I + 1).Value = Cells(XOXO7, 3).Value
    Cells(X7, I + 4).Value = Cells(XOXO7, I + 15).Value
    X7 = X7 + 1
    Z7 = Z7 + 1
  End If
Next XOXO7
LOOPRECALC7C:
If Cells(123, I + 5).Value > 60 + KK + 0.5 Then GoTo LOOPRECALC7A
If Cells(123, I + 5).Value > 60 + KK - 0.5 And Cells(123, I + 5).Value < 60 + KK + 0.5 Then GoTo LOOPRECALC7B
If Cells(123, I + 5).Value < 60 + KK - 0.5 Then GoTo LOOPRECALC7B

LOOPRECALC7A:
Cells(X7, I + 4).Value = Cells(X7, I + 4).Value - 1
GoTo LOOPRECALC7C

LOOPRECALC7B:

OPERATOR7A:
If Cells(Y1 + 1, 3).Value = "" Then GoTo EXITLOOP7
If Cells(Y1 + 1, 4).Value = "Kontrola kvaliteta." Then GoTo QUALITYLOOP7A
Cells(X7, I + 1).Value = Cells(Y1 + 1, 3).Value
Cells(X7, I + 4).Value = TARGET
Y1 = Y1 + 1
GoTo QUALITYLOOP7B

QUALITYLOOP7A:
Y1 = Y1 + 1
GoTo OPERATOR7A

QUALITYLOOP7B:
OPERATOR7E:
If Cells(123, I + 5).Value < 60 + KK - 0.5 Then GoTo OPERATOR7B
If Cells(123, I + 5).Value > 60 + KK - 0.5 And Cells(123, I + 5).Value < 60 + KK + 0.5 Then GoTo OPERATOR7D
If Cells(123, I + 5).Value > 60 + KK + 0.5 Then GoTo OPERATOR7C

OPERATOR7B:
X7 = X7 + 1
GoTo OPERATOR7A

OPERATOR7C:
Cells(X7, I + 4).Value = Cells(X7, I + 4).Value - 1
GoTo OPERATOR7E

OPERATOR7D:
EXITLOOP7:

'OPERATOR8 LOOP

X8 = 126
Z8 = 0

For Q8 = 109 + Z7 To X7
  If Cells(Q8, I + 4).Value = "" Then GoTo OPERATOR8I
  If Cells(Q8, I + 4).Value >= TARGET - 0.01 Then GoTo OPERATOR8H
  If Cells(Q8, I + 4).Value < TARGET - 0.01 Then
    Cells(X8, I + 1).Value = Cells(Q8, I + 1).Value
    Cells(X8, I + 4).Value = TARGET - Cells(Q8, I + 4).Value
    Z8 = Z8 + 1
  End If

  If Cells(140, I + 5).Value < 60 + KK - 0.5 Then GoTo OPERATOR8F
  If Cells(140, I + 5).Value > 60 + KK - 0.5 And Cells(140, I + 5).Value < 60 + KK + 0.5 Then GoTo OPERATOR8D
  If Cells(140, I + 5).Value > 60 + KK + 0.5 Then GoTo OPERATOR8G

OPERATOR8G:
  Cells(X8, I + 4).Value = Cells(X8, I + 4).Value - 1
  If Cells(140, I + 5).Value > 60 + KK + 0.5 Then GoTo OPERATOR8G
  If Cells(140, I + 5).Value > 60 + KK - 0.5 And Cells(140, I + 5).Value < 60 + KK + 0.5 Then GoTo OPERATOR8D

OPERATOR8I:
OPERATOR8F:
  If Z8 = 0 Then GoTo LOOP0EDIT8
  X8 = X8 + 1

LOOP0EDIT8:
OPERATOR8H:
Next Q8

If Application.Sum(Range(Cells(7, I + 15), Cells(Y1, I + 15))) < 0.01 Then GoTo LOOPRECALC8B
For XOXO8 = 7 To Y1
  If Cells(XOXO8, I + 15).Value > 0.01 Then
    Cells(X8, I + 1).Value = Cells(XOXO8, 3).Value
    Cells(X8, I + 4).Value = Cells(XOXO8, I + 15).Value
    X8 = X8 + 1
    Z8 = Z8 + 1
  End If
Next XOXO8
LOOPRECALC8C:
If Cells(140, I + 5).Value > 60 + KK + 0.5 Then GoTo LOOPRECALC8A
If Cells(140, I + 5).Value > 60 + KK - 0.5 And Cells(140, I + 5).Value < 60 + KK + 0.5 Then GoTo LOOPRECALC8B
If Cells(140, I + 5).Value < 60 + KK - 0.5 Then GoTo LOOPRECALC8B

LOOPRECALC8A:
Cells(X8, I + 4).Value = Cells(X8, I + 4).Value - 1
GoTo LOOPRECALC8C

LOOPRECALC8B:

OPERATOR8A:
If Cells(Y1 + 1, 3).Value = "" Then GoTo EXITLOOP8
If Cells(Y1 + 1, 4).Value = "Kontrola kvaliteta." Then GoTo QUALITYLOOP8A
Cells(X8, I + 1).Value = Cells(Y1 + 1, 3).Value
Cells(X8, I + 4).Value = TARGET
Y1 = Y1 + 1
GoTo QUALITYLOOP8B

QUALITYLOOP8A:
Y1 = Y1 + 1
GoTo OPERATOR8A

QUALITYLOOP8B:
OPERATOR8E:
If Cells(140, I + 5).Value < 60 + KK - 0.5 Then GoTo OPERATOR8B
If Cells(140, I + 5).Value > 60 + KK - 0.5 And Cells(140, I + 5).Value < 60 + KK + 0.5 Then GoTo OPERATOR8D
If Cells(140, I + 5).Value > 60 + KK + 0.5 Then GoTo OPERATOR8C

OPERATOR8B:
X8 = X8 + 1
GoTo OPERATOR8A

OPERATOR8C:
Cells(X8, I + 4).Value = Cells(X8, I + 4).Value - 1
GoTo OPERATOR8E

OPERATOR8D:
EXITLOOP8:

Code goes on and on with these LOOPS (X9, X10, X11....) and variables "KK" & "I" are also used in other parts of code for other functiones (some of them are divided into smaller subroutines)! How to shorten this?

Community
  • 1
  • 1
  • Basically, it is finding most efficient way to distribute production procedures (phases) amoung operators by their level of knowledge and speed. Operators are always changing (sometimes hourly) but we have fixed number of max operators - 16,procedures are changing daily, except that certain procedures needs to be excluded from calculations and added to certain operators instantly while distributing his available capacity amoung other n operators in that case. As tested, this macro works and does what I need (when I reduce number of operators to 8 and therefore reduce the size of code). –  Dec 07 '16 at 19:59
  • 9
    Refactor your code. You've written it like it's 30 years ago. Break certain functionality into defined functions, and pass arguments. This really needs rebuilding from the ground up.. – MacroMarc Dec 07 '16 at 20:06
  • 4
    All the `GoTo`s are bad practice. Try rewriting the code without using any `GoTo`s as I suspect that is related the problem. – tigeravatar Dec 07 '16 at 20:06
  • Yup, I believe it looks prehistoric. I`m quite a noob, I know what I need but I`m building it like a lego, from fractions of what I know. –  Dec 07 '16 at 20:11
  • 2
    Shameless plug: [vba code refactoring - are there any tools to assist?](http://stackoverflow.com/a/29991570/1188513) – Mathieu Guindon Dec 07 '16 at 20:11
  • How could I make some of this variables global? I dont quite know how to use it, but it could possibly give me opportunity to break the code in smaller routines! For example functions with "I"s and "KK"s could be in other subroutines but as soon as I break the part of code with those variable it values is reset to 0 (in that subroutine)! –  Dec 07 '16 at 20:13
  • Not true, you can always pass variables back and forth to functions and subs. – Rdster Dec 07 '16 at 20:26
  • Frankly, you need to hire an experienced VBA programmer and have them spend a couple of weeks on a complete rewrite. Thousands of lines of code like this is almost definitely suboptimal, buggy, or both. It could be risky to base business decisions on it. Furthermore, it sounds like you are trying to code an ad-hoc solution to a linear programming problem. Excel's built-in solver can handle that. What you really need is a VBA programmer who knows operations research. If you want to develop the skills yourself, see the book "VBA for Modelers" by Chris Albright. – John Coleman Dec 07 '16 at 20:43
  • But I tried and received a error. When Ive checked, in subroutine variable "I" and variable "KK" which is constantly updating in loop in main routine had value 0 in subroutine section even if it should have let say 93! –  Dec 07 '16 at 20:45
  • Thnx @John Coleman for book suggestion, and also for solution suggestion, but unfortunately, our IT`s in company specialiased in different areas, so Im stuck with what I have, and due extremly large amount of data and operations we have on daily basis I must figure something on my own. So far it was ok, but maybe I was just lucky. As I said code finished OK and perfectly distributed operations but only with less No of operators (guess because of my prehistoric way of wrting code). –  Dec 07 '16 at 20:52
  • Thank you all for help guys, @Mat`s Mug and @John Coleman, you both have very good points and reffer to proper solution for my problem, but unfortunately i had to deal with it differently (to be honest to @MatsMug I didnt quite got what to do with add-in). I solved it with modifying my code a bit and making some variables global so it can be refactored. I know it is maybe prehistoric and not as it should be but it was done with what I know and what I have. –  Dec 08 '16 at 15:34
  • @IvanKočiš if your code works as intended now, take it to [codereview.se] to learn how to improve your code structure and avoid it in the future. – Mathieu Guindon Dec 08 '16 at 15:40

0 Answers0