Compile Error: Procedure too large.

[LaBam]

I’m writing a procedure to compute the Total Interest on Loans using the Monthly Reducing Balance principle for a total period of 25 years (300 months). It works fine for as long as it’s under one sub procedure but then I get the error message “Procedure too large” when the whole code is placed under one procedure. When compiled, the code for a procedure can't exceed 64K.

When I tried splitting it, I get zero or null when I call the other procedures except I declare all the variables in each of the split procedures which means I would have to break them into several smaller procedures which is just too mind-numbing.

Please look at the procedure below (all put in one) and advise:
I. Is it possible to split procedures without having to re-declare all the variables in each?
II. Is it possible to increase the MS Access restriction on compiled code from the 64K?
III. Is there any shorter way of re-writing this code to get the same results so that I could just get it all under one procedure which falls below the 64K restriction? Is there a VBA formula for calculating interest on monthly reducing balance?

Private Sub cmdCompInterest_Click()
Dim TotalInterest As Double
Dim K As Double
Dim R As Single
Dim N As Single
Dim P As Double

Dim M01, M02, M03, M04, M05, M06, M07, M08, M09, M10 As Double
Dim M11, M12, M13, M14, M15, M16, M17, M18, M19, M20 As Double
Dim M21, M22, M23, M24, M25, M26, M27, M28, M29, M30 As Double
Dim M31, M32, M33, M34, M35, M36, M37, M38, M39, M40 As Double
Dim M41, M42, M43, M44, M45, M46, M47, M48, M49, M50 As Double

(The pattern continues on to…)

Dim M291, M292, M293, M294, M295, M296, M297, M298, M299, M300 As Double

R = tInterestR / 100
N = 1 / 12
P = tPrincipal
K = tPrincipal / tDurationMths

M01 = (P - (K * 0)) * R * N
M02 = (P - (K * 1)) * R * N
M03 = (P - (K * 2)) * R * N
M04 = (P - (K * 3)) * R * N
M05 = (P - (K * 4)) * R * N
M06 = (P - (K * 5)) * R * N
M07 = (P - (K * 6)) * R * N

(The pattern continues on to…)

M300 = (P - (K * 299)) * R * N

Select Case tDurationMths
Case 1
TotalInterest = M01
Case 2
TotalInterest = M01 + M02
Case 3
TotalInterest = M01 + M02 + M03
Case 4
TotalInterest = M01 + M02 + M03 + M04
Case 5
TotalInterest = M01 + M02 + M03 + M04 + M05
Case 6
TotalInterest = M01 + M02 + M03 + M04 + M05 + M06
Case 7
TotalInterest = M01 + M02 + M03 + M04 + M05 + M06 + M07
Case 8
TotalInterest = M01 + M02 + M03 + M04 + M05 + M06 + M07 + M08
Case 9
TotalInterest = M01 + M02 + M03 + M04 + M05 + M06 + M07 + M08 + M09
Case 10
TotalInterest = M01 + M02 + M03 + M04 + M05 + M06 + M07 + M08 + M09 + M10

(The pattern continues on to…)

Case 300
TotalInterest = M01 + M02 + M03 + M04 + M05 + M06 + M07 + M08 + M09 + M10 + … + M300

End Select


TotalLoan = TotalInterest + tPrincipal

 

[Malcy]

Hi
I had a similar problem but with less code than you have here. I resolved it by migrating some of the code off to other procedures and in my case it worked. It did cause me a big headache though.
It may be you can move some of the calculations off into modules and then call them.
Good luck with it. Looks a monster!
Best wishes

 

[Pat Hartman]

Dim M01, M02, M03, M04, M05, M06, M07, M08, M09, M10 As Double

- this statement only dim's M10 as Double. All the other variables end up as variants.

You need to learn how to use arrays. The proper coding technique will most likely reduce your code to less than a single page. Rather than 300 dim's, you'll have one dim that defines an array. Rather than a case statement with 300 cases, you'll have a loop that sums n elements of the array, etc.

 

[RV]

Is there any shorter way of re-writing this code to get the same results

Yes.
Define a function as there is a repeating pattern in how monthly interest is calculated.
You don't need to explicitialy calculate your interest per month.
Do don't even need array as Pat suggested, just one user defined function in VBA will do.

RV

 

[LaBam]

Thank you very much for the responses.

Pat and RV, could you please elaborate here for me. I'm afraid, I'm not too advance in VBA and a bit lost in your suggestions.

RV, could you please do a small illustration for me?

I thank you very much in advance.

 

[RV]

RV, could you please do a small illustration for me?

Sorry but I do not intend to do your job for you
It's not so much 'bout VBA, it's 'bout mathematics.
Are you doing this for a company offering loans, they no doubt can tell you exactly how they do their calculations.

Or search the Internet.

RV

 

[mcirvine]

your formula could look something like this

M(i) = (P - (K * i)) * R * N

that should get you started if you read on how to use a loop statement in vba

Good luck

 

[LaBam]

Mcirvine, thanks for the suggestion.

I'll try looping through the formular and see what would come out. I think the formular should be:

M(i) = (P - (K * (i-1))) * R * N

I'll post whatever I come up with.

 

[LaBam]

Thanks all of you who responded to my question. I've finally got it right with an amazingly few lines of code!

May be someone would be interested in using the code. Here it is:
........................................................................................

Public Function Interest_Calc()
    Dim TotalInterest As Double
    Dim K As Double
    Dim R As Single
    Dim N As Single
    Dim P As Double
    Dim i As Integer
    Dim tInterest As Double
    Dim NewInterest As Double
    
    R = tInterestR / 100
    N = 1 / 12
    P = tPrincipal
    K = tPrincipal / tDurationMths
    
    For i = 1 To tDurationMths
        tInterest = (P - (K * (i - 1))) * R * N
        If i = 1 Then
            TotalInterest = tInterest
        ElseIf i > 1 Then
            If i > 1 Then
                NewInterest = tInterest
            End If
            TotalInterest = TotalInterest + NewInterest
        End If
    Next i
    'If you want to display the end result in a message box
    MsgBox Format(TotalInterest, "##,###.00"), vbOKOnly, i - 1
End Function