×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Procedure too large

Procedure too large

Procedure too large

(OP)
I am using VBA in Excel 2003. I have a very long procedure and I tried turning it into sub procedures, but when I did that, it only performs my first procedure and none of the others.

This is about 1/9 of the procedure. I have 9 "if".

CODE

Sub engine_cost()
    
    
 'Sets engine prices

    If Range("Reference!A2").Value = 1 Then
        Range("Information!D16") = Range("Reference!E3")
    ElseIf Range("Reference!A2").Value = 2 Then
        Range("Information!D16") = Range("'Engine Pricing'!B50")
    ElseIf Range("Reference!A2").Value = 3 Then
        Range("Information!D16") = Range("'Engine Pricing'!B51")
    ElseIf Range("Reference!A2").Value = 4 Then
        Range("Information!D16") = Range("'Engine Pricing'!B52")
    ElseIf Range("Reference!A2").Value = 5 Then
        Range("Information!D16") = Range("'Engine Pricing'!B53")
    ElseIf Range("Reference!A2").Value = 6 Then
        Range("Information!D16") = Range("'Engine Pricing'!B54")
    ElseIf Range("Reference!A2").Value = 7 Then
        Range("Information!D16") = Range("'Engine Pricing'!B55")
    ElseIf Range("Reference!A2").Value = 8 Then
        Range("Information!D16") = Range("'Engine Pricing'!B56")
    ElseIf Range("Reference!A2").Value = 9 Then
        Range("Information!D16") = Range("'Engine Pricing'!B57")
    ElseIf Range("Reference!A2").Value = 10 Then
        Range("Information!D16") = Range("'Engine Pricing'!B58")
    ElseIf Range("Reference!A2").Value = 11 Then
        Range("Information!D16") = Range("'Engine Pricing'!B59")
    ElseIf Range("Reference!A2").Value = 12 Then
        Range("Information!D16") = Range("'Engine Pricing'!B60")
    ElseIf Range("Reference!A2").Value = 13 Then
        Range("Information!D16") = Range("'Engine Pricing'!B61")
    ElseIf Range("Reference!A2").Value = 14 Then
        Range("Information!D16") = Range("'Engine Pricing'!B62")
    ElseIf Range("Reference!A2").Value = 15 Then
        Range("Information!D16") = Range("'Engine Pricing'!B63")
    ElseIf Range("Reference!A2").Value = 16 Then
        Range("Information!D16") = Range("'Engine Pricing'!B64")
    ElseIf Range("Reference!A2").Value = 17 Then
        Range("Information!D16") = Range("'Engine Pricing'!B65")
    ElseIf Range("Reference!A2").Value = 18 Then
        Range("Information!D16") = Range("'Engine Pricing'!B66")
    ElseIf Range("Reference!A2").Value = 19 Then
        Range("Information!D16") = Range("'Engine Pricing'!B67")
    ElseIf Range("Reference!A2").Value = 20 Then
        Range("Information!D16") = Range("Reference!E3")
    ElseIf Range("Reference!A2").Value = 21 Then
        Range("Information!D16") = Range("'Engine Pricing'!B69")
    ElseIf Range("Reference!A2").Value = 22 Then
        Range("Information!D16") = Range("'Engine Pricing'!B70")
    ElseIf Range("Reference!A2").Value = 23 Then
        Range("Information!D16") = Range("'Engine Pricing'!B71")
    ElseIf Range("Reference!A2").Value = 24 Then
        Range("Information!D16") = Range("'Engine Pricing'!B72")
    ElseIf Range("Reference!A2").Value = 25 Then
        Range("Information!D16") = Range("'Engine Pricing'!B73")
    ElseIf Range("Reference!A2").Value = 26 Then
        Range("Information!D16") = Range("'Engine Pricing'!B74")
    ElseIf Range("Reference!A2").Value = 27 Then
        Range("Information!D16") = Range("'Engine Pricing'!B75")
    ElseIf Range("Reference!A2").Value = 28 Then
        Range("Information!D16") = Range("'Engine Pricing'!B76")
    ElseIf Range("Reference!A2").Value = 29 Then
        Range("Information!D16") = Range("'Engine Pricing'!B77")
    ElseIf Range("Reference!A2").Value = 30 Then
        Range("Information!D16") = Range("'Engine Pricing'!B78")
    ElseIf Range("Reference!A2").Value = 31 Then
        Range("Information!D16") = Range("'Engine Pricing'!B79")
    ElseIf Range("Reference!A2").Value = 32 Then
        Range("Information!D16") = Range("'Engine Pricing'!B80")
    ElseIf Range("Reference!A2").Value = 33 Then
        Range("Information!D16") = Range("'Engine Pricing'!B81")
    ElseIf Range("Reference!A2").Value = 34 Then
        Range("Information!D16") = Range("'Engine Pricing'!B82")
    ElseIf Range("Reference!A2").Value = 35 Then
        Range("Information!D16") = Range("'Engine Pricing'!B83")
    ElseIf Range("Reference!A2").Value = 36 Then
        Range("Information!D16") = Range("'Engine Pricing'!B84")
    ElseIf Range("Reference!A2").Value = 37 Then
        Range("Information!D16") = Range("'Engine Pricing'!B85")
    ElseIf Range("Reference!A2").Value = 38 Then
        Range("Information!D16") = Range("'Engine Pricing'!B86")
    ElseIf Range("Reference!A2").Value = 39 Then
        Range("Information!D16") = Range("'Engine Pricing'!B87")
    ElseIf Range("Reference!A2").Value = 40 Then
        Range("Information!D16") = Range("'Engine Pricing'!B88")
    ElseIf Range("Reference!A2").Value = 41 Then
        Range("Information!D16") = Range("Reference!E3")
    ElseIf Range("Reference!A2").Value = 42 Then
        Range("Information!D16") = Range("'Engine Pricing'!B90")
    ElseIf Range("Reference!A2").Value = 43 Then
        Range("Information!D16") = Range("'Engine Pricing'!B91")
    ElseIf Range("Reference!A2").Value = 44 Then
        Range("Information!D16") = Range("'Engine Pricing'!B92")
    ElseIf Range("Reference!A2").Value = 45 Then
        Range("Information!D16") = Range("'Engine Pricing'!B93")
    ElseIf Range("Reference!A2").Value = 46 Then
        Range("Information!D16") = Range("'Engine Pricing'!B94")
    ElseIf Range("Reference!A2").Value = 47 Then
        Range("Information!D16") = Range("'Engine Pricing'!B95")
    ElseIf Range("Reference!A2").Value = 48 Then
        Range("Information!D16") = Range("'Engine Pricing'!B96")
    ElseIf Range("Reference!A2").Value = 49 Then
        Range("Information!D16") = Range("'Engine Pricing'!B97")
    ElseIf Range("Reference!A2").Value = 50 Then
        Range("Information!D16") = Range("'Engine Pricing'!B98")
    ElseIf Range("Reference!A2").Value = 51 Then
        Range("Information!D16") = Range("'Engine Pricing'!B99")
    ElseIf Range("Reference!A2").Value = 52 Then
        Range("Information!D16") = Range("'Engine Pricing'!B100")
    ElseIf Range("Reference!A2").Value = 53 Then
        Range("Information!D16") = Range("'Engine Pricing'!B101")
    ElseIf Range("Reference!A2").Value = 54 Then
        Range("Information!D16") = Range("'Engine Pricing'!B102")
    ElseIf Range("Reference!A2").Value = 55 Then
        Range("Information!D16") = Range("'Engine Pricing'!B103")
    ElseIf Range("Reference!A2").Value = 56 Then
        Range("Information!D16") = Range("'Engine Pricing'!B104")
    ElseIf Range("Reference!A2").Value = 57 Then
        Range("Information!D16") = Range("'Engine Pricing'!B105")
    ElseIf Range("Reference!A2").Value = 58 Then
        Range("Information!D16") = Range("'Engine Pricing'!B106")
    ElseIf Range("Reference!A2").Value = 59 Then
        Range("Information!D16") = Range("Reference!E3")

        
End If


End Sub

Anyone have an idea how I can fix this?

Thanks,
Daniel

RE: Procedure too large

Instead of "if...else if" try 'select case'. Look it up in the help file, it is made for a large number of choices.

RE: Procedure too large

Are you calling the other procedures somewhere?  I assume you run this procedure by pressing a button.  If so, the macro will stop executing when it reaches the "End Sub" statement.  You will need to call the other procedures, possibly at the end of this one, with a line such as:

Call {WhateverYouNamedTheOtherProcedures}

RE: Procedure too large


You can use formula in the cell D16

CODE

=if(OR(Reference!A2=1,Reference!A2=59),Reference!E3,offset('Engine Pricing'!B48,Reference!A2,0,1,1))

or modify your code

CODE

Sub engine_cost()

Select Case Range("Reference!A2").Value
    Case 1, 59
        Range("Information!D16") = Range("Reference!E3")
    Case Is > 1, Is < 59
        Range("Information!D16") = Range("'Engine Pricing'!B48").Offset(Range("Reference!A2").Value)
End Select

End Sub

RE: Procedure too large

(OP)
I am now calling all my procedures.

Thanks for all the help.
Daniel

RE: Procedure too large

Select Case statement is the correct solution for this. Yakpol's solution is very close, you just need to include the other odd values (like 20 and 41) in the first Case statement

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

RE: Procedure too large

(OP)
I changed the code to Select Case

CODE

Sub engine_cost()

Select Case Range("Reference!A2").Value
    Case 1, 20, 41, 59
        Range("Information!D16") = Range("Reference!E3")
    Case Is > 1, Is < 59
        Range("Information!D16") = Range("'Engine Pricing'!B50").Offset(Range("Reference!A2").Value)
End Select

Call engine_costnet
End Sub

Now I am lost. The order of my pricing is off. After 20 its off by one cell, then by two after 41, then four after cell 59.  

What can I do?
Thanks,
Daniel

RE: Procedure too large

Here's a function that may be useful. the function call would be made from information d16 and would look like
=GetEnginePrice(Reference!A2)
HTH

Function GetEnginePrice(iCase As Integer) As Double
  Dim rngPrices As Variant
  rngPrices = Range("'Engine Pricing'!B50:B106")
  Select Case iCase
    Case 2 To 19: GetEnginePrice = rngPrices(iCase, 1)
    Case 21 To 40: GetEnginePrice = rngPrices(iCase + 1, 1)
    Case 42 To 58: GetEnginePrice = rngPrices(iCase + 2, 1)
    Case Else: GetEnginePrice = Range("Reference!E3")
  End Select
End Function

RE: Procedure too large

Daniel,
20 and 41 is checked twice change code to

CODE

Select Case Range("Reference!A2").Value
    Case 1, 20, 41, 59
        Range("Information!D16") = Range("Reference!E3")
    Case Else
        Range("Information!D16") = Range("'Engine Pricing'!B50").Offset(Range("Reference!A2").Value)
End Select

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources