Procedure too large

Question

I try to display a message of from a drop-down list , i have actually 40 Drop down list I've made only for 28 and for the last 12 drop down list i got an error message ( procedure too large)

I used this function for all cases

If Target.Address(0, 0) = "K3" Then
    Select Case Target
        Case "EX1": Sheets("By Assessment method").Range("K2") = Sheets("SOMC-Legend").Range("B9")
        Case "EX2": Sheets("By Assessment method").Range("K2") = Sheets("SOMC-Legend").Range("B10")
        Case "EX3": Sheets("By Assessment method").Range("K2") = Sheets("SOMC-Legend").Range("B11") 
        Case "Educ":  Sheets("By Assessment method").Range("K2") = Sheets("SOMC-Legend").Range("B7")   End Select 
End if

http://stackoverflow.com/questions/11450232/getting-error-procedure-too-large-in-vba-macros-excel — Siddharth Rout, Sep 11 '15 at 20:20

score 1 · Answer 1 · answered Sep 11 '15 at 20:30

Your code is more verbose than it should be, and there are a number of ways you can address that, such as:

Dim rng as string

If Target.Address(False, False) = "K3" Then
    Select Case Target
        Case "EX1":   rng= "B9"
        Case "EX2":   rng= "B10"
        Case "EX3":   rng= "B11" 
        Case "Educ":  rng= "B7"  
     End Select 
End If

If rng<>"" Then
     Sheets("By Assessment method").Range("K2") = _
                Sheets("SOMC-Legend").Range(rng).Value
End If

It's difficult to suggest what might be even more useful approaches without seeing more of your existing methods.

Any time you run into "procedure too large" in VBA it's a hint you maybe need to re-think how you're doing things.

Thanks for ur help that helped me alot – Riadh Saïd Sep 14 '15 at 20:45 — Riadh Saïd, Sep 14 '15 at 20:45

Procedure too large

1 Answers1