Excel VBA 编译错误：预期的子、函数或属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25141506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA compile error: Expected Sub, Function or Property
提问by user3910805
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.
我在 Excel VBA 中收到一个编译错误，显示预期的子、函数或属性。下面给出了我正在使用的函数，它试图在 Excel 中复制速率函数。谢谢你的帮助。
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
回答by aphoria
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 DIMyour variables all as Double. Unfortunately, this line:
其次，您可能希望将DIM变量全部设为Double. 不幸的是，这一行：
Dim y, y0, y1, x0, x1, f, i As Double
Only declares ias a Double, all the others will be a Variant. You need to declare each one individually, like this:
仅声明i为 a Double，所有其他都将是 a Variant。您需要单独声明每一个，如下所示：
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
回答by Alex
Every IFends 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
每个IF 都以End If（除非在一行中）和While...loop 结束。您可能想看看 VBA 的语法：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
编辑：您必须单独声明变量，而不是： 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
你可以这样做：
y = 0
y0 = 0
y1 = 0
x0 = 0
x1 = 0
f = 0
i = 0
