Excel VBA compile error: Expected Sub, Function or Property

Question

I am getting a compile error in Excel VBA which says Expected Sub, Function or Property. The function I am using is given below which is trying to copy the rate function in Excel. Thanks for your help.

Function rate_m(nper As Double, pmt As Double, pv As Double, fv As Double, types As Double, guess As Double) As Variant
    Dim y, y0, y1, x0, x1, f, i As Double
    Dim FINANCIAL_MAX_ITERATIONS As Double
    Dim FINANCIAL_PRECISION As Double

    If IsNull(guess) Then guess = 0.01
    If IsNull(fv) Then fv = 0   
    If IsNull(types) Then types = 0

    FINANCIAL_MAX_ITERATIONS = 128 'Bet accuracy with 128
    FINANCIAL_PRECISION = 0.0000001 '1.0e-8

    y , y0, y1, x0, x1, f, i = 0
    rate_m = guess

    If Abs(rate_m) < FINANCIAL_PRECISION Then
        y = pv * (1 + nper * rate_m) + pmt * (1 + rate_m * types) * nper + fv
    Else
        f = Exp(nper * Log(1 + rate_m))
        y = pv * f + pmt * (1 / rate_m + types) * (f - 1) + fv

        y0 = pv + pmt * nper + fv
        y1 = pv * f + pmt * (1 / rate_m + types) * (f - 1) + fv
    End If

    'find root by Newton secant method
    i , x0 = 0
    x1 = rate_m

    While Abs(y0 - y1) > FINANCIAL_PRECISION & i < FINANCIAL_MAX_ITERATIONS
        rate_m = (y1 * x0 - y0 * x1) / (y1 - y0)
        x0 = x1
        x1 = rate_m

        If Abs(rate_m) < FINANCIAL_PRECISION Then
            y = pv * (1 + nper * rate_m) + pmt * (1 + rate_m * types) * nper + fv
        Else
            f = Exp(nper * Log(1 + rate_m))
            y = pv * f + pmt * (1 / rate_m + types) * (f - 1) + fv
        End If

        y0 = y1
        y1 = y
        i = i + 1
    Wend
End Function

aphoria · Accepted Answer · 2014-08-05 15:07:47Z

2

A couple things...

First, you have to assign each variable individually...like this:

y = 0
y0 = 0
y1 = 0
x0 = 0
x1 = 0
f = 0
i = 0

Second, you probably want to DIM your variables all as Double. Unfortunately, this line:

Dim y, y0, y1, x0, x1, f, i As Double

Only declares i as a Double, all the others will be a Variant. You need to declare each one individually, like this:

Dim y As Double
Dim y0 As Double
Dim y1 As Double
Dim x0 As Double
Dim x1 As Double
Dim f As Double
Dim i As Double

answered Aug 5, 2014 at 15:07

aphoria

20.3k8 gold badges67 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2014-08-05 15:13:07Z

1

Every IF ends with a End If (unless in a single line) and While...loop. You might want to take a look at VBA's syntax:
http://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx

EDIT: You have to declare variable individually, instead of: y , y0, y1, x0, x1, f, i = 0

you could do:
y = 0
y0 = 0
y1 = 0
x0 = 0
x1 = 0
f = 0
i = 0

edited Aug 5, 2014 at 15:13

answered Aug 5, 2014 at 14:40

Alex

1,6321 gold badge12 silver badges30 bronze badges

4 Comments

user3910805 Over a year ago

Thanks for reply. I changed to include End if and Loop ends with Wend. But still getting the same compile error.

Alex Over a year ago

@aphoria I've edited the answer. I suppose they're all zero so didn't bother

aphoria Over a year ago

I deleted my original comment because I tested it after I made the comment and your original code (y = y0 = y1 = x0 = x1 = f = i = 0) seemed to work. My mistake was testing with 0...changing it to any other value does not work. Sorry for the confusion.

Alex Over a year ago

@aphoria No worries I suppose both works but it's better to provide an answer that works in all senario (i.e. y0 = y1 = x0 = x1 = f = i = 1 won't works)

Collectives™ on Stack Overflow

Excel VBA compile error: Expected Sub, Function or Property

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related