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".
Anyone have an idea how I can fix this?
Thanks,
Daniel
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
'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
RE: Procedure too large
Call {WhateverYouNamedTheOtherProcedures}
RE: Procedure too large
You can use formula in the cell D16
CODE
or modify your code
CODE
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
Thanks for all the help.
Daniel
RE: Procedure too large
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
CODE
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
=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
20 and 41 is checked twice change code to
CODE
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