Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

Register Log in
  • Congratulations IDS on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Procedure too large

Status
Not open for further replies.
dje71183

dje71183

Industrial
Dec 23, 2005
17
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
 
Replies continue below

Recommended for you

Sort by date Sort by votes
Instead of "if...else if" try 'select case'. Look it up in the help file, it is made for a large number of choices.
 
Upvote 0 Downvote
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}
 
Upvote 0 Downvote

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
 
Upvote 0 Downvote
I am now calling all my procedures.

Thanks for all the help.
Daniel
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Washingtons
  • Locked
  • Question
Procedure too large--help!! VB Novice 1
Replies
2
Views
43
cowski
cowski
mtroche
  • Locked
  • Question
Calling procedure from a sheet in Excel 1
Replies
2
Views
42
mtroche
mtroche
mulderm
  • Locked
  • Question
Starting a procedure in Access every second
Replies
1
Views
27
CajunCenturion
CajunCenturion
Murali27
  • Locked
  • Question
Event Procedure
Replies
1
Views
29
joerd
joerd
typhonic
  • Locked
  • Question
Remote Procedure Call
Replies
1
Views
46
CTruax
CTruax

Part and Inventory Search

Sponsor

Back
Top Bottom