是这样的，一个题目，让自己模拟出标准正态分布的分布函数。
也就是模拟出这个 excel里面本身自带的函数： = Norm.s.dist(z,1)
要求自己在VBA里面编写code写一个function来实现这个算法。
我自己做了这个算法，用泰勒公式来计算 error function这个积分的，
但是到了一定程度，比如6个sigma开外，我的函数的结果就跟excel的norm.s.dist(z,1)
的结果有一些误差了，5个sigma之内的误差，在E-10以内。
我的代码是这样的，结果，我算z=8的时候就出现了 ”运行时错误6“，溢出
Option ExplicitOption Base 1
'L.D. I design this function to calculate the CDF of corresponding x
'L.D. I mainly use the Talor's Theorem to calculate the error function
'L.D. which is a part of the integral CDF of normal distribution
Function LiweiNormDist(x As Double) As Double
'so u = F(x) = F(0) + F^(-1)(0)*x + (F^(-2)(0)/2!)*x^2 + _ ' (F^(-3)(0)/3!)*x^3 + ...+ (F^(-n)(0)/n!)*x^n
'and here,as Norm.s.dist(0,1) = 0.5, so F(0) = 0.5,
'and in my formula above, F^(-1)(0) means the first derivative of
'F(x) at x = 0, and F^(-2)(0),F^(-n)(0) can be interpreted in the
'same way
Dim n As Integer, sum As Double, f() As Double, i As Integer
n = 0
sum = 0.5
ReDim f(1)
f(1) = x
Do
n = n + 1
ReDim Preserve f(n + 1)
f(n + 1) = -1 * f(n) * (x ^ 2) * (2 * n - 1) / ((2 * n + 1) * (2 * n))
Loop Until Abs(f(n + 1)) < 10 ^ (-18) And f(n + 1) > 0
For i = 1 To n + 1
sum = sum + f(i) * 1 / Sqr(2 * WorksheetFunction.Pi())
Next i
LiweiNormDist = sum
End FunctionSub mytest()
Dim t As Double t = InputBox("input your number: ")
MsgBox "it's gonna be " & LiweiNormDist(t)
End Sub
我想让误差尽可能地小一些，E-15就可以了。