Errors using Arrays in VBA

Question

I am new to VBA ans really appreciate your help.

I am writing a custom function. I am trying to use arrays to store values from the for loop and retrieve the values based on their location on the array. Please refer to the code below

Function Amounttopay(Original_Principal As Integer, APR As Double, Npayperyear As Integer, term As Integer, Paydone As Integer)

Dim strinitialamount() As Integer
Dim strInterestp() As Integer
Dim strendamount() As Integer
Dim i As Integer

r = (APR / Npayperyear)
n = Npayperyear * term
emi = (Original_Principal * r) / (1 - ((1 + r) ^ (-1 * n)))

ReDim strinitialamount(n)
ReDim strInterestp(n)
ReDim strendamount(n)

strinitialamount(0) = Original_Principal
strInterestp(0) = (Original_Principal * r)
strendamount(0) = (Original_Principal - (emi - strInterestp(0)))

For i = 1 To (n - 1)
    strinitialamount(i) = strendamount(i - 1)
    strInterestp(i) = (strinitialamount(i)) * r
    strendamount(i) = (strinitialamount(i)) - (emi - strInterestp(i))
Next i

Amounttopay = strendamount(Paydone)

End Function

In your declaration, you didn't completely the function. .. function x (...stuff ) as integer. — Kerry White
– Kerry White, Commented Jul 24, 2016 at 11:45
You should use Option Explicit in all modules, and declare all variables with appropriate data types. — Andre
– Andre, Commented Jul 24, 2016 at 11:49
&Sam not sure it's the cause for the error but Original_Principal is an integer , if you want a value of 1000000 modify it to Long — Shai Rado
– Shai Rado, Commented Jul 24, 2016 at 12:31

Kerry White · Accepted Answer · 2016-07-24 12:47:11Z

You need change it to Long because an integer only goes between -32,768 to 32,767.

I ran this with your values:

    Function Amounttopay(Original_Principal As Long, APR As Variant, Npayperyear As Integer, term As Integer, Paydone As Integer)

    Dim strinitialamount() As Long
    Dim strInterestp() As Long
    Dim strendamount() As Long
    Dim i As Integer

    r = (APR / Npayperyear)
    n = Npayperyear * term
    emi = (Original_Principal * r) / (1 - ((1 + r) ^ (-1 * n)))

    ReDim strinitialamount(n)
    ReDim strInterestp(n)
    ReDim strendamount(n)

    strinitialamount(0) = Original_Principal
    strInterestp(0) = (Original_Principal * r)
    strendamount(0) = (Original_Principal - (emi - strInterestp(0)))

    For i = 1 To (n - 1)
        strinitialamount(i) = strendamount(i - 1)
        strInterestp(i) = (strinitialamount(i)) * r
        strendamount(i) = (strinitialamount(i)) - (emi - strInterestp(i))
    Next i

    Amounttopay = strendamount(Paydone)

    End Function

    Sub TestFunction()

    Debug.Print Amounttopay(1000000, 0.1, 12, 1, 6)
    'Original_Principal = 1000000, APR = 0.1, Npayperyear = 12, term = 1, Paydone = 6
    End Sub

I received this as a result: 428798

Collectives™ on Stack Overflow

Errors using Arrays in VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related